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?
CodePudding user response：
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