Android RoomDB get List of children from another table using Hilt and ViewModels

Question

I have two tables Sale and SaleProducts with a one-to-many relationship (one Sale to many SaleProducts)

I'm trying to figure out what's the best way to get the sale and all the sale items from the room database using hilt and viewmodels.

Right now I get the LiveData of List<Sale> and observe it. I was thinking of then getting each sales SaleProduct's by looping in the observer but I that would require me to observe each List<SaleProduct>.

What's the best way to tackle this?

Answer 1

Room can retrieve the Children of a Parent, with the Parent so there would be no need to loop through getting the children.

To let Room do it on your behalf you could:-

1. Create a POJO with a field for the Sale object embedded using the @Embedded annotation and a field for a List<SaleProducts> annotated with the @Relationship annotation, specifying the parent column as the column that is referenced (typically the primary key of the referenced object, the Sale) by the child (The SaleProducts) and with the entity column as the column in the child that references the parent (The Sale).

2. add a Dao Query, that is:-

   1. annotated with the @Transaction annotation, and
      1. this is not actutaly required but strongly recommended. If not then Room will issue a build warning that the results may be inconsistent.
   2. has an @Query annotation that queries the required Sale objects, and
   3. returns a List

e.g.

Say you have the Sale class as:-

@Entity
data class Sale(
    @PrimaryKey
    val saleId: Long?=null,/* THE COLUMN REFERENCED BY THE SALEPRRODUCTS */
    val saleDate: Long,
    /* .... othere columns/fields */
)

And the SaleProducts class as:-

@Entity
data class SaleProducts(
    @PrimaryKey
    val saleProductId: Long?=null,
    @ColumnInfo(index = true) /* Optional BUT suggested else warning will be issued re full table scan */
    val saleIdMap: Long, /* THE COLUMN THAT REFERENCES THE PARENT SALE */
    /* .... other columns/fields*/
)

And the POJO :-

data class SaleWithSaleProducts(
    @Embedded
    val sale: Sale,
    @Relation(
        entity = SaleProducts::class,
        parentColumn = "saleId",
        entityColumn = "saleIdMap"
    )
    val saleProducts: List<SaleProducts>
)

Finally a function in the/an @Dao annotated interface:-

@Transaction
@Query("SELECT * FROM Sale")
fun getAllSalesWithRespectiveListOfSalesProducts(): LiveData<List<SaleWithSaleProducts>>

For each Sale located by the query a SaleWithSaleProducts will be returned, each will have the List of of all the related child SaleProducts.

Demo

The following demonstrates the code above, noting the following additional functions in the AllDAOs interace (i.e. annotated with @Dao) to allowing the insertion of Sale and SaleProducts rows.

@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(sale: Sale): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(saleProducts: SaleProducts): Long

Also a pretty basic @Database annotated class that allows use of the main thread for brevity of the demo (as such LiveData is not used/observed).

Finally some activity code as per :-

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


    val saleOnDate1Id = dao.insert(Sale(saleDate = 1))
    val saleOnDate2Id = dao.insert(Sale(saleDate = 2))
    val saleOnDate3Id = dao.insert(Sale(saleDate = 3))

    dao.insert(SaleProducts(saleIdMap = saleOnDate1Id))
    dao.insert(SaleProducts(saleIdMap = saleOnDate1Id))
    dao.insert(SaleProducts(saleIdMap = saleOnDate1Id))

    dao.insert(SaleProducts(saleIdMap = saleOnDate2Id))
    dao.insert(SaleProducts(saleIdMap = saleOnDate2Id))

    for (swsp in dao.getAllSalesWithRespectiveListOfSalesProducts()) {
        val sb = StringBuilder()
        for (sp in swsp.saleProducts) {
            sb.append("\n\tID of SaleProducts is ${sp.saleProductId} it maps to (is a child of) the Sale with an ID of ${sp.saleIdMap}")
        }
        Log.d("DBINFO","Sale ID is ${swsp.sale.saleId} Sale Date is ${swsp.sale.saleDate}. It has ${swsp.saleProducts.size} child SaleProducts. they are:- $sb")
    }

So 3 Sale rows are inserted with the saleId being stored in the respective saleOnDate?Id field.

Then 5 SaleProducts are inserted, the first 3 with the first Sale as the parent, the remaining 2 having the second Sale as the parent. The third Sale has no children.

The function to get the Sales with the SaleProducts is invoked. The resultant SaleWithSaleProducts is traversed writing the results to the Log.

• Note for simplicity/brevity 1,2 and 3 are used as the SaleDate rather than real dates.

The result being:-

D/DBINFO: Sale ID is 1 Sale Date is 1. It has 3 child SaleProducts. they are:- 
        ID of SaleProducts is 1 it maps to (is a child of) the Sale with an ID of 1
        ID of SaleProducts is 2 it maps to (is a child of) the Sale with an ID of 1
        ID of SaleProducts is 3 it maps to (is a child of) the Sale with an ID of 1
        
        
D/DBINFO: Sale ID is 2 Sale Date is 2. It has 2 child SaleProducts. they are:- 
        ID of SaleProducts is 4 it maps to (is a child of) the Sale with an ID of 2
        ID of SaleProducts is 5 it maps to (is a child of) the Sale with an ID of 2
        
        
D/DBINFO: Sale ID is 3 Sale Date is 3. It has 0 child SaleProducts. they are:- 

• i.e. as expected 3 Sale rows have been extracted, the first has the 3 SaleProducts (id's 1,2 and 3), the second the last2 SaleProducts (id's 4 and 5), the third Sale has no SaleProducts.