How to access each column on many-to-many relationships in room database android

Question

so here is the diagram of the database

What i wanna do is to join all these tables to get every column in each table, i have seen many-to-many relationship diagram examples in room example example1 example2 example3 but on these examples the relation table (OrderItem) only has 2 columns which the ids of the other tables (Order, Item), they ignore the value on relation table. But for me i also want to access/get the columns on relation table (OrderItem) that is quantity and total_price

I have tried something like this

ItemModel

@Entity(tableName = "item_table")
data class ItemModel(

  @PrimaryKey
  @ColumnInfo(name = "id")
  val itemId: String,
  @ColumnInfo(name = "item_name")
  var itemName: String,
  @ColumnInfo(name = "item_price")
  var itemPrice: String,
  @ColumnInfo(name = "item_stock")
  var itemStock: Int
)

OrderModel

@Entity(tableName = "order_table")
data class OrderModel(

  @PrimaryKey
  @ColumnInfo(name = "id")
  val orderId: String,
  @ColumnInfo(name = "order_total_price")
  val orderTotalPrice: String,
  @ColumnInfo(name = "order_pay")
  val orderPay: String,
  @ColumnInfo(name = "order_exchange")
  val orderExchange: String
)

OrderItemModel

@Entity(tableName = "order_item_table", primaryKeys = ["order_id", "item_id"])
data class OrderItemModel(

  @ColumnInfo(name = "order_id", index = true)
  var orderId: String,
  @ColumnInfo(name = "item_id", index = true)
  var itemId: String,
  @ColumnInfo(name = "quantity")
  val qty: Int,
  @ColumnInfo(name = "price")
  val price: String,
  @ColumnInfo(name = "total_price")
  val totalPrice: String
)

OrderWithItems

data class OrderWithItems(

  @Embedded
  val order: OrderModel,

  @Relation(
      entity = OrderItemModel::class,
      parentColumn = "id",
      entityColumn = "order_id"
  )
  val orderItems: List<OrderItemModel>,

  @Relation(
      entity = ItemModel::class,
      parentColumn = "id",
      entityColumn = "id",
      associateBy = Junction(
          OrderItemModel::class,
          parentColumn = "order_id",
          entityColumn = "item_id"
      )
  )
  val items: List<ItemModel>
)

But this give me inconcistence result, sometime the item_name on index 0 (from ItemModel) matches with the quantity on index 0 (from OrderItemModel) and sometimes not. Then what is the correct way to access each column on every table using room database?

MikeT · Accepted Answer · 2022-03-08T01:02:23.323

Then what is the correct way to access each column on every table using room database?

You need to follow the hierarchy.

That is you need, at the lower level, an OrderItemWithItem that has @Embedded OrderItem and @Relation Item. Then your OrderWithItem should have @Embedded for the Order and @Relation for the OrderItemWithItem.

But this give me inconcistence result

I believe that you do not want a many-many relationship as is introduced by associating. That is an order will have a unique set of orderitems rather than an order having many orderitems which may also be used by other orders.

So I believe that you want something like :-

data class OrderItemWithItem(
    @Embedded
    val orderItemModel: OrderItemModel,
    @Relation(entity = ItemModel::class, parentColumn = "item_id", entityColumn = "id")
    val itemList: List<ItemModel>
)

the lower hierarchy that will have a list (although you probably only want 1) of items.

With :-

data class OrderWithItems(

    @Embedded
    val order: OrderModel,
    @Relation(entity = OrderItemModel::class, parentColumn = "id", entityColumn = "order_id")
    val orderWithItems: List<OrderItemWithItem>

)

Working Example/Demo

Using your code and the above along with :-

@Dao
abstract class AllDao {

    @Insert
    abstract fun insert(orderModel: OrderModel): Long
    @Insert
    abstract fun insert(orderItemModel: OrderItemModel): Long
    @Insert
    abstract fun insert(itemModel: ItemModel): Long

    @Transaction
    @Query("SELECT * FROM order_table")
    abstract fun getAllFullOrders(): List<OrderWithItems>

}

Then using :-

    db = TheDatabase.getInstance(this)
    dao = db.getAllDao()

    val item1 = "Item1"
    val item2 = "Item2"
    val item3 = "Item3"

    val order1 = "Order1"
    val order2 = "Order2"
    val order3 = "Order3"

    dao.insert(ItemModel(item1,"Item1Name","10",100))
    dao.insert(ItemModel(item2,"Item2Name","20",200))
    dao.insert(ItemModel(item3,"Item3Name","30",300))

    dao.insert(OrderModel(order1,"0","?","?"))
    dao.insert(OrderModel(order2,"0","?","?"))
    dao.insert(OrderModel(order3,"0","?","?"))

    dao.insert(OrderItemModel(order1,item1,2,"100","200"))
    dao.insert(OrderItemModel(order2, item1,5,"100","500"))
    dao.insert(OrderItemModel(order2,item2,3,"200","600"))
    dao.insert(OrderItemModel(order3,item1,7,"100","700"))
    dao.insert(OrderItemModel(order3,item2,4,"200","800"))
    dao.insert(OrderItemModel(order3,item3,3,"300","900"))

    for(owi in dao.getAllFullOrders()) {
        Log.d("DBINFO","Order is ${owi.order.orderId} etc")
        for (oiwi in owi.orderWithItems) {
            Log.d("DBINFO","\t ItemId is ${oiwi.orderItemModel.itemId} Quantity is ${oiwi.orderItemModel.qty} Total is ${oiwi.orderItemModel.totalPrice}")
            for(i in oiwi.itemList) {
                Log.d("DBINFO","\t\tItem is ${i.itemName} name is ${i.itemName} price is ${i.itemPrice} in stock is ${i.itemStock}")
            }
        }
    }

Results in the Log including :-

D/DBINFO: Order is Order1 etc
D/DBINFO:    ItemId is Item1 Quantity is 2 Total is 200
D/DBINFO:       Item is Item1Name name is Item1Name price is 10 in stock is 100

D/DBINFO: Order is Order2 etc
D/DBINFO:    ItemId is Item1 Quantity is 5 Total is 500
D/DBINFO:       Item is Item1Name name is Item1Name price is 10 in stock is 100
D/DBINFO:    ItemId is Item2 Quantity is 3 Total is 600
D/DBINFO:       Item is Item2Name name is Item2Name price is 20 in stock is 200

D/DBINFO: Order is Order3 etc
D/DBINFO:    ItemId is Item1 Quantity is 7 Total is 700
D/DBINFO:       Item is Item1Name name is Item1Name price is 10 in stock is 100
D/DBINFO:    ItemId is Item2 Quantity is 4 Total is 800
D/DBINFO:       Item is Item2Name name is Item2Name price is 20 in stock is 200
D/DBINFO:    ItemId is Item3 Quantity is 3 Total is 900
D/DBINFO:       Item is Item3Name name is Item3Name price is 30 in stock is 300

Thankyou, yeah this is exactly what I wanted, making 2 separate embedded data class instead on 1 is something I've never expected to do — rijamyazid, Mar 08 '22 at 00:57

How to access each column on many-to-many relationships in room database android

1 Answers1