Room Android ignores @Query conditions in Dao class (Strange)-CodePudding

I am posting this because same issue is already there on stackoverflow but no solution on this. I am using Room library for db operations. I have created data classes with @Embedded and @Relation with other tables. Now the issue is when I put join queries with multiple where conditions on main as well as joined tables, it returns all/incorrect data of joined tables. This shows it ignores the conditions I have put in a DAO class query. Important thing is when I run the same query on a database externally (using stetho in chrome) it works as expected. Please help me with this as this is highly critical issue. Room version: 2.4.0

This is the data class:

data class ProductFull{
    @Embedded val product: ProductMaster,

    @Relation(
        entity = ProductZone::class,
        parentColumn = "productId",
        entityColumn = "productId",
    )
    var productZone: ProductZone? = null,
}

This is the DAO class method:

@Query("select * from ProductMaster as pm inner join ProductZone as pz on pz.productId = pm.productId where pz.zoneId = 3")
    abstract suspend fun getTempProducts(): List<ProductFull>

Above query returns data in productZone field of data class having zoneId = 1. Whereas it should only return zones having zoneId = 3.

CodePudding user response：

When using @Relation room builds the underlying query(ies) to get ALL children (ProductZones) for each parent (ProductMaster) that the query selects.

A convenience annotation which can be used in a POJO to automatically fetch relation entities. When the POJO is returned from a query, all of its relations are also fetched by Room. https://developer.android.com/reference/kotlin/androidx/room/Relation

A get-around is two have 2 dao's one that selects the parents and the other that selects the required children and a function (use an abstract class rather than an interface for the Dao's) that gets the parents using the first query and then for each parent gets the required children using the second query.

The function should be annotated with @Transaction, to allow this also annotate it with @Query("")

You would want something like:-

@Transaction
@Query("SELECT * FROM productmaster JOIN productzone on productmaster.productId = productzone.productId WHERE productzone.zoneId = 3")
abstract fun getTempProducts(): List<ProductFull>

@Query("SELECT * FROM productzone WHERE productId=:productId AND zoneId=3")
abstract fun getTempZone(productId: Long): ProductZone

@Transaction
@Query("")
fun buildFullProducts(): List<ProductFull> {
    var rv = getTempProducts()
    for (pf: ProductFull in rv) {
        pf.productZone = getTempZone(pf.product.productId!!)
    }
    return rv
}

and use the buildFullProducts function to retrieve the list of ProductFull's