Room @Relation annotation with a One To Many relationship-CodePudding

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?

CodePudding user response：

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

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.