Room @Relation annotation with a One To Many relationship

Issue

I have a POJO I'd like to get from the database which has a list of POJO's as a property. This, according to docs is doable via a @Relationship annotation. However, it's a one to many relationship where I don't directly reference the table/entity in question. How would I go about getting this back from the DB directly from the DAO? Is this even possible, or do I have to implement some intermediary binding logic manually? The POJO I'd like to get from DB:

data class Chore(
    var name: String,
    //This is the line that doesn't work
    @Relation(parentColumn = "id", entityColumn = "userId", entity = UserEntity::class)
    var contributingUsers: List<User>,
    @DrawableRes var drawableRes: Int,
    var done: Boolean
)

The User POJO I'd like to get automatically mapped:

data class User(
    val userName: String,
    @DrawableRes val userPhoto: Int
)

The One-To-Many reference table/entity:

@Entity(
    tableName = TableNames.CHORE_TO_USER,
    foreignKeys = [
        ForeignKey(
            entity = UserEntity::class,
            parentColumns = arrayOf("id"),
            childColumns = arrayOf("userId"),
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE,
        ),
        ForeignKey(
            entity = ChoreEntity::class,
            parentColumns = arrayOf("id"),
            childColumns = arrayOf("choreId"),
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE,
        )
    ],
    indices = [Index("choreId"), Index("userId")]
)
internal data class ChoreToUser(
    val userId: Int,
    val choreId: Int,
    val canFulfill: Boolean,
): BaseEntity()

The query:

    @Query("SELECT Chores.name, drawableRes," +
            "Users.name as userName, Users.imageRes as userPhoto, " +
            "(COUNT(ChoreLogs.id) = 1) as done " +
            "FROM Chores " +
            "LEFT JOIN ChoreToUsers ON ChoreToUsers.choreId = Chores.id " +
            "LEFT JOIN Users ON ChoreToUsers.userId = Users.id " +
            "LEFT JOIN ChoreLogs ON ChoreLogs.choreToUserId = ChoreToUsers.id")
    fun getChoreTiles(): List<Chore>

TLDR: I wanna embed a list of users into the Chore POJO. It's refferenced via an intermediary table. How would I go about doing this?

Solution

To use @Relation the parent table must be available so that the parent column can be found.

So you would need something along the lines of :-

data class Chore(
    @Embedded
    var choreEntity: ChoreEntity, //<<<<<
    var name: String,
    //This is the line that doesn't work
    @Relation(parentColumn = "id", entityColumn = "userId", entity = UserEntity::class)
    var contributingUsers: List<User>,
    @DrawableRes var drawableRes: Int,
    var done: Boolean
)

In short @Relation results in a sub query being invoked that retrieves ALL of the children of the parent (which MUST exist/be known) thus an @Embed of that parent is required. Note that these are for use by the convenience methods, which are a little restrictive in nature.

However, as you have an intermediate table (mapping/associative/reference .... table) then you need to tell Room about this by using the associateBy parameter to define the Junction

• there is no need for such a table for one-to-many relationships, such tables can be used but are really for many-many relationships.

@Relation will build the underlying query to access the children accordingly.

If you want the result of your query then a Chore object could just be:-

data class Chore(
    var name: String,
    var contributingUsers,
    @DrawableRes var drawableRes: Int,
    var done: Boolean
)

BUT a row would exist for every combination that is for every User that is related to a Chore there would be a row i.e. the result is the cartesian product.

If you wanted to build a Chore with a list of it's child Users then you would have to

• a) either process the entire result building the resultant List or
• b) just extract the respective Chore and then run a query per extracted Chore that returns the List.

Working Examples

Based upon UserEntity being :-

@Entity( tableName = TableNames.CHORE)
data class ChoreEntity(
    @PrimaryKey
    val id: Long?=null,
    val name: String,
    val drawableRes: Int
)

and UserEntity being :-

@Entity(tableName = TableNames.USER)
data class UserEntity(
    @PrimaryKey
    val id: Long? = null,
    val name: String,
    val imageRes: Int
    /* etc */
)

and ChoreToUser being:-

@Entity(
    tableName = TableNames.CHORE_TO_USER,
    foreignKeys = [
        ForeignKey(
            entity = UserEntity::class,
            parentColumns = arrayOf("id"),
            childColumns = arrayOf("userId"),
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE,
        ),
        ForeignKey(
            entity = ChoreEntity::class,
            parentColumns = arrayOf("id"),
            childColumns = arrayOf("choreId"),
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE,
        )
    ],
    // indices = [Index("choreId"), Index("userId")], // Replaced by required primary key
    primaryKeys = ["choreId","userId"]
)
data class ChoreToUser(
    var userId: Long,
    var choreId: Long,
    @ColumnInfo(index = true)
    var canFulfill: Boolean,
)

• Note that the index has been replaced with @Primary key (Room requires a Primary Key). They are effectively the same. Additionally @ColumnInfo has been used to also create an index on the userId column (more efficient).

Example 1 - Cartesian Product from you Query

So using the sort of equivalent of your Chore class there is Chore1 :-

data class Chore1(
    var name: String,
    var userName: String, // ADDED for demo
    //This is the line that doesn't work
    //@Relation(parentColumn = "id", entityColumn = "userId", entity = UserEntity::class)
    //var contributingUsers: List<User>,
    @DrawableRes var drawableRes: Int,
    var done: Boolean
)

• @Relation cannot be used without an @Embedded so commented out.

The dao function used with the above was:-

@Query("SELECT Chores.name, drawableRes," +
        "Users.name as userName, Users.imageRes as userPhoto, " +
        " 10 /*(COUNT(ChoreLogs.id) = 1)*/ as done " + // not using Chorelogs table so fake output
        "FROM Chores " +
        "LEFT JOIN ChoreToUsers ON ChoreToUsers.choreId = Chores.id " +
        "LEFT JOIN Users ON ChoreToUsers.userId = Users.id " +
        "/* LEFT JOIN ChoreLogs ON ChoreLogs.choreToUserId = ChoreToUsers.id */") // SQL commented out for Brevity
fun getChoreTiles(): List<Chore1> /* Cartesian Product */

• Note for convenience/brevity the ChoreLogs JOIN has been excluded

• See results for EX01

Example 2 - Using the option a)

Here Chore2 has been used as the resultant class, it being:-

data class Chore2(
    var name: String,
    //This is the line that doesn't work
    //@Relation(parentColumn = "id", entityColumn = "userId", entity = UserEntity::class)
    var contributingUsers: List<User>,
    @DrawableRes var drawableRes: Int,
    var done: Boolean
)

• As can be seen the contributingUsers is a List

This used in conjunction with 2 queries and a function that uses the queries, these being:-

@Query("SELECT * FROM Chores")
fun getAllChores(): List<ChoreEntity>
@Query("SELECT * FROM  ChoreToUsers JOIN Users ON ChoreToUsers.userId = Users.id WHERE ChoreToUsers.choreId=:choreId")
fun getRelatedUsersForAChore(choreId: Long): List<UserEntity>
@Transaction
@Query("")
fun getRelatedUsersPerChoreAsList(): List<Chore2> {
    var rv = arrayListOf<Chore2>()
    for (ct in getAllChores()) {
        var ul = arrayListOf<User>()
        for (ue in getRelatedUsersForAChore(ct.id!!)) {
            ul.add(User(ue.name,ue.imageRes))
        }
        rv.add(Chore2(ct.name,ul.toList(),ct.drawableRes,false))
    }
    return rv
}

• again no need for an @Relation as the queries do all that is required.

• See results for EX02

Example 3 - using option b) BUT via Room

i.e. using @Embedded, with @Relation AND as there is the intermediate associative table associateBy and the Junction.

• i.e. letting Room build the sub query(ies)

In this case the equivalent class is Chore3 :-

data class Chore3(
    @Embedded
    val chore: ChoreEntity,
    @Relation(
        entity = UserEntity::class,
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = ChoreToUser::class, parentColumn = "choreId", entityColumn = "userId"
        )
    )
    val contributingUsers: List<UserEntity>
)

The Dao function being:-

@Transaction
@Query("SELECT * FROM Chores")
fun getAllChore3s(): List<Chore3>

• See results for EX03

Testing/Demonstrating the 3 Examples

The following code was included in an activity (run on the main thread for convenience/brevity):-

const val TAG = "DBINFO"
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 u1Id = dao.insert(UserEntity(name = "User1", imageRes = 1000))
        val u2Id = dao.insert(UserEntity(name = "User2", imageRes = 2000))
        val u3Id = dao.insert(UserEntity(name = "user3", imageRes = 3000))
        val u4Id = dao.insert(UserEntity(name = "user4", imageRes = 4000))

        val c1Id = dao.insert(ChoreEntity(name = "Chore1", drawableRes = 10000))
        val c2Id = dao.insert(ChoreEntity(name = "Chore2",drawableRes = 20000))
        val c3Id = dao.insert(ChoreEntity(name = "Chore3",drawableRes = 30000))
        val c4Id = dao.insert(ChoreEntity(name = "Chore4",drawableRes = 40000))
        val c5Id = dao.insert(ChoreEntity(name = "Chore5",drawableRes = 50000))
        val c6Id = dao.insert(ChoreEntity(name = "Chore6",drawableRes = 60000))

        /* Mapping */

        dao.insert(ChoreToUser(u1Id,c1Id,false))
        dao.insert(ChoreToUser(u1Id,c2Id,true))
        dao.insert(ChoreToUser(u1Id,c3Id,false))
        dao.insert(ChoreToUser(u1Id,c4Id,false))

        dao.insert(ChoreToUser(u2Id,c5Id,true))
        dao.insert(ChoreToUser(u2Id,c6Id,true))

        dao.insert(ChoreToUser(u3Id,c1Id,false))
        dao.insert(ChoreToUser(u3Id,c2Id,false))
        dao.insert(ChoreToUser(u3Id,c3Id,false))
        dao.insert(ChoreToUser(u3Id,c4Id,false))
        dao.insert(ChoreToUser(u3Id,c5Id,false))
        dao.insert(ChoreToUser(u3Id,c6Id,false))

        /* EX01 - Cartesain result */
        for (ct in dao.getChoreTiles()) {
            Log.d(TAG+"_EX01","Chore is ${ct.name} + User is ${ct.userName}")
        }

        /* EX02 - using SQl with JOINS */
        for (ct in dao.getRelatedUsersPerChoreAsList()) {
            Log.d(TAG+"EX02","Chore is ${ct.name}, image is ${ct.drawableRes}, there are ${ct.contributingUsers.size}  contributing Users:-" )
            for (u in ct.contributingUsers) {
                Log.d(TAG+"EX02","\tUser is ${u.userName}, photo is ${u.userPhoto}")
            }
        }

        /* EX03 = using @Embedded/@Relation and associateBy/Junction */
        for (c3 in dao.getAllChore3s()) {
            Log.d(TAG+"EX03","Chore is ${c3.chore.name}, image is ${c3.chore.drawableRes}, there are ${c3.contributingUsers.size} contributing users:-")
            for (u in c3.contributingUsers) {
                Log.d(TAG+"EX03","\tUser is ${u.name}, photo is ${u.imageRes}")
            }
        }
    }
}

• The majority of the code is just loading the data which ends up being:-

and

• Note that above data takes advantage of the many-many allowable by an associative table.

Results (aka output included in the log, split per example)

DBINFO_EX01: Chore is Chore1 + User is User1
DBINFO_EX01: Chore is Chore1 + User is user3
DBINFO_EX01: Chore is Chore2 + User is User1
DBINFO_EX01: Chore is Chore2 + User is user3
DBINFO_EX01: Chore is Chore3 + User is User1
DBINFO_EX01: Chore is Chore3 + User is user3
DBINFO_EX01: Chore is Chore4 + User is User1
DBINFO_EX01: Chore is Chore4 + User is user3
DBINFO_EX01: Chore is Chore5 + User is User2
DBINFO_EX01: Chore is Chore5 + User is user3
DBINFO_EX01: Chore is Chore6 + User is User2
DBINFO_EX01: Chore is Chore6 + User is user3


DBINFOEX02: Chore is Chore1, image is 10000, there are 2  contributing Users:-
DBINFOEX02:     User is User1, photo is 1000
DBINFOEX02:     User is user3, photo is 3000
DBINFOEX02: Chore is Chore2, image is 20000, there are 2  contributing Users:-
DBINFOEX02:     User is User1, photo is 1000
DBINFOEX02:     User is user3, photo is 3000
DBINFOEX02: Chore is Chore3, image is 30000, there are 2  contributing Users:-
DBINFOEX02:     User is User1, photo is 1000
DBINFOEX02:     User is user3, photo is 3000
DBINFOEX02: Chore is Chore4, image is 40000, there are 2  contributing Users:-
DBINFOEX02:     User is User1, photo is 1000
DBINFOEX02:     User is user3, photo is 3000
DBINFOEX02: Chore is Chore5, image is 50000, there are 2  contributing Users:-
DBINFOEX02:     User is User2, photo is 2000
DBINFOEX02:     User is user3, photo is 3000
DBINFOEX02: Chore is Chore6, image is 60000, there are 2  contributing Users:-
DBINFOEX02:     User is User2, photo is 2000
DBINFOEX02:     User is user3, photo is 3000


DBINFOEX03: Chore is Chore1, image is 10000, there are 2 contributing users:-
DBINFOEX03:     User is User1, photo is 1000
DBINFOEX03:     User is user3, photo is 3000
DBINFOEX03: Chore is Chore2, image is 20000, there are 2 contributing users:-
DBINFOEX03:     User is User1, photo is 1000
DBINFOEX03:     User is user3, photo is 3000
DBINFOEX03: Chore is Chore3, image is 30000, there are 2 contributing users:-
DBINFOEX03:     User is User1, photo is 1000
DBINFOEX03:     User is user3, photo is 3000
DBINFOEX03: Chore is Chore4, image is 40000, there are 2 contributing users:-
DBINFOEX03:     User is User1, photo is 1000
DBINFOEX03:     User is user3, photo is 3000
DBINFOEX03: Chore is Chore5, image is 50000, there are 2 contributing users:-
DBINFOEX03:     User is User2, photo is 2000
DBINFOEX03:     User is user3, photo is 3000
DBINFOEX03: Chore is Chore6, image is 60000, there are 2 contributing users:-
DBINFOEX03:     User is User2, photo is 2000
DBINFOEX03:     User is user3, photo is 3000

• as can be seen EX02 and EX03 produce the same output.

Answered By - MikeT