android-roomandroid-room-relation

Android Room `@Relation` to a class with `@Embedded` entities

I have 3 tables A, X, Y. Records of A are defined by pairs of X and Y - in another table AWithXY. I can model A the way I receive list of X or Y in a list like below:

data class AWithXY(
    @Embedded val a: A,
    @Relation(
        parentColumn = BaseColumns.PK,
        entityColumn = BaseColumns.PK,
        associateBy = Junction(
            value = AWithXY::class,
            parentColumn = AWithXY.FK_A,
            entityColumn = AWithXY.FK_X,
        ),
        entity = X::class,
    )
    val xList: List<X>,
    // Similar relation for Y
)

Another option would be to define an extra table XWithY and use its primary key instead to associate it with A, but is there a way to do it without it? Technically I could have an embedded class:

data class XWithY(
    @Embedded val x: X,
    @Embedded val y: Y,
)

And write a custom query and join, so there are some alternatives, but I was wondering if there's something that Room provides to achieve it without extra tables or custom queries.

Ideally it would be having a @Relation to a class with 2 or more @Embedded entities, but I suppose it's impossible, because it's technically a relation to 2 different tables. The issues is similar to this or this (@Relation limitation).

Solution

  • Currently I believe the best that can be done is to have 4 tables A, X and Y and a mapping/associative/reference .... table.

    However, trying to use the convenience @Relation has issues in that it will always try to get ALL the relations and if not List(Y) within a List(X) then the first Y per X will be used (see demo output)

    To demonstrate consider the following which includes variations (V1 and V2).

    First the 4 tables (@Entity annotated classes) A, X Y and AXYMAP:-

    @Entity
data class A(
    @PrimaryKey
    val aId: Long?=null,
    val aName: String
)
@Entity
data class X(
    @PrimaryKey
    val xId: Long?=null,
    val xName: String
)
@Entity
data class Y(
    @PrimaryKey
    val yId: Long?=null,
    val yName: String
)
@Entity(
    foreignKeys = [
        ForeignKey(
            entity = A::class,
            parentColumns = ["aId"],
            childColumns = ["FK_A"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = X::class,
            parentColumns = ["xId"],
            childColumns = ["FK_X"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = Y::class,
            parentColumns = ["yId"],
            childColumns = ["FK_Y"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ],
    primaryKeys = ["FK_A","FK_X","FK_Y"]
)
data class AXYMap(
    val FK_A: Long,
    val FK_X: Long,
    val FK_Y: Long
)

    Now the supportive POJO's:-

    /* See V2 below */
data class XWithY(
    @Embedded
    val x: X,
    @Relation(
        entity = Y::class,
        parentColumn = "xId",
        entityColumn = "yId"
    )
    val y: Y
)
data class AWithXWithY(
    @Embedded
    val a: A,
    @Relation(
        entity = X::class,
        parentColumn = "aId",
        entityColumn = "xId",
        associateBy = Junction(
            AXYMap::class,
            parentColumn = "FK_A",
            entityColumn = "FK_X"
        )
    )
    val xWithYList: List<XWithY>
)
/* Note this being the V2 Version WITHOUT the @Relation (an issue as val y should really be a list) */
data class XWithYV2(
    @Embedded
    val x: X,
    @Embedded
    val y: Y
)

    an @Dao annotated interface, again noting that V2 is the one that does not return incorrect values.

    @Dao
interface AllDAOs {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(a: A): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(x: X): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(y: Y): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(axyMap: AXYMap): Long

    /* OOOPS (see result) */
    @Transaction
    @Query("SELECT * FROM a")
    fun getAWithXAndYPairs(): List<AWithXWithY>

    @Query("SELECT * FROM axymap JOIN x ON xId=FK_X JOIN y ON yId=FK_Y WHERE FK_A=:aId")
    fun getXWithYList(aId: Long): List<XWithY>

    @Query("SELECT * FROM axymap JOIN x ON xid=FK_X JOIN y ON yid=FK_Y WHERE FK_A=:aId")
    fun getXWithYListV2(aId: Long): List<XWithYV2>

    @Query("SELECT * FROM a")
    fun getAList(): List<A>

    /* OOOPS */
    @Transaction
    @Query("")
    fun getAWithXAndYPairsV1(): List<AWithXWithY> {
        val rv = mutableListOf<AWithXWithY>()
        for (alist in getAList()) {
            val xybaselist = mutableListOf<XWithY>()
            for (xy in getXWithYList(alist.aId!!)) {
                xybaselist.add(XWithY(X(xy.x.xId,xy.x.xName),Y(xy.y.yId,xy.y.yName)))
            }
            rv.add(AWithXWithY(A(alist.aId,alist.aName),xybaselist))
        }
        return rv
    }

    /* THE GOOD version */
    @Transaction
    @Query("")
    fun getAWithXAndYPairsV2(): List<AWithXWithY> {
        val rv = mutableListOf<AWithXWithY>()
        for (alist in getAList()) {
            val xybaselist = mutableListOf<XWithY>()
            for (xy in getXWithYListV2(alist.aId!!)) {
                xybaselist.add(XWithY(X(xy.x.xId, xy.x.xName),Y(xy.y.yId,xy.y.yName)))
            }
            rv.add(AWithXWithY(A(alist.aId,alist.aName),xybaselist))
        }
        return rv
    }
}

    To demo an @Database annotated abstract class:-

    @Database(entities = [A::class,X::class,Y::class, AXYMap::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDAOs(): AllDAOs
    companion object {
        private var instance: TheDatabase?=null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance=Room.databaseBuilder(
                    context,TheDatabase::class.java,"the_database.db"
                )
                    .allowMainThreadQueries() /* For brevity of the demo */
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

    Finally some Activity code to demo by

    :-

    class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDAOs
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)
        dao = db.getAllDAOs()

        val a1id = dao.insert(A(aName = "A1"))
        val a2id = dao.insert(A(aName = "A2"))
        val a3id = dao.insert(A(aName = "A3"))
        val x1id = dao.insert(X(xName = "X1"))
        val x2id = dao.insert(X(xName = "X2"))
        val x3id = dao.insert(X(xName = "X3"))
        val x4id = dao.insert(X(xName = "X4"))
        val x5id = dao.insert(X(xName = "X5"))
        val y1id = dao.insert(Y(yName = "Y1"))
        val y2id = dao.insert(Y(yName = "Y2"))
        val y3id = dao.insert(Y(yName = "Y3"))
        val y4id = dao.insert(Y(yName = "Y4"))
        val y5id = dao.insert(Y(yName = "Y5"))

        dao.insert(AXYMap(a1id,x2id,y2id))
        dao.insert(AXYMap(a1id,x4id,y4id))
        dao.insert(AXYMap(a2id,x1id,y1id))
        dao.insert(AXYMap(a2id,x1id,y3id))
        dao.insert(AXYMap(a2id,x1id,y5id))
        dao.insert(AXYMap(a2id,x3id,y1id))
        dao.insert(AXYMap(a2id,x3id,y3id))
        dao.insert(AXYMap(a2id,x3id,y5id))
        dao.insert(AXYMap(a2id,x5id,y1id))
        dao.insert(AXYMap(a2id,x5id,y3id))
        dao.insert(AXYMap(a2id,x5id,y5id))

        for (axy in dao.getAWithXAndYPairs()) {
            val sb=StringBuilder()
            for (xwy in axy.xWithYList) {
                sb.append("\n\tX is ${xwy.x.xName} X ID is ${xwy.x.xId}. Y is ${xwy.y.yName} ID is ${xwy.y.yId}")
            }
            Log.d("DBINFOR1","A is ${axy.a.aName} ID is ${axy.a.aId} it has ${axy.xWithYList.size} X-Y pairs; they are:- ${sb}")
        }
        for(axy in dao.getAWithXAndYPairsV1()) {
            val sb = StringBuilder()
            for (xwy in axy.xWithYList) {
                sb.append("\n\tX is ${xwy.x.xName} X ID is ${xwy.x.xId}. Y is ${xwy.y.yName} ID is ${xwy.y.yId}")
            }
            Log.d("DBINFOR2","A is ${axy.a.aName} ID is ${axy.a.aId} it has ${axy.xWithYList.size} X-Y pairs; they are:- ${sb}")
        }
        for (axy in dao.getAWithXAndYPairsV2()) {
            val sb = StringBuilder()
            for (xwy in axy.xWithYList) {
                sb.append("\n\tX is ${xwy.x.xName} X ID is ${xwy.x.xId}. Y is ${xwy.y.yName} ID is ${xwy.y.yId}")
            }
            Log.d("DBINFOR3","A is ${axy.a.aName} ID is ${axy.a.aId} it has ${axy.xWithYList.size} X-Y pairs; they are:- ${sb}")
        }
    }
}

    Demo Result (as per the log):-

    2023-03-20 12:47:47.246 D/DBINFOR1: A is A1 ID is 1 it has 2 X-Y pairs; they are:- 
        X is X2 X ID is 2. Y is Y2 ID is 2
        X is X4 X ID is 4. Y is Y4 ID is 4
2023-03-20 12:47:47.247 D/DBINFOR1: A is A2 ID is 2 it has 9 X-Y pairs; they are:- 
        X is X1 X ID is 1. Y is Y1 ID is 1
        X is X1 X ID is 1. Y is Y1 ID is 1
        X is X1 X ID is 1. Y is Y1 ID is 1
        X is X3 X ID is 3. Y is Y3 ID is 3
        X is X3 X ID is 3. Y is Y3 ID is 3
        X is X3 X ID is 3. Y is Y3 ID is 3
        X is X5 X ID is 5. Y is Y5 ID is 5
        X is X5 X ID is 5. Y is Y5 ID is 5
        X is X5 X ID is 5. Y is Y5 ID is 5
2023-03-20 12:47:47.247 D/DBINFOR1: A is A3 ID is 3 it has 0 X-Y pairs; they are:- 


2023-03-20 12:47:47.263 D/DBINFOR2: A is A1 ID is 1 it has 2 X-Y pairs; they are:- 
        X is X2 X ID is 2. Y is Y2 ID is 2
        X is X4 X ID is 4. Y is Y4 ID is 4
2023-03-20 12:47:47.263 D/DBINFOR2: A is A2 ID is 2 it has 9 X-Y pairs; they are:- 
        X is X1 X ID is 1. Y is Y1 ID is 1
        X is X1 X ID is 1. Y is Y1 ID is 1
        X is X1 X ID is 1. Y is Y1 ID is 1
        X is X3 X ID is 3. Y is Y3 ID is 3
        X is X3 X ID is 3. Y is Y3 ID is 3
        X is X3 X ID is 3. Y is Y3 ID is 3
        X is X5 X ID is 5. Y is Y5 ID is 5
        X is X5 X ID is 5. Y is Y5 ID is 5
        X is X5 X ID is 5. Y is Y5 ID is 5
2023-03-20 12:47:47.263 D/DBINFOR2: A is A3 ID is 3 it has 0 X-Y pairs; they are:- 


2023-03-20 12:47:47.268 D/DBINFOR3: A is A1 ID is 1 it has 2 X-Y pairs; they are:- 
        X is X2 X ID is 2. Y is Y2 ID is 2
        X is X4 X ID is 4. Y is Y4 ID is 4
2023-03-20 12:47:47.268 D/DBINFOR3: A is A2 ID is 2 it has 9 X-Y pairs; they are:- 
        X is X1 X ID is 1. Y is Y1 ID is 1
        X is X1 X ID is 1. Y is Y3 ID is 3
        X is X1 X ID is 1. Y is Y5 ID is 5
        X is X3 X ID is 3. Y is Y1 ID is 1
        X is X3 X ID is 3. Y is Y3 ID is 3
        X is X3 X ID is 3. Y is Y5 ID is 5
        X is X5 X ID is 5. Y is Y1 ID is 1
        X is X5 X ID is 5. Y is Y3 ID is 3
        X is X5 X ID is 5. Y is Y5 ID is 5
2023-03-20 12:47:47.268 D/DBINFOR3: A is A3 ID is 3 it has 0 X-Y pairs; they are:-

    As can be seen All three work fine for A1 and A3, the simpler data but only the 3rd (V2) returns the expected values for A2 (1, 3 and 5 Y's as opposed to the xId rather than the yId value).

    Even though the underlying query returns the expected data e.g.

    enter image description here

    Of course you could use List<Y> in the XWithY class with an @Relation but that would then incur a subquery of the subquery which is in efficient as the subquery retrieves all the necessary data.

    Another option would be to try using Maps as is suggested by the link to the Issue.

    So you could just have the following POJOs (with the same 4 tables):-

    data class AWithXWithY(
    @Embedded
    val a: A,
    val xWithYList: List<XWithYV2>
)

data class XWithYV2(
    @Embedded
    val x: X,
    @Embedded
    val y: Y
)

    Along with:-

    @Query("SELECT * FROM axymap JOIN x ON xid=FK_X JOIN y ON yid=FK_Y WHERE FK_A=:aId")
fun getXWithYListV2(aId: Long): List<XWithYV2>

@Query("SELECT * FROM a")
fun getAList(): List<A>

@Transaction
@Query("")
fun getAWithXAndYPairsV2(): List<AWithXWithY> {
    val rv = mutableListOf<AWithXWithY>()
    for (alist in getAList()) {
        val xybaselist = mutableListOf<XWithYV2>()
        for (xy in getXWithYListV2(alist.aId!!)) {
            xybaselist.add(XWithYV2(X(xy.x.xId, xy.x.xName),Y(xy.y.yId,xy.y.yName)))
        }
        rv.add(AWithXWithY(A(alist.aId,alist.aName),xybaselist))
    }
    return rv
}

    Additional (re comment)

    As I understand it this relation will join Y for each record that's already returned by the join @Query.

    If you use @Relation then Room based upon the parameters supplied via the annotation builds it's own subquery to return ALL children(x) of the parent(y). If you are not receiving into a list of children but just a single child then the first child only will be assigned.

    That is why the output, when using the XWithY (original) retrieves the same values for Y (no issue if there is only the 1 Y per X hence why A1 in the first two examples appears fine). It is always taking the same single Y from the list of Y's (hence why A2 gets the same values for the first two examples).

    The 3rd example doesn't use @Relation (XWithYV2) but instead uses it's own subquery to then build the List of XWithY's. Hence why the third example works (it doesn't rely upon Room convenience handling).

    If you join A in getXWithYListV2 and remove WHERE condition - wouldn't it be enough to return List

    No due to error: Cannot figure out how to read this field from a cursor. private final java.util.List<a.a.so75784594relationships.XWithYV2> xWithYList = null;

    However, you could use:-

    data class XWithListOfY(
    @Embedded
    val x: X,
    @Relation(
        entity = Y::class,
        parentColumn = "xId",
        entityColumn = "yId",
        associateBy = Junction(
            AXYMap::class,
            parentColumn = "FK_X",
            entityColumn = "FK_Y"
        )
    )
    val yList: List<Y>
)

data class AWithXListWithYList(
    @Embedded
    val a: A,
    @Relation(
        entity = X::class,
        parentColumn = "aId",
        entityColumn = "xId",
        associateBy = Junction(
            AXYMap::class,
            parentColumn = "FK_A",
            entityColumn = "FK_X"
        )
    )
    val xListWithYList: List<XWithListOfY>
)

    with :-

    @Transaction
@Query("SELECT * FROM a")
fun getAWithXListWithYList(): List<AWithXListWithYList>

    in which case using :-

        for(awxlwyl in dao.getAWithXListWithYList()) {
        val sbx = StringBuilder()
        for (xwyl in awxlwyl.xListWithYList) {
            val sby = StringBuilder()
            for(y in xwyl.yList) {
                sby.append("\n\t\tY is ${y.yName} ID is ${y.yId}")
            }
            sbx.append("\n\tX is ${xwyl.x.xName} ID is ${xwyl.x.xId}${sby}")
        }
        Log.d("DBINFORA","A is ${awxlwyl.a.aName} ID is ${awxlwyl.a.aId}${sbx}")
    }

    Results in:-

    2023-03-21 08:07:12.238 D/DBINFORA: A is A1 ID is 1
        X is X2 ID is 2
            Y is Y2 ID is 2
        X is X4 ID is 4
            Y is Y4 ID is 4
2023-03-21 08:07:12.239 D/DBINFORA: A is A2 ID is 2
        X is X1 ID is 1
            Y is Y1 ID is 1
            Y is Y3 ID is 3
            Y is Y5 ID is 5
        X is X1 ID is 1
            Y is Y1 ID is 1
            Y is Y3 ID is 3
            Y is Y5 ID is 5
        X is X1 ID is 1
            Y is Y1 ID is 1
            Y is Y3 ID is 3
            Y is Y5 ID is 5
        X is X3 ID is 3
            Y is Y1 ID is 1
            Y is Y3 ID is 3
            Y is Y5 ID is 5
        X is X3 ID is 3
            Y is Y1 ID is 1
            Y is Y3 ID is 3
            Y is Y5 ID is 5
        X is X3 ID is 3
            Y is Y1 ID is 1
            Y is Y3 ID is 3
            Y is Y5 ID is 5
        X is X5 ID is 5
            Y is Y1 ID is 1
            Y is Y3 ID is 3
            Y is Y5 ID is 5
        X is X5 ID is 5
            Y is Y1 ID is 1
            Y is Y3 ID is 3
            Y is Y5 ID is 5
        X is X5 ID is 5
            Y is Y1 ID is 1
            Y is Y3 ID is 3
            Y is Y5 ID is 5
2023-03-21 08:07:12.239 D/DBINFORA: A is A3 ID is 3

    Now if you wanted to use the A X and Y all joined then the result would have to be returned using something like:-

    data class AXY(
    @Embedded
    val a: A,
    @Embedded
    val x: X,
    @Embedded
    val y: Y
)

    And you could then use:-

    @Query("SELECT a.*,x.*,y.* FROM axymap JOIN a ON aId=FK_A JOIN x ON xId=FK_X JOIN y ON yId=FK_Y ORDER BY aId ASC, xId ASC, yId ASC")
fun getAXY(): List<AXY>

    The issue is then handling the cartesian product which using:-

        for (axy in dao.getAXY()) {
        Log.d("DBINFOR9","A is ${axy.a.aName} AID is ${axy.a.aId} X is ${axy.x.xName} XID is ${axy.x.xId} Y is ${axy.y.yName} YID is ${axy.y.yId}")
    }

    would result in:-

    2023-03-21 08:59:45.055 D/DBINFOR9: A is A1 AID is 1 X is X2 XID is 2 Y is Y2 YID is 2
2023-03-21 08:59:45.055 D/DBINFOR9: A is A1 AID is 1 X is X4 XID is 4 Y is Y4 YID is 4
2023-03-21 08:59:45.055 D/DBINFOR9: A is A2 AID is 2 X is X1 XID is 1 Y is Y1 YID is 1
2023-03-21 08:59:45.055 D/DBINFOR9: A is A2 AID is 2 X is X1 XID is 1 Y is Y3 YID is 3
2023-03-21 08:59:45.055 D/DBINFOR9: A is A2 AID is 2 X is X1 XID is 1 Y is Y5 YID is 5
2023-03-21 08:59:45.055 D/DBINFOR9: A is A2 AID is 2 X is X3 XID is 3 Y is Y1 YID is 1
2023-03-21 08:59:45.055 D/DBINFOR9: A is A2 AID is 2 X is X3 XID is 3 Y is Y3 YID is 3
2023-03-21 08:59:45.055 D/DBINFOR9: A is A2 AID is 2 X is X3 XID is 3 Y is Y5 YID is 5
2023-03-21 08:59:45.055 D/DBINFOR9: A is A2 AID is 2 X is X5 XID is 5 Y is Y1 YID is 1
2023-03-21 08:59:45.055 D/DBINFOR9: A is A2 AID is 2 X is X5 XID is 5 Y is Y3 YID is 3
2023-03-21 08:59:45.055 D/DBINFOR9: A is A2 AID is 2 X is X5 XID is 5 Y is Y5 YID is 5