Room relationship matching multiple columns

Question

I have a relationship between entities that should look like this:

Overview -> has many -> Category -> has many -> Transaction

Category should be included in Overview if Category.overviewId == Overview.id. I'm pretty sure I should be able to achieve this with the @Relation annotation, something like:

@Relation(parentColumn = 'id', entityColumn = 'overviewId')
List<Category> categories;

Within the Category entity I then want to include any Transaction where Transaction.overviewId == Category.overviewId && Transaction.categoryId == Category.categoryId. This is the part that I'm struggling with, as the @Relation annotation only seems to be able to take 1 column into account. I think I want something like:

@Relation(parentColumn = {'overviewId','categoryId'}, entityColumn = {'overviewId','categoryId'})
List<Transaction> transactions;

But this doesnt seem to be a valid way of doing things. Does anyone know how I should achieve this? I guess ultimately I want to be able to observe a query that will update whenever a relevant Overview, Category, or Transaction is updated, so embedding the Categories and Transactions in the Overview seems like the best way to go, but if there is a better way it'd be great to hear it.

Answer 1

Had same problem, came up with @Transaction

@Query("SELECT * FROM store_table WHERE userId = :userId AND storeId = :storeId")
fun getStore(storeId:String, userId:String): StoreDb?

@Query("SELECT * FROM product_table WHERE userId = :userId AND storeId = :storeId")
fun getProducts(storeId:String, userId:String):List<ProdusctsDb>

@Transaction
fun getStoreWithProducts(storeId:String, userId:String): StoreWithProductsDb{
    val storeDb: StoreDb = getStore(storeId,userId) ?: return null
    val products = getProducts(storeId,userId)
    return  StoreWithProductsDb(storeDb, products)
}

Where StoreWithProductsDb is usual data class

data class StoreWithProductsDb(val storeDb: StoreDb, val products: List<ProductsDb>)

PS: just adopted example all names are random