How to insert entities with a one to many relationship in Room

user_14596633 0
Viewed 3248

I am building a database using Room and I can't figure out how to insert the new elements that have a relationship (one to many in my case) into the database. No solution has ever talked about the insertion (they only talk about querying the data).

Here is the DAO:

@Dao
abstract class ShoppingListsDao {

    @Insert
    abstract suspend fun addNewShoppingList(newShoppingList: ShoppingList)

    @Insert
    abstract suspend fun addNewItem(newItem: Item)

     // This is how I thought it would work but it didn't
     @Insert
     @Transaction
     abstract suspend fun addNewShoppingListWithItems(newShoppingListWithItems: ShoppingListWithItems)
}

Here are my entities:

@Entity
class ShoppingList(
        @PrimaryKey(autoGenerate = true)
        val listID: Int,
        val ListName: String
)

@Entity(foreignKeys = [ForeignKey(
        entity = ShoppingList::class,
        parentColumns = ["listID"],
        childColumns = ["parentListID"]
)])
class Item(
        @PrimaryKey(autoGenerate = true)
        var itemID: Int,
        val name: String,
        val quantity: Int,
        val parentListID: Int
)
android-jetpack android-room kotlin android
2 Answers

There isn't a way that I am aware of that lets you directly insert a compound entity (like ShoppingListWithItems). You have to just insert the individual entities to their tables.

In your example, you would want to define an insert method for your ShoppingList entity which returns the generated primary key (so you can use it for your other items) and an insert method for your Item entities which can insert a whole list of them.

@Insert
suspend fun addNewShoppingList(newShoppingList: ShoppingList): Long

@Insert
suspend fun addNewItems(newItems: List<Item>)

Then you can run a transaction to insert them in a batch.

@Transaction
suspend fun addNewShoppingListWithItems(shoppingList: ShoppingList, items: List<Item>) {

    val listId = addNewShoppingList(shoppingList)
    
    items.forEach { it.parentListId = listId }
    addNewItems(items)
}
user_100822970

Here's a good resource to understand one-to-many relationships-> https://developer.android.com/training/data-storage/room/relationships#one-to-many

You can create an embedded object for 'ShoppingListWithItems' as (more on embedded objects - https://developer.android.com/training/data-storage/room/relationships#nested-objects):

data class ShoppingListWithItems(
    @Embedded val shoppingList: ShoppingList,
    @Relation(parentColumn = "listID", entityColumn = "parentListID") val itemList: List<Item>
)

To store them in the database, you can simply use a transaction:

@Transaction
suspend fun createTransaction(shoppingList: ShoppingList, itemList: List<Item>) {
    addNewShoppingList(shoppingList)
    addNewItem(*itemList) // or create an alternate to accept the list itself.
}

To retrieve the 'ShoppingListWithItems' instance:

@Query("SELECT * from ShoppingList where listID =:id")
suspend fun getShoppingListWithItemsById(id: String): List<ShoppingListWithItems>
user_101840090
Related