How to work this nested relationship in Room?

Question

I could use a little help. I have created all the tables, and I can create a relationship to retrieve the application, but I do not know how I can retrieve the list of vehicle brands with the models.

@Entity(tableName = "application_table", indices = [Index(value = ["name"], unique = true)])
data class ApplicationItem(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id") val id: Int? = 0,
    @ColumnInfo(name = "name") val name: String
)

data class ApplicationWithBrandAndModel(
    @Embedded val application: ApplicationItem,
    @Relation(
        entity = BrandOfVehicleItem::class,
        parentColumn = "userId",
        entityColumn = "brandId"
    )
    val brands: List<BrandWithModel>
)

data class BrandWithModel(
    @Embedded val brand: BrandOfVehicleItem,
    @Relation(
        parentColumn = "path",
        entityColumn = "brandCreatorId"
    )
    val models: List<ModelOfVehicleItem>
)

Answer 1

In short you need to utilise the AppBrandCrossRef table to reference (associate) the ApplicationItem with the Brand to get the list of BrandWithModel's.

The keyword here, that Room uses is associateBy, so in the @Relation you need to specify the association using the associateBy argument.

The associateBy argument itself takes a Junction argument which is where you define the cross reference table and the respective columns.

So I believe that you want:-

data class ApplicationWithBrandAndModel(
    @Embedded val application: ApplicationItem,

    /*
    @Relation(
        entity = Brand::class,
        parentColumn = "userId", ??????? 
        entityColumn = "brandId"
    )

     */
    @Relation(
        entity = Brand::class,
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = ApplicationBrandCrossRef::class,
            parentColumn = "appId", // column in cross ref table that references the parent (application)
            entityColumn = "brandId" // column in cross ref table that references the child (brand)
        )
    )
    val brands: List<BrandWithModel>
)

The following is a working example.

• Note that changes have been made as you have long's referencing strings and you also have (as commented out above) a userId, which isn't in your diagram.

So the entities used to demonstrate are :-

Model

@Entity(
    indices = [Index("brandCreatorId", unique = false)],
    foreignKeys = [
        ForeignKey(
            entity = Brand::class,
            parentColumns = ["id"],
            childColumns = ["brandCreatorId"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE)
    ]
)
data class Model(
    @PrimaryKey
    val id: Long? = null,
    val path: String,
    val code: String,
    val value: String,
    val brandCreatorId: Long
)

Brand

@Entity(tableName = "brand_table")
data class Brand(
    @PrimaryKey
    val id: Long? = null,
    val path: String,
    val code: String,
    val value: String
)

ApplicationItem

@Entity(tableName = "application_table", indices = [Index(value = ["name"], unique = true)])
data class ApplicationItem(
    @PrimaryKey
    @ColumnInfo(name = "id") val id: Long? = null,
    @ColumnInfo(name = "name") val name: String
)

ApplicationBrandCrossRef

@Entity(
    primaryKeys = ["appId","brandId"],
    indices = [ Index(value = ["brandId"])],
    foreignKeys = [
        ForeignKey(
            entity = ApplicationItem::class,
            parentColumns = ["id"],
            childColumns = ["appId"],
            onDelete =  ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE),
        ForeignKey(
            entity = Brand::class,
            parentColumns = ["id"],
            childColumns = ["brandId"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class ApplicationBrandCrossRef(
    val appId: Long,
    val brandId: Long
)

BrandWithModel is

data class BrandWithModel(
    @Embedded val brand: Brand,
    @Relation(
        parentColumn = "id",
        entityColumn = "brandCreatorId"
    )
    val models: List<Model>
)

ApplicationWithBrandAndModel is

data class ApplicationWithBrandAndModel(
    @Embedded val application: ApplicationItem,

    /*
    @Relation(
        entity = Brand::class,
        parentColumn = "userId", ???????
        entityColumn = "brandId"
    )

     */
    @Relation(
        entity = Brand::class,
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = ApplicationBrandCrossRef::class,
            parentColumn = "appId", // column in cross ref table that references the parent (application)
            entityColumn = "brandId" // column in cross ref table that references the child (brand)
        )
    )
    val brands: List<BrandWithModel>
)

Dao's in AllDao are :-

@Dao
abstract class AllDao {

    @Insert
    abstract fun insert(brand: Brand): Long
    @Insert
    abstract fun insert(model: Model): Long
    @Insert
    abstract fun insert(applicationItem: ApplicationItem): Long
    @Insert
    abstract fun insert(applicationBrandCrossRef: ApplicationBrandCrossRef)

    @Query("SELECT * FROM application_table")
    @Transaction
    abstract fun getApplicationItemWithBrandAndTheModels(): List<ApplicationWithBrandAndModel>
}

The following was utilised to test:-

    db = TheDatabase.getInstance(this)
    dao = db.getHymnDao()
    allDao = db.getAllDao()

    var ai1 = allDao.insert(ApplicationItem(name = "A1"))
    var ai2 = allDao.insert(ApplicationItem(name = "A2"))
    var ai3 = allDao.insert(ApplicationItem(name = "A3"))
    var ai4 = allDao.insert(ApplicationItem(name = "A4"))
    var b1 = allDao.insert(Brand(path = "patha", code = "codea", value = "vala"))
    var b2 = allDao.insert(Brand(path = "pathb",code = "codeb",value = "valb"))
    var b3 = allDao.insert(Brand(path = "pathc",code = "codec",value = "valc"))
    allDao.insert(Model(path = "ma",code = "ma",value = "ma",brandCreatorId = b1))
    allDao.insert(Model(path = "mb", code = "mb", value = "mb", brandCreatorId = b1))
    allDao.insert(Model(path = "mc",code = "mc", value = "mc",brandCreatorId = b2))
    allDao.insert(Model(path = "md",code = "md", value = "md", brandCreatorId = b2))
    allDao.insert(Model(path = "me", code = "me", value = "me", brandCreatorId = b2))
    allDao.insert(ApplicationBrandCrossRef(ai1,b2))
    allDao.insert(ApplicationBrandCrossRef(ai1,b3))
    allDao.insert(ApplicationBrandCrossRef(ai2,b1))
    allDao.insert(ApplicationBrandCrossRef(ai3,b1))
    allDao.insert(ApplicationBrandCrossRef(ai3,b2))
    allDao.insert(ApplicationBrandCrossRef(ai3,b3))
    for(a: ApplicationWithBrandAndModel in allDao.getApplicationItemWithBrandAndTheModels()) {
        Log.d(TAG,"AppItem is ${a.application.name}")
        for(b: BrandWithModel in a.brands) {
            Log.d(TAG,"\tBrand is ${b.brand.code}")
            for(m: Model in b.models) {
                Log.d(TAG,"\t\tModel is ${m.code}")
            }
        }
    }

The Result output to the log :-

D/APPINFO: AppItem is A1
D/APPINFO:  Brand is codeb
D/APPINFO:      Model is mc
D/APPINFO:      Model is md
D/APPINFO:      Model is me
D/APPINFO:  Brand is codec
D/APPINFO: AppItem is A2
D/APPINFO:  Brand is codea
D/APPINFO:      Model is ma
D/APPINFO:      Model is mb
D/APPINFO: AppItem is A3
D/APPINFO:  Brand is codea
D/APPINFO:      Model is ma
D/APPINFO:      Model is mb
D/APPINFO:  Brand is codeb
D/APPINFO:      Model is mc
D/APPINFO:      Model is md
D/APPINFO:      Model is me
D/APPINFO:  Brand is codec
D/APPINFO: AppItem is A4

• i.e. the expected result