Suppose I have items which can each have many categories:
@Entity(tableName = "items")
data class Item(
@PrimaryKey val item_id: Long,
val external_id: String,
val name: String,
val price: Long,
val image: String?,
var indexInResponse: Int = -1
)
@Entity(tableName = "categories")
data class Category(
@PrimaryKey val cat_id: Long,
val name: String,
val image: String?,
var indexInResponse: Int = -1
)
//-------
@Entity(tableName = "items_with_categories", primaryKeys = ["item", "cat"], indices = [Index(value = ["cat"])])
data class ItemCategoryCrossRef(
val item: Long,
val cat: Long
)
data class ItemWithCategories(
@Embedded val item: Item,
@Relation(
parentColumn = "item_id",
entityColumn = "cat_id",
associateBy = Junction(
ItemCategoryCrossRef::class,
parentColumn = "item",
entityColumn = "cat")
)
val cats: List<Category>
)
And I can retrieve all items with categories using this, which works well:
@Transaction
@Query("SELECT * FROM items ORDER BY indexInResponse ASC")
abstract fun getItemsWithCategories(): DataSource.Factory<Int, ItemWithCategories>
How can I retrieve items with particular categories based on ID using Room or SQL?
@Transaction
@Query("SELECT * FROM items WHERE ??<something>?? LIKE :catID ORDER BY indexInResponse ASC")
abstract fun getItemsWithCategoriesByCatID(catID: Long): DataSource.Factory<Int, ItemWithCategories>
I don't think this is possible, obviously the list of Categories is a list of POJOs, and it's a nested relation. So is the only way to do this in SQL to do it with a raw query?
This is the closest question I have found, but the DatabaseView in the answer here uses a hard coded field for it's WHERE clause and I need to be able to specify the category ID at run time.
