Insert relation in Room android

Question

I have question how to insert relation in room. So, I have Product and ProductsList entities. ProductsList could have a lot of Products, but products shouldn't know anything about lists where are they contained.

@Entity(tableName = "products")
data class Product(
    @PrimaryKey(autoGenerate = true)
    val productId: Long,

    val productName: String
)

@Entity
data class ProductList(
    @PrimaryKey(autoGenerate = true)
    val productListId: Long,
    val listName: String
)

I created ProductsListWithProducts class:

data class ProductListWithProducts(
    @Embedded
    val productList: ProductList,

    @Relation(
        parentColumn = "productListId",
        entityColumn = "productId",
        entity = Product::class
    )
    val productsId: List<Product>
)

but I don't understand how to insert data in Database. For example I already added Products in its table and after it want to create new ProductList. I have checked other answers and found that for it just using Dao to insert it something like:

@Dao
abstract class ProductListDao {

    @Transaction
    fun insert(productList: ProductList, products: List<Product>) {
        insert(productList)
        for (product in products) {
            insert(product)
        }
    }

But I don't see how adding relation between this tables, because I don't want to foreign keys in product entity (because in this case I need to create many-to-many relation). I thought about additional entity

@Entity(primaryKeys = ["productId", "productListId"])
data class ProductListProducts(
    val productId: Long,
    val productListId: Long
)

but it's using also to define many-to-many relation.

Can I add this relation without creating many-to-many relation, just one-to-many?

Answer 1

Yes have ProductListProducts however you may wish to consider using :-

@Entity(
    primaryKeys = ["productId", "productListId"]
    ,indices = [
        Index(value = ["productListId"]) /* Index else Room warns */
    ]
    /* Foreign Keys are optional BUT enforce referential integrity */
    , foreignKeys = [
        ForeignKey(
            entity = Product::class,
            parentColumns = ["productId"],
            childColumns = ["productId"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = ProductList::class,
            parentColumns = ["productListId"],
            childColumns = ["productListId"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
    )
data class ProductListProducts(
    val productId: Long,
    val productListId: Long
)

• you may wish to refer to https://sqlite.org/foreignkeys.html

This is an associative table (reference table, mapping table and many other terms). So you use a Relationship that utilises the association for the Junction between the ProductList and Product. Therefore your ProductListWithProducts POJO becomes :-

data class ProductListWithProducts (
    @Embedded
    val productList: ProductList,
    @Relation(
        entity = Product::class,
        parentColumn = "productListId",
        entityColumn = "productId",
        associateBy = Junction(
            ProductListProducts::class,
            parentColumn = "productListId",
            entityColumn = "productId"
        )
    )
    val product: List<Product>
)

Demonstration using the above classes (and your classes where the id's have been altered to be Long=0)

With a Dao class like :-

@Dao
abstract class AllDao {
    @Insert
    abstract fun insert(product: Product): Long
    @Insert
    abstract fun insert(productList: ProductList): Long
    @Insert
    abstract fun insert(productListProducts: ProductListProducts): Long
    @Transaction
    @Query("SELECT * FROM ProductList")
    abstract fun getProductListWithProducts(): List<ProductListWithProducts>
}

Then the following (run on the main thread for brevity/convenience) :-

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

    var p1 = dao.insert(Product( productName = "Product1"))
    var p2 = dao.insert(Product(productName = "Product2"))
    var pl1 = dao.insert(ProductList(listName = "List1"))
    var pl2 = dao.insert(ProductList(listName = "List2"))
    dao.insert(ProductListProducts(p1,pl1))
    dao.insert(ProductListProducts(p1,pl2))
    dao.insert(ProductListProducts(p2,pl1))

    dao.insert(ProductListProducts(dao.insert(Product(productName = "Product3")),dao.insert(
        ProductList(listName = "List3")))
    )
    for(plwp: ProductListWithProducts in dao.getProductListWithProducts()) {
        Log.d(TAG,"ProductList is ${plwp.productList.listName} ID is ${plwp.productList.productListId}")
        for(p: Product in plwp.product) {
            Log.d(TAG,"\t Product is ${p.productName} ID is ${p.productId}")
        }
    }

results in the log containing :-

D/DBINFO: ProductList is List1 ID is 1
D/DBINFO:    Product is Product1 ID is 1
D/DBINFO:    Product is Product2 ID is 2
D/DBINFO: ProductList is List2 ID is 2
D/DBINFO:    Product is Product1 ID is 1
D/DBINFO: ProductList is List3 ID is 3
D/DBINFO:    Product is Product3 ID is 3

Can I add this relation without creating many-to-many relation, just one-to-many?

The above (i.e. the associative table) will handle 1 to many but if you don't want the the extra table then you would have to include the identifier of the parent in the child. You could enforce 1-many, in the associative table, by making the column for the 1 unique.

As for Mass type insertions you say

For example I already added Products in its table and after it want to create new ProductList

Then you could perhaps go about by having the following additional @Dao's :-

@Insert
abstract fun insert(productListList: List<ProductList>): LongArray
@Insert
abstract fun insertManyProductListProducts(productListProductsList: List<ProductListProducts>): LongArray



/* This can be used to get a specific product or products according to a pattern */
/* e.g. */
/*  if productPatterName is product1 then an exact match */
/* if prod% then all that start with prod */
/* if %prod all that end in prod */
/* if %prod% then all that have prod anywhere */
@Query("SELECT productId FROM products WHERE productName LIKE :productNamePattern")
abstract fun getProductIdByName(productNamePattern: String): LongArray

And then have code such as :-

    /* Adding many new ProductLists to existing Products */
    /* 1 add the new ProductLists */
    /*      Noting that the insert returns an array of the productListId's inserted */
    val insertedProductLists = dao.insert(
        listOf(
            ProductList(listName = "ListX1"),
            ProductList(listName = "ListX2")
        )
    )
    /* 2. Determine the Product(s) that will be related to the new list of ProductLists */
    val productIdList = dao.getProductIdByName("Product%") /* All products */
    /* 3. Prepare the List of ProductListProducts for mass insertion */
    val plplist: ArrayList<ProductListProducts> = ArrayList()
    for(pid: Long in productIdList) {
        for(plid: Long in insertedProductLists) {
            plplist.add(ProductListProducts(pid,plid))
        }
    }
    /* 4. add the relationships */
    dao.insertManyProductListProducts(plplist)

• If you wanted 1 existing product e.g. product1 then you would use dao.getProductIdByName("Product1"), of course you can also increase/reduce the productLists in the Array to suit.

This would result in :-

D/DBINFO: ProductList is List1 ID is 1
D/DBINFO:    Product is Product1 ID is 1
D/DBINFO:    Product is Product2 ID is 2
D/DBINFO: ProductList is List2 ID is 2
D/DBINFO:    Product is Product1 ID is 1
D/DBINFO: ProductList is List3 ID is 3
D/DBINFO:    Product is Product3 ID is 3
D/DBINFO: ProductList is ListX1 ID is 4
D/DBINFO:    Product is Product1 ID is 1
D/DBINFO:    Product is Product2 ID is 2
D/DBINFO:    Product is Product3 ID is 3
D/DBINFO: ProductList is ListX2 ID is 5
D/DBINFO:    Product is Product1 ID is 1
D/DBINFO:    Product is Product2 ID is 2
D/DBINFO:    Product is Product3 ID is 3