I am struggling with executing queries containing JOIN in room. The problem is, that in the entity joining multiple entities I get the @Embedded entity with ID of always 0.
This are my following entities:
@Entity(
tableName = "FinancialCategories",
indices = [Index(value = ["name"], unique = true)]
)
data class FinancialCategory(
@PrimaryKey(autoGenerate = true)
@ColumnInfo(name = "id")
val id: Long = 0L,
@ColumnInfo(name = "name")
val name: String,
@ColumnInfo(name = "parent")
val parent: Long
)
@Entity(
tableName = "FinancialRecords",
foreignKeys = [ForeignKey(
entity = FinancialCategory::class,
parentColumns = ["id"],
childColumns = ["category"],
onDelete = ForeignKey.CASCADE
)],
indices = [Index(value = ["title"], unique = true), Index(value = ["category"], unique = false)]
)
data class FinancialRecord(
@PrimaryKey(autoGenerate = true)
val id: Long = 0L,
@ColumnInfo(name = "title")
val title: String,
@ColumnInfo(name = "amount")
val amount: BigDecimal,
@ColumnInfo(name = "category")
val category: Long
)
This is the relation entity:
data class CategoryWithRecords(
@Embedded
val category: FinancialCategory,
@Relation(
parentColumn = "id",
entityColumn = "category"
)
val records: List<FinancialRecord>
)
And this is how I fetch this data from room:
@Transaction
@Query("SELECT *" +
"FROM FinancialCategories " +
"LEFT JOIN FinancialRecords " +
"ON FinancialCategories.id = FinancialRecords.category " +
"WHERE FinancialCategories.parent = (SELECT id FROM FinancialCategories WHERE name = :categoryName);")
fun getCategoriesWithItems(categoryName: String): Flow<List<CategoryWithRecords>>
And this is my repository:
fun getFinancialData(category: FinancialCategories): Flow<Map<FinancialCategory, List<FinancialRecord>>> {
return categoryDao.getCategoriesWithItems(category.displayName).map {
it.associate {
categoryWithRecords -> categoryWithRecords.category to categoryWithRecords.records
}
}
}
At this point it contains a map of my entities which are mapped correctly, except every key (FinancialCategory) has an ID of 0. Any ideas why this happens?
I am storing this like so:
override fun addCategory(intent: FinancialRecordIntent.AddFinanceCategory) {
viewModelScope.launch(Dispatchers.IO) {
repository.storeCategory(category = intent.category)
}
}
For anyone having the same issue. There seems to be a bug directly in Room. It seems that if you are relating tables with @Embedded the column names must not have the same name. For example
Won't work. It seems like room can not identify properly the entities with db columns but giving the columns in the entities unique names seems to work fine: