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

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 )

CodePudding user response：

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 :-

and

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.