Search code examples
androidsqliteandroid-room

How to sort child data using relation in Android ROOM database which uses (One to many relationship)

Store Entity

data class Store(
val storeId: Int,
val name: String,
val storeRank:Int
)

Product Entity

data class Product(
val productId: Int,
val name: String
)

Reference Entity

data class Reff(
val storeId: Int,
val productId: Int,
val productRankInStore:Int
)

Relation

data class StoreAndProduct(
@Embedded
val store: Store,
@Relation(
    entity = Product::class,
    parentColumn = "storeId",
    entityColumn = "productId",
    associateBy = Junction(
        parentColumn = "storeId",
        entityColumn = "productId",
        value = Reff::class
    )
)
val product: List<Product>


)

Here I need to sort Products using the key productRankInStore. I have already implemented the relation and which is working fine. But I couldn't find any other way to sort the products using productRankInStore

NB: Same product have different rank in different store ( productRankInStore )

Solution

  • If you have an abstract class rather than an interface for the @Dao annotated class(es) then you could effectively override how Room handles @Relation using a function that does the 2 stages, with the latter sorted accordingly.

    You do this by having 2 @Query 's :-

    • the primary (Store(s)) and
    • secondary query (the products sorted by rank)

    You then combine them into a function e.g. :-

    @Dao
abstract class AllDAO {

    @Query("SELECT * FROM store")
    abstract fun getAllStores(): List<Store>
    @Query("SELECT product.* FROM reff JOIN product ON product.productId = reff.productId WHERE reff.storeId=:storeId ORDER BY productRankInStore DESC")
    abstract fun getStoreProductsSortedByRank(storeId: Int): List<Product>

    @Query("")
    @Transaction
    fun getStoreAndProductsSortedByProductRank(): List<StoreAndProduct> {
        val rv = arrayListOf<StoreAndProduct>()
        for (store in getAllStores() /* obviously change initial query if desired */) {
            rv.add(StoreAndProduct(store,getStoreProductsSortedByRank(store.storeId)))
        }
        return rv
    }
}

    You can then use:-

    dao.getStoreAndProductsSortedByProductRank()

    e.g. if you have data as :-

    enter image description here

    and

    enter image description here

    enter image description here

    and Rank 1 is the top then the following

        for(s in dao.getStoreAndProductsSortedByProductRank()) {
        Log.d("DBINFO","Store is ${s.store.name}")
        for (p in s.product) {
            Log.d("DBINFO","\tProduct is ${p.name}")
        }
    }

    will output :-

    2022-03-26 06:43:15.753 D/DBINFO: Store is Store1
2022-03-26 06:43:15.753 D/DBINFO:   Product is ProductA
2022-03-26 06:43:15.753 D/DBINFO:   Product is ProductB
2022-03-26 06:43:15.753 D/DBINFO:   Product is ProductC
2022-03-26 06:43:15.753 D/DBINFO: Store is Store2
2022-03-26 06:43:15.753 D/DBINFO:   Product is ProductC
2022-03-26 06:43:15.753 D/DBINFO:   Product is ProductA
2022-03-26 06:43:15.753 D/DBINFO:   Product is ProductB
2022-03-26 06:43:15.753 D/DBINFO: Store is Store3
2022-03-26 06:43:15.753 D/DBINFO:   Product is ProductC
2022-03-26 06:43:15.753 D/DBINFO:   Product is ProductB
2022-03-26 06:43:15.754 D/DBINFO:   Product is ProductA

    NOTE the rankInStore will not be available (as per your StoreAndProduct).

    • If you need the rankInStore available then you would need to do something like have and use a ProductAndRank POJO.