Android Room: How to extract data from relational tables?

I have 2 tables as below and I have a relational table.

I want to access the details of an order. Which products were selected in the order and what is the quantity of the products. What should I write in the SQL query to access this information and how should I define the relationship class? No matter what I did, it didn't work.

The structure I want is as follows.

{ order: OrderEntity,
productlist: {ProductEntity ,quantity}
}





@Entity(tableName = "Orders")
data class OrderEntity(

    @PrimaryKey(autoGenerate = true)
    val orderId:Int=0,

    @ColumnInfo("name")
    val name:String,

    @ColumnInfo("description")
    val description:String,

    @ColumnInfo("price")
    val price:Double,

    @ColumnInfo("createdAt")
    val createdAt: Long = System.currentTimeMillis()
)

@Entity(tableName = "Products")
data class ProductEntity(

    @PrimaryKey(autoGenerate = true)
    val productId:Int=0,

    @ColumnInfo("name")
    val name:String,

    @ColumnInfo("description")
    val description:String,

    @ColumnInfo("price")
    val price:Double,

    @ColumnInfo("createdAt")
    val createdAt: Long = System.currentTimeMillis()
)

@Entity(
    primaryKeys = ["orderId", "productId"],
    tableName = "OrderProductCrossRef",
    foreignKeys = [
        ForeignKey(entity = OrderEntity::class, parentColumns = ["orderId"], 
childColumns = ["orderId"]),
        ForeignKey(entity = ProductEntity::class, parentColumns = ["productId"], 
childColumns = ["productId"])
    ],
    indices = [Index(value = ["orderId"]), Index(value = ["productId"])]
)
data class OrderProductCrossRef(
    @ColumnInfo("orderId") val orderId: Int,
    @ColumnInfo("productId") val productId: Int,
    @ColumnInfo("quantity")val quantity: Int
)

What should I write in the SQL query to access this information and how should I define the relationship class?

Typically the query is very simple, you just SELECT rows from the parent table.

• the Parent could be either of the objects pointed to by the relational table

However, what is important is the expected result type returned that is for the resolution of relationships via a relational table (associative table mapping table, reference table and other names/terms).

In your case to appear to want an OrderEntity(s) along with the related ProductEntitys. As such the Parent would be the OrderEntity and the children the ProductEntity. So you need a result type (a Class) that combines an OrderEntity with it's related List of ProductEntitys.

So as the basis something like:-

data class OrderWithProducts(
    val order: OrderEntity,
    val orderProducts: List<ProductEntity>
)

However, you have to provide further information to inform Room how it should work.

First you use the @Embedded annotation to let Room know what columns are to be used to construct the single occurrence of the OrderEntity.

So:-

data class OrderWithProducts(
    @Embedded
    val order: OrderEntity,
    val orderProducts: List<ProductEntity>
)

Additionally you need to let Room know about the relationship using the @Relation annotation. Furthermore, as the relationships is via the intermediate relational table then the associateBy parameter has to be used to provide a Junction.

So the above could then become:-

data class OrderWithProducts(
    @Embedded
    val order: OrderEntity,
    @Relation(
        /* Describes the Objects that make up the list (entity parameter can be omitted if it is implied)*/
        entity = ProductEntity::class,
        /* The field/column in the Embedded object that is referenced*/
        parentColumn = "orderId", 
        /* The field/column in the referenced object(s) */
        entityColumn = "productId",
        /* Describes the mapping/association/relationship/reference .... */
        associateBy = Junction(
            /* The @Entity annotated Object that defines the table */
            value = OrderProductCrossRef::class,
            /* the columns IN THE RELATION TABLE that reference the rows in the parent/child tables*/
            parentColumn = "orderId",
            entityColumn = "productId"
        )
    )
    val products: List<ProductEntity>
)

To use the above and get all Orders with the Products per Order you could use, for example:-

@Transaction
@Query("SELECT * FROM orders")
fun getOrdersWithTheOrdersProducts(): List<OrderWithProducts>

NOTE

The above WILL NOT retrieve the quantity column/field/value from the intermediate/relational table. Room doesn't directly cater for anything other than such a table just associating (build the relationship).

Not the ideal solution but to get the quantity a simple to implement way could be to extend the OrderWithProducts to include the list of OrderProductCrossRef rows associated with the order e.g.:-

data class OrderWithProducts(
    @Embedded
    val order: OrderEntity,
    @Relation(
        /* Describes the Objects that make up the list (entity parameter can be omitted if it is implied)*/
        entity = ProductEntity::class,
        /* The field/column in the Embedded object that is referenced*/
        parentColumn = "orderId",
        /* The field/column in the referenced object(s) */
        entityColumn = "productId",
        /* Describes the mapping/association/relationship/reference .... */
        associateBy = Junction(
            /* The @Entity annotated Object that defines the table */
            value = OrderProductCrossRef::class,
            /* the columns IN THE RELATION TABLE that reference the rows in the parent/child tables*/
            parentColumn = "orderId",
            entityColumn = "productId"
        )
    )
    val products: List<ProductEntity>,

    /*!!!!!!!!!! Added to get the mapped quantity !!!!!!!!!!*/
    @Relation(
        entity = OrderProductCrossRef::class,
        parentColumn = "orderId",
        entityColumn = "orderId"
    )
    val mappedQuantity: List<OrderProductCrossRef>
)

• of course a bit of a pain to tie a quantity with the correct product.