android android-room entity-relationship pojo android-room-relation

POJO within POJO, Entity-Relation problem

I have five tables in my database: AREA, AREA_TYPE, SAMPLE, PACK, UNIT

    @Entity(tableName = "AREA")
    data class AreaEntity(
       @PrimaryKey val id:String,
       val title:String,
       @ColumnInfo(name = "area_type_id") val areaTypeId:Int,
       @ColumnInfo(name = "is_active") val isActive:Boolean
    )

    @Entity(tableName = "AREA_TYPE")
    data class AreaTypeEntity(
       @PrimaryKey val id:String,
       val title:String,
       @ColumnInfo(name = "title") val parentAreaId : String
    )

    @Entity(tableName = "SAMPLE")
    data class SampleEntity(
       @PrimaryKey val id:String,
       val title:String,
    )

    @Entity(tableName = "PACK")
    data class PackEntity(
       @PrimaryKey val id:String,
       val title:String,
    )

    @Entity(tableName = "UNIT")
    data class UnitEntity(
       @PrimaryKey val id:String,
       @ColumnInfo(name = "sample_id") val parentAreaId : String,
       @ColumnInfo(name = "area_id") val areaId:Int,
       @ColumnInfo(name = "pack_type_id") val packTypeId: Int,
       @ColumnInfo(name = "is_active") val isActive:Boolean
    )

UNIT table has three foreign keys : sample_id, area_id, pack_id
Every area has one-to-one relationship with area type.

I have an AreaPOJO for Area-AreaType Relationship:

     data class AreaPOJO (
           @Embedded val areaEntity : AreaEntity

           @Relation (
                   parentColumn = "area_id",
                   entityColumn = "id"
           )
           val areaTypeEntity : AreaTypeEntity
     )

Visual view of tables (https://i.sstatic.net/bXzl5.png)

So I assume that I will have a POJO for UNIT for the Relationships like this:

     data class UnitPOJO (
           @Embedded val unitEntity : UnitEntity

           @Relation (
                   parentColumn = "area_id",
                   entityColumn = "id"
           )
           val areaEntity : AreaEntity
           
           @Relation (
                   parentColumn = "pack_id",
                   entityColumn = "id"
           )
           val packEntity : PackEntity
           
           @Relation (
                   parentColumn = "sample_id",
                   entityColumn = "id"
           )
           val sampleEntity : SampleEntity
      )

With this POJO, I can get AreaEntity,SampleEntity,UnitEntity but I can't get AreaTypeEntity for UnitPOJO. When I use AreaPOJO instead of AreaEntity, I have a compilation error which tells me to use "prefix" for AreaPOJO. When I use prefix, this time AreaPOJO gives an error that it can't find the column names for relationship.

So I am stuck :) Briefly I need all the fields from all five tables for this query :

    "SELECT * FROM UNIT 
              INNER JOIN AREA ON UNIT.AREA_ID = AREA.ID
              INNER JOIN AREA_TYPE ON AREA.AREA_TYPE_ID = AREA_TYPE.ID 
              INNER JOIN SAMPLE ON UNIT.SAMPLE_ID = SAMPLE.ID 
              INNER JOIN PACK ON UNIT.PACK_ID = PACK.ID"

Solution

First the use of prefix, this is an option to circumvent the ambiguous column names (e.g. which id column is the correct one to use? (rhetorical)) BUT you would then have to play around with the queries to include AS (implicitly or explicitly) to rename the extracted columns.

I would suggest that using unique column names is the way to avoid such ambiguities.

Onto the grandparent/grandchild.

In short you are close BUT you retrieve an AreaPOJO (Area with Type) not an AreaEntity, but you then have to tell Room to use the AreaEntity class (as that is the class used to ascertain the columns for the AreaEntity and then the @relation in the AreaPOJO knows to get the inderlying AreaType).

So although untested but successfully compiled consider the following:-

@Entity(tableName = "UNIT")
data class UnitEntity(
    @PrimaryKey val id:String,
    @ColumnInfo(name = "sample_id") val parentAreaId : String,
    @ColumnInfo(name = "area_id") val areaId:Int,
    @ColumnInfo(name = "pack_type_id") val packTypeId: Int,
    @ColumnInfo(name = "is_active") val isActive:Boolean
)
@Entity(tableName = "AREA_TYPE")
data class AreaTypeEntity(
    @PrimaryKey @ColumnInfo(name = "area_type_id") val id:String, //<<<<< unique name
    val title:String,
    @ColumnInfo(name = "area_type_title") val parentAreaId : String
)

data class AreaPOJO(
    @Embedded val areaEntity : AreaEntity,
    @Relation(
        parentColumn = "area_type_id", //<<<<< changed accrodingly
        entityColumn = "area_type_id" //<<<<< changed accordingly
    )
    val areaTypeEntity : AreaTypeEntity
)

data class UnitPOJO (
    @Embedded val unitEntity : UnitEntity,

    @Relation (
        entity = AreaEntity::class, //<<<<< ADDED 
        parentColumn = "area_id",
        entityColumn = "area_id"
    )
    val areaWithAreaType : AreaPOJO,
    @Relation (
        parentColumn = "pack_type_id",
        entityColumn = "pack_id"
    )
    val packEntity : PackEntity,
    @Relation (
        parentColumn = "sample_id",
        entityColumn = "sample_id"
    )
    val sampleEntity : SampleEntity
)

Notice the pack_type_id and pack_id in the UnitPOJO as opposed to sample_id and sample_id for the sample reference/relationship.
- I would suggest considering using unique names e.g. pack_type_id is referencing/mapping the relationship between the unit and the pack, perhaps naming the column pack_id_map in the Unit. Thus the column names are then more descriptive and also unique. The downside is that there is more coding.

using the above the @Query could be :-

@Transaction
@Query("SELECT * FROM UNIT")
fun getUnitsWithRelations(): List<UnitPOJO>

Obviously adjusted according if using Flow/Live Data and so on.

Saying that, the above is inefficient as when Room processes an @Relation it builds and underlying query per @Relation that gets ALL the children from the parent (I believe on a per parent basis). In your case, it appears that you have 1 to many relationships thus @Embedded can be used BUT the query has to be more complex.

Working Example

The following is a working example based upon your code that

uses both @Relation and @Embedded resolutions
- The @Relation POJO's are UnitPOJO and AreaPOJO
- The @Embedded versions are prefixed with Alternative
Adds data (3 rows into each table, except 5 Units)
Extracts the Unit's and the related data using both alternatives
includes Foreign Key constraints that enforce referential and maintain integrity see https://sqlite.org/foreignkeys.html

It should be noted that some changes have been made as I believe that you some unusual and at a guess unecesassry relationships. e.g. You appear to have Area relate to AreaType both ways when only one is required. That is an Area will have an AreaType as a parent but if an ArearType also has an Area as a parent then you get the chicken and egg scenario.

The assumption has been made that an Area has one of the many available AreaTypes as a parent.

First the classes (see comments) :-

@Entity(
    tableName = "AREA",

    /* Enforces/Maintains referential Integrity */
    /* i.e does not allow orphans */
    foreignKeys = [
        ForeignKey(
            entity =  AreaTypeEntity::class,
            parentColumns =  ["area_type_id"],
            childColumns = ["area_type_id_map" ],
            onDelete = ForeignKey.CASCADE /* ????? */,
            onUpdate = ForeignKey.CASCADE /* ????? */
        )
    ]
)
data class AreaEntity(
    @PrimaryKey @ColumnInfo(name = "area_id")val id:String, //<<<<< unique name
    @ColumnInfo(name = "area_title") val title:String,
    @ColumnInfo(name = "area_type_id_map") val areaTypeId:String, //<<<<< see Area Type
    @ColumnInfo(name = "area_is_active") val isActive:Boolean
)
@Entity(tableName = "SAMPLE")
data class SampleEntity(
    @PrimaryKey @ColumnInfo(name = "sample_id") val id:String, //<<<<< unique name
    @ColumnInfo(name = "sample_title") val title:String,
)
@Entity(tableName = "PACK")
data class PackEntity(
    @PrimaryKey @ColumnInfo(name = "pack_id") val id:String, //<<<<< unique name
    @ColumnInfo(name = "pack_title") val title:String,  //<<<<< unique name
)

@Entity(
    tableName = "UNIT",
    foreignKeys = [
        ForeignKey(
            entity = SampleEntity::class,
            parentColumns = ["sample_id"],
            childColumns = ["sample_id_map"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = AreaEntity::class,
            parentColumns = ["area_id"],
            childColumns = ["area_id_map"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = PackEntity::class,
            parentColumns = ["pack_id"],
            childColumns = ["pack_id_map"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class UnitEntity(
    @PrimaryKey val id:String,
    @ColumnInfo(name = "sample_id_map") val sampleId : String,
    @ColumnInfo(name = "area_id_map") val areaId:String,
    @ColumnInfo(name = "pack_id_map") val packTypeId: String,
    @ColumnInfo(name = "unit_is_active") val isActive:Boolean
)
@Entity(
    tableName = "AREA_TYPE"
)
data class AreaTypeEntity(
    @PrimaryKey @ColumnInfo(name = "area_type_id") val id:String, //<<<<< unique name
    @ColumnInfo(name = "area_type_title") val title:String,

    /* ???? should an area type have an area as a parent? potential issues if so */
    /* commented out
    @ColumnInfo(name = "area_type_title") val parentAreaId : String //<<<<< unique name
    */
)

data class AreaPOJO(
    @Embedded val areaEntity : AreaEntity,
    @Relation(
        parentColumn = "area_type_id_map", //<<<<< changed accordingly
        entityColumn = "area_type_id" //<<<<< changed accordingly
    )
    val areaTypeEntity : AreaTypeEntity
)

data class UnitPOJO (
    @Embedded val unitEntity : UnitEntity,
    @Relation (
        entity = AreaEntity::class, //<<<<< ADDED
        parentColumn = "area_id_map",
        entityColumn = "area_id"
    )
    val areaWithAreaType : AreaPOJO,
    @Relation (
        parentColumn = "pack_id_map",
        entityColumn = "pack_id"
    )
    val packEntity : PackEntity,
    @Relation (
        parentColumn = "sample_id_map",
        entityColumn = "sample_id"
    )
    val sampleEntity : SampleEntity
)
data class AlternativeAreaPOJO (
        @Embedded val areaEntity: AreaEntity,
        @Embedded val areaTypeEntity: AreaTypeEntity
        )

data class AlternativeUnitPOJO (
        @Embedded val unitEntity: UnitEntity,
        @Embedded val alternativeAreaPOJO: AlternativeAreaPOJO,
        @Embedded val packEntity: PackEntity,
        @Embedded val sampleEntity: SampleEntity
        )

The @Dao annotated interface AllDao :-

@Dao
interface AllDAO {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(areaEntity: AreaEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(unitEntity: UnitEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(sampleEntity: SampleEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(packEntity: PackEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(areaTypeEntity: AreaTypeEntity)

    @Transaction
    @Query("SELECT * FROM UNIT")
    fun getUnitsWithRelations(): List<UnitPOJO>

    @Query("SELECT * FROM UNIT " +
            "INNER JOIN AREA ON UNIT.area_id_map = AREA.area_id " +
            "INNER JOIN AREA_TYPE ON AREA.area_type_id_map = AREA_TYPE.area_type_id " +
            "INNER JOIN SAMPLE ON UNIT.sample_id_map = SAMPLE.sample_id " +
            "INNER JOIN PACK ON UNIT.pack_id_map = PACK.pack_id")
    fun getAlternativeUnitsWithRelations(): List<AlternativeUnitPOJO>
}

The @Database annotated class TheDatabase :-

@Database(entities = [
    AreaEntity::class,
    SampleEntity::class,
    PackEntity::class,
    UnitEntity::class,
    AreaTypeEntity::class
                     ],
    version = 1,
    exportSchema = false
)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDAO(): AllDAO

    companion object {
        private var instance: TheDatabase? = null
        fun getInstance(context: Context): TheDatabase {
            if (instance == null) {
                instance = Room.databaseBuilder(
                    context,
                    TheDatabase::class.java,
                    "the_database.db"
                )
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

Note for convenience and brevity .allowMainThreadQueries has been utilised.

Code within an activity (designed to run just the once):-

class MainActivity : AppCompatActivity() {

    lateinit var db: TheDatabase
    lateinit var dao: AllDAO
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)
        dao = db.getAllDAO()

        val TAG = "DBINFO"

        val p1 = PackEntity("P001","Pack1")
        val p2 = PackEntity("P002","Pack2")
        val p3 = PackEntity("P003","Pack3")
        dao.insert(p1)
        dao.insert(p2)
        dao.insert(p3)
        val s1 = SampleEntity("S001","Sample1")
        val s2 = SampleEntity("S002","Sample2")
        val s3 = SampleEntity("S003","Sample3")
        dao.insert(s1)
        dao.insert(s2)
        dao.insert(s3)
        val at1 = AreaTypeEntity("AT001","AreaType1")
        val at2 = AreaTypeEntity("AT002","AreaType2")
        val at3 = AreaTypeEntity("AT003","AreaType3",)
        dao.insert(at1)
        dao.insert(at2)
        dao.insert(at3)
        val a1 = AreaEntity("A001","Area1",at1.id,true)
        val a2 = AreaEntity("A002","Area2",at2.id,false)
        val a3 = AreaEntity("A003","Area3",at1.id,true)
        dao.insert(a1)
        dao.insert(a2)
        dao.insert(a3)

        dao.insert(UnitEntity("U001",s1.id,a1.id,p1.id,true))
        dao.insert(UnitEntity("U002",s2.id,a2.id,p2.id, false))
        dao.insert(UnitEntity("U003",s3.id,a3.id,p3.id,true))
        dao.insert(UnitEntity("U004",s1.id,a2.id,p3.id,false))
        dao.insert(UnitEntity("U005",s3.id,a2.id,p1.id, true))

        for(uwr in dao.getUnitsWithRelations()) {
            Log.d(TAG,
                "Unit is ${uwr.unitEntity.id} " +
                    "Active = ${uwr.unitEntity.isActive} " +
                        "Sample is ${uwr.sampleEntity.title} " +
                        "Area is ${uwr.areaWithAreaType.areaEntity.title} " +
                        "AreaType is ${uwr.areaWithAreaType.areaTypeEntity.title}"
            )
        }

        for (auwr in dao.getAlternativeUnitsWithRelations()) {
            Log.d(TAG,
                "Unit is ${auwr.unitEntity.id} " +
                        "Active is ${auwr.unitEntity.isActive} " +
                        "Sample is ${auwr.sampleEntity.title} " +
                        "Area is ${auwr.alternativeAreaPOJO.areaEntity.title} " +
                        "AreaType is ${auwr.alternativeAreaPOJO.areaTypeEntity.title}"
            )
        }
    }
}

Last the resultant output from the log:-

2022-04-05 09:32:40.528 D/DBINFO: Unit is U001 Active = true Sample is Sample1 Area is Area1 AreaType is AreaType1
2022-04-05 09:32:40.528 D/DBINFO: Unit is U002 Active = false Sample is Sample2 Area is Area2 AreaType is AreaType2
2022-04-05 09:32:40.529 D/DBINFO: Unit is U003 Active = true Sample is Sample3 Area is Area3 AreaType is AreaType1
2022-04-05 09:32:40.529 D/DBINFO: Unit is U004 Active = false Sample is Sample1 Area is Area2 AreaType is AreaType2
2022-04-05 09:32:40.529 D/DBINFO: Unit is U005 Active = true Sample is Sample3 Area is Area2 AreaType is AreaType2

2022-04-05 09:32:40.537 D/DBINFO: Unit is U001 Active is true Sample is Sample1 Area is Area1 AreaType is AreaType1
2022-04-05 09:32:40.537 D/DBINFO: Unit is U002 Active is false Sample is Sample2 Area is Area2 AreaType is AreaType2
2022-04-05 09:32:40.537 D/DBINFO: Unit is U003 Active is true Sample is Sample3 Area is Area3 AreaType is AreaType1
2022-04-05 09:32:40.537 D/DBINFO: Unit is U004 Active is false Sample is Sample1 Area is Area2 AreaType is AreaType2
2022-04-05 09:32:40.537 D/DBINFO: Unit is U005 Active is true Sample is Sample3 Area is Area2 AreaType is AreaType2

i.e. the results are the same for the 2 alternatives and of course the relationships are working as expected