Nested embedded lists on Room

Question

Using room I'm trying to have an object of type Something, that contains a list of Stuff, and each item of that list contains another list of Things.

data class rootWrapper(
        @Embeded
        var somethingEntity: SomethingEntity
         @Relation(
            parentColumn = "id",
            entityColumn = "idSomething",
            associatedBy = Junction(SomethingWithStuffEntity::class)
     )
     var listOfStuff: MutableList<StuffWithThingsInsideWrapper>
)

data class StuffWithThingsInsideWrapper(
        @Embeded
        var stuff: stuffEntity
         @Relation(
            parentColumn = "id",
            entityColumn = "idStuff",
            associatedBy = Junction(StuffWithThingsEntity::class)
     )
     var listOfThings: MutableList<ThingsEntity>
)

The problem is that since StuffWithThingsInsided doesn't have an ID. I get a Cannot find the parent entity column id in com.test.room.entity.StuffWithThingsWapper. I don't know if what I'm trying to achieve is even possible, I couldn't find any example out there. Any suggestion?

Answer 1

I don't know if what I'm trying to achieve is even possible, I couldn't find any example out there. Any suggestion?

It is possible. Perhaps consider the following.

When you associateBy you are saying that you are using an associative table to access the parent and children.

Such a table would have at least two columns, in your case one column to reference the stuffEntity and the other column to reference the relevant ThingEntity.

As such there are 4 columns involved in an @Relation with associateBy :-

• The Parent i.e. the @Embedded
• The Child(ren) i.e. the @Relation
• The column in the associative table that references the @Embedded
• The column in the associative table that references the @Relation

The first two are as per the parent and entity parameters of the @Relation, the second two are as per the 'Junction'.

Furthermore when including a hierarchy/levels there is a further complication, which you have encountered, is that the column to variable matching requires parent entity of the subordinate which is not in the subordinate. You need to specify the entity class as the respective entity NOT the class of the POJO being extracted.

So you could have something like :-

data class StuffWithThingsInsideWrapper(
    @Embedded
    var stuff: StuffEntity,
    @Relation(
        entity = ThingsEntity::class,
        parentColumn = "id",
        entityColumn = "id",
        associateBy = (
                Junction(
                    value = StuffWithThingsEntity::class,
                    parentColumn = "idStuff",
                    entityColumn = "idThings"
                )
                )
    )
    var listOfThings: List<ThingsEntity>
)

and :-

data class RootWrapper(
    @Embedded
    var somethingEntity: SomethingEntity,
    @Relation(
        entity = StuffEntity::class, //<<<<< The entity behind a Something With Stuff
        parentColumn = "id",
        entityColumn = "id",
        associateBy = (
                Junction(
                    SomethingWithStuffEntity::class,
                    parentColumn = "idSomething",
                    entityColumn = "idStuff"
                )
                )
    )
    var listOfStuff: List<StuffWithThingsInsideWrapper>
)

• NOTE as the underlying entities have not been included the column names have been guessed.

Working Example/Demo

In addition to the above the following classes were used :-

Things

@Entity
data class ThingsEntity(
    @PrimaryKey
    var id: Long? = null,
    var name: String
)

Stuff

@Entity
data class StuffEntity(
    @PrimaryKey
    var id: Long? = null,
    var name: String
)

Something

@Entity
data class SomethingEntity(
    @PrimaryKey
    var id: Long? = null,
    var name: String
)

StuffWithThings

@Entity(
    primaryKeys = ["idStuff","idThings"],
    foreignKeys = [
        ForeignKey(
            entity =  StuffEntity::class,
            parentColumns = ["id"],
            childColumns = ["idStuff"]
        ), ForeignKey(
            entity = ThingsEntity::class,
            parentColumns = ["id"],
            childColumns = ["idThings"]
        )
    ]
)
data class StuffWithThingsEntity(
    var idStuff: Long,
    @ColumnInfo(index = true)
    var idThings: Long
)

• ForeignKeys are optional, they enforce referential integrity

SomethingWithStuffEntity

@Entity(
    primaryKeys = ["idSomething","idStuff"],
    foreignKeys = [
        ForeignKey(
            entity = SomethingEntity::class,
            parentColumns = ["id"],
            childColumns = ["idSomething"]
        ), ForeignKey(
            entity =  StuffEntity::class,
            parentColumns = ["id"],
            childColumns = ["idStuff"])
    ]
)
data class SomethingWithStuffEntity(
    var idSomething: Long,
    @ColumnInfo(index = true)
    var idStuff: Long
)

AllDao

@Dao
interface AllDao {
    @Insert
    fun insert(thingsEntity: ThingsEntity): Long
    @Insert
    fun insert(stuffEntity: StuffEntity): Long
    @Insert
    fun insert(somethingEntity: SomethingEntity): Long
    @Insert
    fun insert(stuffWithThingsEntity: StuffWithThingsEntity): Long
    @Insert
    fun insert(somethingWithStuffEntity: SomethingWithStuffEntity)
    @Query("SELECT * FROM somethingentity")
    fun getRootWrapperList(): List<RootWrapper>

}

TheDatabase

@Database(entities = [ThingsEntity::class,StuffEntity::class,SomethingEntity::class,StuffWithThingsEntity::class,SomethingWithStuffEntity::class], version = 1)
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,
                    "thedatabase.db"
                )
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

• for brevity/convenience .allowMainThreadQueries has been used.

Finally putting it all together in an activity :-

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 thing1Id = dao.insert(ThingsEntity(name = "Thing1"))
        val thing2Id = dao.insert(ThingsEntity(name = "Thing2"))
        val thing3Id = dao.insert(ThingsEntity(name = "Thing3"))
        val stuff1Id = dao.insert(StuffEntity(name = "Stuff1"))
        val stuff2Id = dao.insert(StuffEntity(name = "Stuff2"))
        val something1Id = dao.insert(SomethingEntity(name = "Something1"))
        val something2Id = dao.insert(SomethingEntity(name = "Something2"))
        val something3Id = dao.insert(SomethingEntity(name = "Something3"))

        dao.insert(StuffWithThingsEntity(stuff1Id,thing1Id))
        dao.insert(StuffWithThingsEntity(stuff1Id,thing2Id))
        dao.insert(StuffWithThingsEntity(stuff1Id,thing3Id))
        dao.insert(StuffWithThingsEntity(stuff2Id,thing3Id))
        dao.insert(StuffWithThingsEntity(stuff2Id,thing2Id))

        dao.insert(SomethingWithStuffEntity(something1Id,stuff1Id))
        dao.insert(SomethingWithStuffEntity(something1Id,stuff2Id))
        dao.insert(SomethingWithStuffEntity(something2Id,stuff2Id))

        for(rw: RootWrapper in dao.getRootWrapperList()) {
            Log.d("RWINFO","Something is ${rw.somethingEntity.name}")
            for (swt: StuffWithThingsInsideWrapper in rw.listOfStuff) {
                Log.d("RWINFO","\tStuff is ${swt.stuff.name}")
                for (t: ThingsEntity in swt.listOfThings) {
                    Log.d("RWINFO","\t\tThings is ${t.name}")
                }
            }
        }
    }
}

Running the above (first time) then the result output to the log is :-

2021-11-25 07:04:58.828 D/RWINFO: Something is Something1
2021-11-25 07:04:58.828 D/RWINFO:   Stuff is Stuff1
2021-11-25 07:04:58.828 D/RWINFO:       Things is Thing1
2021-11-25 07:04:58.828 D/RWINFO:       Things is Thing2
2021-11-25 07:04:58.828 D/RWINFO:       Things is Thing3
2021-11-25 07:04:58.828 D/RWINFO:   Stuff is Stuff2
2021-11-25 07:04:58.828 D/RWINFO:       Things is Thing2
2021-11-25 07:04:58.828 D/RWINFO:       Things is Thing3

2021-11-25 07:04:58.828 D/RWINFO: Something is Something2
2021-11-25 07:04:58.828 D/RWINFO:   Stuff is Stuff2
2021-11-25 07:04:58.829 D/RWINFO:       Things is Thing2
2021-11-25 07:04:58.829 D/RWINFO:       Things is Thing3

2021-11-25 07:04:58.829 D/RWINFO: Something is Something3