Room entity on @Embedded annotation has id always 0

Question

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)
        }
}

Answer 1

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

data class FinancialCategory(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo
    val id: Long = 0L,
)

@Entity(
    tableName = "FinancialRecords",
    foreignKeys = [ForeignKey(
        entity = FinancialCategory::class,
        parentColumns = ["id"],
        childColumns = ["category"],
        onDelete = ForeignKey.CASCADE
    )],
    indices = [Index(value = ["category"], unique = false)]
)
data class FinancialRecord(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0L,
    ...

)

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:

data class FinancialCategory(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "parentId"
    val id: Long = 0L,
)

@Entity(
    tableName = "FinancialRecords",
    foreignKeys = [ForeignKey(
        entity = FinancialCategory::class,
        parentColumns = ["parentId"],
        childColumns = ["category"],
        onDelete = ForeignKey.CASCADE
    )],
    indices = [Index(value = ["category"], unique = false)]
)
data class FinancialRecord(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "childId"
    val id: Long = 0L,
    ...

)

data class CategoryWithRecords(
    @Embedded
    val category: FinancialCategory,

    @Relation(
        parentColumn = "parentId",
        entityColumn = "category"
    )
    val records: List<FinancialRecord>
)

Answer 2

Try changing the query to be just:-

@Transaction
@Query("SELECT DISTINCT *" +
       "FROM FinancialCategories " + 
       "WHERE FinancialCategories.parent = (SELECT id FROM FinancialCategories WHERE name = :categoryName);")
fun getCategoriesWithItems(categoryName: String): Flow<List<CategoryWithRecords>>

That is, when using an @Query, all that Room needs is are the parent objects, the children (note ALL children per parent) are then obtained via a subquery per parent (hence the recommended @Transaction annotation to encompass all the queries into a single transaction).

• If you want to select particular children then you need to bypass Room's get all children (easiest way is to have a function that get's the parent's and then uses it's own subquery e.g. Room Android ignores @Query conditions in Dao class (Strange))

Room, does also sometimes have issues with ambiguous columns in output (e.g. multiple columns with the same name (id), it is recommended to use unique column names but not necessary as the demo below shows).

Perhaps consider this example that demonstrates some of the issues that can arise from including the children in the output:-

The following activity code is used to insert some data:-

    val fcid_01 = categoryDao.insert(FinancialCategory(id = 1,name = "FC001", parent = 1))
    val fcid_02 = categoryDao.insert(FinancialCategory(id = 2,name = "FC002", parent = 3))
    val fcid_03 = categoryDao.insert(FinancialCategory(id = 3,name = "FC003", parent = 1))
    val fcid_04 = categoryDao.insert(FinancialCategory(id =4,name = "FC004", parent = -4))
    val fcid_05 = categoryDao.insert(FinancialCategory(id = 5,name = "FC005", parent = 1))

    categoryDao.insert(FinancialRecord(title = "FR001", amount = /*BigDecimal(*/111111.11/*)*/, category = fcid_01))
    categoryDao.insert(FinancialRecord(title = "FR002", amount = /*BigDecimal(*/222222.22/*)*/, category = fcid_01))
    categoryDao.insert(FinancialRecord(title = "FR003", amount = /*BigDecimal(*/333333.33/*)*/, category = fcid_01))
    categoryDao.insert(FinancialRecord(title = "FR004", amount = /*BigDecimal(*/444444.44/*)*/, category = fcid_02))
    categoryDao.insert(FinancialRecord(title = "FR005", amount = /*BigDecimal(*/555555.55/*)*/, category = fcid_02))
    categoryDao.insert(FinancialRecord(title = "FR006", amount = /*BigDecimal(*/666666.66/*)*/, category = fcid_03))
    categoryDao.insert(FinancialRecord(title = "FR007", amount = /*BigDecimal(*/7777777.77/*)*/, category = fcid_01))
    categoryDao.insert(FinancialRecord(title = "FR008", amount = /*BigDecimal(*/8888888.88/*)*/, category = fcid_04))
    categoryDao.insert(FinancialRecord(title = "FR009", amount = /*BigDecimal(*/9999999.99/*)*/, category = fcid_05))
    categoryDao.insert(FinancialRecord(title = "FR010", amount = /*BigDecimal(*/1234567.12/*)*/, category = fcid_05))

• note it is assumed that a FinanciaCategory point's to itself, or to a non existent FinancialCategory if it is not a sub-category (id 1 points to iteslf, id 4 points to -4).

When run then the tables are, using App Inspection:-

and :-

Now consider the @Dao annotated CategoryDao as having your original function and another 2 (inserts used included):-

@Dao
interface CategoryDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(financialCategory: FinancialCategory): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(financialRecord: FinancialRecord): Long
    @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>/*>*/

    /* DEMO how Room gets the embedded children JUST from the parents*/
    @Transaction
    @Query("SELECT * FROM financialcategories")
    fun getAllCategoriesWithItemsAsRoomExpects(): List<CategoryWithRecords>

    /* SUGGESTED */
    @Transaction
    @Query(
        "SELECT DISTINCT *" +
                "FROM FinancialCategories " +
                "WHERE FinancialCategories.parent = (SELECT id FROM FinancialCategories WHERE name = :categoryName);"
    )
    fun getCategoriesWithItemsV2(categoryName: String): /*Flow<*/List<CategoryWithRecords>/*>*/

}

Now consider the following activity code that is run after the data has been loaded:-

....

        for (cwr in categoryDao.getCategoriesWithItems("FC001")) {
            logCategoryWithRecords(cwr,"RUN1")
        }
        for (cwr in categoryDao.getAllCategoriesWithItemsAsRoomExpects()) {
            logCategoryWithRecords(cwr,"RUN2")
        }

        for (cwr in categoryDao.getCategoriesWithItemsV2("FC001")) {
            logCategoryWithRecords(cwr,"RUN3")
        }
    }

    fun logCategoryWithRecords(cwr: CategoryWithRecords, tagSuffix: String) {
        val sb = StringBuilder()
        for (r in cwr.records) {
            sb.append("\n\tFR: ID=${r.id} Title=${r.title} CategroryRef=${r.category} amount=${r.amount}")
        }
        Log.d("DBINFO_${tagSuffix}","FC name=${cwr.category.name} ID=${cwr.category.id} parentRef=${cwr.category.parent}: It has ${cwr.records.size} records. They are:${sb}")
    }
}

• CategoriesWithRecords unchanged.

The logcat includes :-

From your Query RUN 1

2023-10-16 10:58:17.658 D/DBINFO_RUN1: FC name=FC001 ID=1 parentRef=1: It has 4 records. They are:
        FR: ID=1 Title=FR001 CategroryRef=1 amount=111111.11
        FR: ID=2 Title=FR002 CategroryRef=1 amount=222222.22
        FR: ID=3 Title=FR003 CategroryRef=1 amount=333333.33
        FR: ID=7 Title=FR007 CategroryRef=1 amount=7777777.77
2023-10-16 10:58:17.658 D/DBINFO_RUN1: FC name=FC001 ID=2 parentRef=1: It has 2 records. They are:
        FR: ID=4 Title=FR004 CategroryRef=2 amount=444444.44
        FR: ID=5 Title=FR005 CategroryRef=2 amount=555555.55
2023-10-16 10:58:17.658 D/DBINFO_RUN1: FC name=FC001 ID=3 parentRef=1: It has 1 records. They are:
        FR: ID=6 Title=FR006 CategroryRef=3 amount=666666.66
2023-10-16 10:58:17.658 D/DBINFO_RUN1: FC name=FC001 ID=7 parentRef=1: It has 0 records. They are:
2023-10-16 10:58:17.659 D/DBINFO_RUN1: FC name=FC003 ID=6 parentRef=1: It has 0 records. They are:
2023-10-16 10:58:17.659 D/DBINFO_RUN1: FC name=FC005 ID=9 parentRef=1: It has 0 records. They are:
2023-10-16 10:58:17.659 D/DBINFO_RUN1: FC name=FC005 ID=10 parentRef=1: It has 0 records. They are:

• As can be seen not the expected results FC id's are being retrieved from the AMIBGUOUS 2nd ID column ALSO repeated Parents (Cartesian Product see below)

RUN 2 All just from parent

2023-10-16 10:58:17.665 D/DBINFO_RUN2: FC name=FC001 ID=1 parentRef=1: It has 4 records. They are:
        FR: ID=1 Title=FR001 CategroryRef=1 amount=111111.11
        FR: ID=2 Title=FR002 CategroryRef=1 amount=222222.22
        FR: ID=3 Title=FR003 CategroryRef=1 amount=333333.33
        FR: ID=7 Title=FR007 CategroryRef=1 amount=7777777.77
2023-10-16 10:58:17.666 D/DBINFO_RUN2: FC name=FC002 ID=2 parentRef=3: It has 2 records. They are:
        FR: ID=4 Title=FR004 CategroryRef=2 amount=444444.44
        FR: ID=5 Title=FR005 CategroryRef=2 amount=555555.55
2023-10-16 10:58:17.666 D/DBINFO_RUN2: FC name=FC003 ID=3 parentRef=1: It has 1 records. They are:
        FR: ID=6 Title=FR006 CategroryRef=3 amount=666666.66
2023-10-16 10:58:17.666 D/DBINFO_RUN2: FC name=FC004 ID=4 parentRef=-4: It has 1 records. They are:
        FR: ID=8 Title=FR008 CategroryRef=4 amount=8888888.88
2023-10-16 10:58:17.666 D/DBINFO_RUN2: FC name=FC005 ID=5 parentRef=1: It has 2 records. They are:
        FR: ID=9 Title=FR009 CategroryRef=5 amount=9999999.99
        FR: ID=10 Title=FR010 CategroryRef=5 amount=1234567.12

• As can be seen all FC's have been extracted with the respective children no duplicated FC's with ambiguous data.

SUGGESTED QUERY RUN 3

2023-10-16 10:58:17.675 D/DBINFO_RUN3: FC name=FC001 ID=1 parentRef=1: It has 4 records. They are:
        FR: ID=1 Title=FR001 CategroryRef=1 amount=111111.11
        FR: ID=2 Title=FR002 CategroryRef=1 amount=222222.22
        FR: ID=3 Title=FR003 CategroryRef=1 amount=333333.33
        FR: ID=7 Title=FR007 CategroryRef=1 amount=7777777.77
2023-10-16 10:58:17.675 D/DBINFO_RUN3: FC name=FC003 ID=3 parentRef=1: It has 1 records. They are:
        FR: ID=6 Title=FR006 CategroryRef=3 amount=666666.66
2023-10-16 10:58:17.675 D/DBINFO_RUN3: FC name=FC005 ID=5 parentRef=1: It has 2 records. They are:
        FR: ID=9 Title=FR009 CategroryRef=5 amount=9999999.99
        FR: ID=10 Title=FR010 CategroryRef=5 amount=1234567.12

• Selected and FC and FC's that are a sub-category of it (what appears to be the result expected)

Cartesian Product

Running your query, via App Inspection:-

i.e. per parent every combination of parent and child results in a FinnancialCategory being created but then due to the ambiguous id column the last id encountered is the id used. (of course not applicable if letting Room work in it's way e.g. the suggested way).

As opposed to the suggested query (DISTINCT should not be needed as a Category row will only meet the criteria once, and the name is unique):-