androidandroid-sqliteandroid-room

Handling nested entities in Room database

I have these Entities I would like to insert in the database: It seems I can't get to build the nested entities right. I need your help in this

[1] ArticleEntity

@Entity(
tableName = "articles",
foreignKeys = [
    ForeignKey(
        entity = PartCargo::class,
        parentColumns = ["tId"],
        childColumns = ["partTId"],
        onDelete = ForeignKey.CASCADE
        )
   ]
)
data class ArticleEntity(
    @PrimaryKey(autoGenerate = true)
    val tId: Long = 0,
    val partTId: Long = -1,
    val id: String?,
    val name: String?,
) : Serializable

[2] PartEntity

@Entity(
tableName = "parts",
foreignKeys = [
    ForeignKey(
        entity = DetailsCargo::class,
        parentColumns = ["tId"],
        childColumns = ["detailsTId"],
        onDelete = ForeignKey.CASCADE
     )
   ]
)
data class PartCargo(
    @PrimaryKey(autoGenerate = true)
    val tId: Long = 0,
    val detailsTId: Long = -1,
    val id: String?,
    val name: String?,
) : Serializable

data class PartEntity(
    @Embedded
    val part: PartCargo,

    @Relation(parentColumn = "tId", entityColumn = "partTId")
    val partArticles: List<ArticleEntity>?
) : Serializable

[3] DetailsEntity

@Entity(tableName = "details")
data class DetailsCargo(
    @PrimaryKey(autoGenerate = true)
    val tId: Long = 0,
    val id: String?,
    val name: String?
) : Serializable

data class DetailsEntity(
    @Embedded
    val details: DetailsCargo,

    @Relation(parentColumn = "tId", entityColumn = "detailsTId")
    val parts: List<PartEntity>?

) : Serializable

This is DetailsDAO

@Dao
interface DetailsDAO {

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insertPartCargo(parent: PartCargo): Long

    @Transaction
    fun insertAllParts(parentId: Long, parts: List<PartEntity>) {
        parts.forEach { part ->
            val cargoParentId = insertPartCargo(part.part.copy(detailsTId = parentId))
            part.partArticles?.let { items ->
                val children = items.map { it.copy(partTId = cargoParentId) }
                insertArticleEntities(children)
            }
        }
    }


    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insertDetailsCargo(parent: DetailsCargo): Long

    @Transaction
    fun insertDetails(details: DetailsEntity) {
        val parentId = insertDetailsCargo(details.details)

        details.parts?.let { items ->
            insertAllParts(parentId, items)
        }
    }

    @Transaction
    @Query("SELECT * FROM details WHERE :id = id")
    fun getDetails(id: String): DetailsEntity?
}

Now, I get this error while building the app:

Cannot find the child entity column `detailsTId` in PartEntity.

Thanks in advance

Solution

  • Are my entities built right?

    Not fully checking the relationships/entities

    If not, what is the solution?

    BUT if you are using hierarchical (nested) POJO classes then the @Relation annotation that is lower (a child of a child) should reflect the Entity (the table where the columns exist) not the POJO.

    @Relation has an entity parameter, this should be used in such cases, as such I believe that your issue may be resolved by using:-

    data class DetailsEntity(
    @Embedded
    val details: DetailsCargo,
    @Relation(entity = PartCargo::class, parentColumn = "tId", entityColumn = "detailsTId")
    val parts: List<PartEntity>?

) : Serializable

    Without (comment out) then:-

    enter image description here

    With:-

    enter image description here

    You may wish to heed the 3 warnings and use the @ColumnInfo on the respective fields e.g.

    ....
@ColumnInfo(index = true)
val partTId: Long = -1,
....

    Additional Re Comment

    Just one question, On the "getDetails" Query, will it fetch the whole "PartEntity" (PartCargo + ArticleEntity), or just the "PartCargo" since it is the one defined in the "@Relation entity" ??

    It will/should retrieve everything as per the POJOs the @Relation is telling Room how to get the data from the tables/relationships to build the resultant POJO(s).

    Perhaps consider this result (code follows):-

    D/DBINFO: Detail ID is DC002 NAME is DCNAME002 TID is 2. It has 2 Parts. They are:-
        PE ID is PC004 NAME is PCNAME004 TID is 4. It has 3 articles. They are:-
            Art ID is AE010 NAME is AENAME010 TID is 10
            Art ID is AE011 NAME is AENAME011 TID is 11
            Art ID is AE012 NAME is AENAME012 TID is 12
        PE ID is PC005 NAME is PCNAME005 TID is 5. It has 4 articles. They are:-
            Art ID is AE006 NAME is AENAME006 TID is 6
            Art ID is AE007 NAME is AENAME007 TID is 7
            Art ID is AE008 NAME is AENAME008 TID is 8
            Art ID is AE009 NAME is AENAME009 TID is 9

    Based upon your code(amended as per the question, also with some extra code to use just the single Dao) this is some activity code (note for the brevity of the demo main thread has been used) that generated result above:-

    class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: DetailsDAO
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = TheDatabase.getInstance(this)
        dao = db.getDetailsDao()

        val dcId1 = dao.insertDetailsCargo(DetailsCargo(id = "DC001", name = "DCNAME001"))
        val dcId2 = dao.insertDetailsCargo(DetailsCargo(id = "DC002", name = "DCNAME002"))
        val dcId3 = dao.insertDetailsCargo(DetailsCargo(id = "DC003", name = "DCNAME003"))

        val pcId1 = dao.insertPartCargo(PartCargo(id = "PC001", name = "PCNAME001", detailsTId = dcId1))
        val pcId2 = dao.insertPartCargo(PartCargo(id = "PC002", name = "PCNAME002", detailsTId = dcId1))
        val pcId3 = dao.insertPartCargo(PartCargo(id = "PC003", name = "PCNAME003", detailsTId = dcId1))
        val pcId4 = dao.insertPartCargo(PartCargo(id = "PC004", name = "PCNAME004", detailsTId = dcId2))
        val pcId5 = dao.insertPartCargo(PartCargo(id = "PC005", name = "PCNAME005", detailsTId = dcId2))
        val pcId6 = dao.insertPartCargo(PartCargo(id = "PC006", name = "PCNAME006", detailsTId = dcId3))

        dao.insertArticleEntity(ArticleEntity(partTId =  pcId6,id = "AE001", name = "AENAME001"))
        dao.insertArticleEntity(ArticleEntity(partTId =  pcId6,id = "AE002", name = "AENAME002"))
        dao.insertArticleEntity(ArticleEntity(partTId =  pcId6,id = "AE003", name = "AENAME003"))
        dao.insertArticleEntity(ArticleEntity(partTId =  pcId6,id = "AE004", name = "AENAME004"))
        dao.insertArticleEntity(ArticleEntity(partTId =  pcId6,id = "AE005", name = "AENAME005"))
        dao.insertArticleEntity(ArticleEntity(partTId = pcId5,id = "AE006", name = "AENAME006"))
        dao.insertArticleEntity(ArticleEntity(partTId = pcId5,id = "AE007", name = "AENAME007"))
        dao.insertArticleEntity(ArticleEntity(partTId = pcId5,id = "AE008", name = "AENAME008"))
        dao.insertArticleEntity(ArticleEntity(partTId = pcId5,id = "AE009", name = "AENAME009"))
        dao.insertArticleEntity(ArticleEntity(partTId = pcId4,id = "AE010", name = "AENAME010"))
        dao.insertArticleEntity(ArticleEntity(partTId = pcId4,id = "AE011", name = "AENAME011"))
        dao.insertArticleEntity(ArticleEntity(partTId = pcId4,id = "AE012", name = "AENAME012"))
        dao.insertArticleEntity(ArticleEntity(partTId = pcId3,id = "AE013", name = "AENAME013"))
        dao.insertArticleEntity(ArticleEntity(partTId = pcId3,id = "AE014", name = "AENAME014"))
        dao.insertArticleEntity(ArticleEntity(partTId = pcId2,id = "AE015", name = "AENAME015"))
        dao.insertArticleEntity(ArticleEntity(partTId = pcId1,id = "AE016", name = "AENAME016"))
        dao.insertArticleEntity(ArticleEntity(partTId = pcId1,id = "AE017", name = "AENAME017"))
        dao.insertArticleEntity(ArticleEntity(partTId = pcId1,id = "AE018", name = "AENAME018"))
        dao.insertArticleEntity(ArticleEntity(partTId = pcId1,id = "AE019", name = "AENAME019"))


        val dc = dao.getDetails("DC002")

        if (dc!=null) {
            val sbp = StringBuilder()
            for (p in dc.parts!!) {
                val sba = StringBuilder()
                for (a in p.partArticles!!) {
                    sba.append("\n\t\tArt ID is ${a.id} NAME is ${a.name} TID is ${a.tId}")
                }
                sbp.append("\n\tPE ID is ${p.part.id} NAME is ${p.part.name} TID is ${p.part.tId}. It has ${p.partArticles.size} articles. They are:-${sba}")
            }
            Log.d("DBINFO","Detail ID is ${dc.details.id} NAME is ${dc.details.name} TID is ${dc.details.tId}. It has ${dc.parts.size} Parts. They are:-${sbp}")
        } else {
            Log.d("DBINFO_OOOPS","DC WAS NULL!!!!!!!!!")
        }
    }
}