Android Room and nested relationships error

Question

I have the following 3 entities:

@Entity(tableName = "PROPERTY",
    indices = [
        androidx.room.Index(value = ["id"], unique = true)
    ])
data class Property (
    @Expose @PrimaryKey override val id: UUID = UUID.randomUUID(),
    @SerializedName("type") @ColumnInfo(name = "type") val type: ItemPropertyType,
    @SerializedName("code") @ColumnInfo(name = "code") val code: String,
    @SerializedName("default_description") @ColumnInfo(name = "default_description") val defaultDescription: String?,
    @SerializedName("description_id") @ColumnInfo(name = "description_id") val descriptionId: UUID?,
    @SerializedName("default_property") @ColumnInfo(name = "default_property") val defaultProperty: Boolean,
    @SerializedName("entity") @ColumnInfo(name = "entity") val entity: String = "ITEM"
)

@Entity(tableName = "PROPERTY_SET_DETAIL",
    indices = [
        Index(value = ["id"], unique = true),
        Index(value = ["property_id"], unique = false)
    ],
    foreignKeys = [
        ForeignKey(entity = PropertySet::class, parentColumns = ["id"], childColumns = ["property_set_id"]),
        ForeignKey(entity = Property::class, parentColumns = ["id"], childColumns = ["property_id"])])
data class PropertySetDetail (
    @Expose @PrimaryKey override val id: UUID = UUID.randomUUID(),
    @SerializedName("property_id") @ColumnInfo(name = "property_id") val propertyId: UUID,
    @SerializedName("value") @ColumnInfo(name = "value") var value: String?,
    @SerializedName("rank") @ColumnInfo(name = "rank") val rank: Int,
    @SerializedName("property_set_id") @ColumnInfo(name = "property_set_id") val propertySetId: UUID
)

@Entity(tableName = "PROPERTY_SET",
    indices = [Index(value = ["id"], unique = true), Index(value = ["properties_charset"], unique = false), Index(value = ["hashkey"], unique = false)])
data class PropertySet (
    @Expose @PrimaryKey override val id: UUID = UUID.randomUUID(),
    @SerializedName("item_id") @ColumnInfo(name = "item_id") val itemId: UUID,
    @SerializedName("properties_charset") @ColumnInfo(name = "properties_charset") var propertiesCharset: String?,
    @SerializedName("hashkey") @ColumnInfo(name = "hashkey") var hashkey: String?,
)

and also the following data classes that I need for the nested relationships among the 3 entities declared above:

data class PropertySetAndDetails(
    @Embedded
    val set: PropertySet,

    @Relation(
        entity = PropertySetDetail::class,
        parentColumn = "id",
        entityColumn = "property_set_id"
    ) val details: List<PropertyDetailAndProperty>,
) 

data class PropertyDetailAndProperty(
    @Embedded
    val property: Property,

    @Relation(
        parentColumn = "id",
        entityColumn = "property_id"
    ) val detail: PropertySetDetail

)

and the following DAO query function:

@Transaction
@Query("select * from property_set where item_id = :itemId")
suspend fun getSetsForItem(itemId: UUID): List<PropertySetAndDetails>

There is no way to make it work. I get the following build error:

error: There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such column: type)

I also declared the following convert functions for

ItemPropertyType:

@TypeConverter
    fun toItemPropertyType(v: Byte?): ItemPropertyType {
        return when (v) {
            null -> ItemPropertyType.Unknown
            else -> ItemPropertyType.getByValue(v)
        }
    }

    @TypeConverter
    fun fromItemPropertyType(t: ItemPropertyType?): Byte {
        return when (t) {
            null -> ItemPropertyType.Unknown.value
            else -> t.value
        }
    }

Can someone explain me why this nested relationship doesn't work ? Thanks in advance.

Answer 1

Can someone explain me why this nested relationship doesn't work ?

I can't (let's call it Room's magic), but I can hint you how to make this work. Try to change your PropertyDetailAndProperty class to this:

data class PropertyDetailAndProperty(
    @Embedded
    val detail: PropertySetDetail,

    @Relation(
        parentColumn = "property_id",
        entityColumn = "id"
    ) val property: Property
)

It seems nested class should contain entity you use in outer class ( PropertySetDetail in your case) beyond @Relation. Why? Well, I guess it just was developers' design.

Answer 2

Have you tried using the converter? Room cannot identify this data ("ItemPropertyType"). This is why you are getting this error.

Doc