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
)
- Note V2 the usable if used in conjunction with
getXWithYListV2
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
-
- inserting some data (3 As, 5 Xs and 5 Ys). With A1 having 2 pairs with different Xs. A2 having 9 pairs with similar Xs
- the core issue being that using the convenience @Relation without a List of Ys within an XWithY results in only the first Y for the X
-
- Extracting the data using the various versions (no version and V1 not returning accurate values and V2 the 3rd extract returning the expected values)
:-
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.
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
}
- of course the above could be extracted from a single query that includes joining A but the code to build the extracted As with the X and Y pairs would be more in the way of coding, it would also require that the result is ORDERED or that checking is done to see if the A has already been processed.
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;
- i.e. there is no @Relation so Room doesn't expect to build a list.
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
- probably not the desired result, it being more complex to handle the more complex and less efficient sub-subquery due to the hierarchy of the @Relation (due to the issue/restrictions of the @Relation handling by room).
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
- Obviously the most efficient with regard to retrieving the data from the database (no subqueries) BUT the issue is then the complexity of building the A with XY pairs list.
