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.

Are my entities built right?
If not, what is the solution?

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

Note the entity that defines the PartCargo class otherwise the type of the field is used to ascertain the columns used to build the result
- that is the PartCargo class, which is the @Entity annotated class, has the fields (columns) that the underlying queries will extract.
- without the entity being specified then the PartEntity class is implied that that does not have fields that can be properly associated with the columns
you may wish to consider always coding the entity parameter when using the @Relation annotation.

Without (comment out) then:-

With:-

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

The single detail has 2 parts, one part has 3 articles, the other part has 4

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

You wan to pay particular attention to the code at the bottom that gets the detail and then loops through the parts and for each part loops through the article via the POJO's as opposed to the @Entity annotated being used (i.e. dc is a DetailsEntity object not a DetailsCargo, p is a PartsEntity object not a PartsCargo object)