I have a many to many relationship defined in android room. The junction table contains additional attributes, but i think that doesn't matter here.
When I execute my db query, i get to many result. With to many i mean results i do not expect due to wrong relations. Obviously I have a bunch of Event and TrackedArtist rows in my database. I addition i have some EventArtistCrossRef rows. Lets say
Now when I call getAllEventsWithEventsAndArtists(), I get two results. One according to event with id 1 and one for event with id 2. event and trackedArtists is correct. But artistsAtEvent contains "wrong" entries. In example for event with id 2 I want to get row 3, 4 and 5 but I get row 2 in addition.
Is there something I need to add to the @Relation or to the @Query? Or do I have to write a custom @Query beacause room is not capable for this use case?
Here is my code:
@Entity(tableName = "event_table")
data class Event(
@PrimaryKey(autoGenerate = true)
@ColumnInfo("event_id")
var eventId: Long = 0L,
@ColumnInfo("name")
var name: String = "",
// Other attributes ...
)
@Entity(tableName = "tracked_artist_table")
data class TrackedArtist(
@PrimaryKey(autoGenerate = true)
@ColumnInfo("artist_id")
var artistId: Long = 0L,
@ColumnInfo(name = "name")
var name: String = "",
// other attributes ...
)
@Entity(
primaryKeys = ["event_id", "artist_id"],
foreignKeys = [
ForeignKey(
entity = Event::class,
parentColumns = ["event_id"],
childColumns = ["event_id"],
onDelete = ForeignKey.CASCADE
),
ForeignKey(
entity = TrackedArtist::class,
parentColumns = ["artist_id"],
childColumns = ["artist_id"],
onDelete = ForeignKey.CASCADE
)
]
)
data class EventArtistCrossRef(
@ColumnInfo("event_id", index = true)
var eventId: Long,
@ColumnInfo("artist_id", index = true)
var artistId: Long,
// the mentioned before other attributes on junction table
)
data class EventWithEventsAndArtists(
@Embedded
var event: Event,
@Relation(
parentColumn = "event_id",
entityColumn = "artist_id",
associateBy = Junction(EventArtistCrossRef::class)
)
val artists: List<TrackedArtist>,
@Relation(
parentColumn = "event_id",
entityColumn = "artist_id",
associateBy = Junction(
value = EventArtistCrossRef::class,
parentColumn = "event_id",
entityColumn = "artist_id"
)
)
var artistsAtEvent: List<EventArtistCrossRef>
)
@Dao
interface EventDao {
@Transaction
@Query("SELECT * from event_table ORDER BY lower(name) ASC")
fun getAllEventsWithEventsAndArtists(): LiveData<List<EventWithEventsAndArtists>>
// other queries ...
}
I believe that your issue is that you are telling Room to SELECT events from the event_table and to JOIN the eventartistcrossref table with the event_table, as the junction and to again JOIN the eventartistcrossref table and thus the permutations are greater than expected.
Using your code and the following activity code (note .allowMainThreadQueries has been used for brevity and convenience) to mimic what your are doing BUT using unique id's throughout (artist id's 10,20,30 and 40 instead of 1-4):-
lateinit var db: TheDatabase
lateinit var dao: EventDao
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
db = TheDatabase.getInstance(this)
dao = db.getEventDao()
dao.insert(Event(1,"Event1"))
dao.insert(Event(2,"Event2"))
dao.insert(TrackedArtist(10,"TA10"))
dao.insert(TrackedArtist(20,"TA20"))
dao.insert(TrackedArtist(30,"TA30"))
dao.insert(TrackedArtist(40,"TA40"))
dao.insert(EventArtistCrossRef(1,10))
dao.insert(EventArtistCrossRef(1,20))
dao.insert(EventArtistCrossRef(2,10))
dao.insert(EventArtistCrossRef(2,30))
dao.insert(EventArtistCrossRef(2,40))
for(eweaa in dao.getAllEventsWithEventsAndArtists()) {
val taoutput = StringBuilder()
for (ta in eweaa.artists) {
taoutput.append("\n\tNames is ${ta.name} artistId is ${ta.artistId}")
}
val aaeoutput = StringBuilder()
for (aae in eweaa.artistsAtEvent) {
aaeoutput.append("\n\tEventID=${aae.eventId} ArtistId=${aae.artistId}")
}
Log.d("DBINFO","Event Name is ${eweaa.event.name} Event ID is ${eweaa.event.eventId}" +
".\nThere are ${eweaa.artists.size} tacked artists; they are ${taoutput}" +
".\nThere are ${eweaa.artistsAtEvent.size} artistsAtEvents; they are ${aaeoutput}")
}
}
The result is:-
D/DBINFO: Event Name is Event1 Event ID is 1.
There are 2 tacked artists; they are
Names is TA10 artistId is 10
Names is TA20 artistId is 20.
There are 3 artistsAtEvents; they are
EventID=1 ArtistId=10
EventID=2 ArtistId=10
EventID=1 ArtistId=20
D/DBINFO: Event Name is Event2 Event ID is 2.
There are 3 tacked artists; they are
Names is TA10 artistId is 10
Names is TA30 artistId is 30
Names is TA40 artistId is 40.
There are 4 artistsAtEvents; they are
EventID=1 ArtistId=10
EventID=2 ArtistId=10
EventID=2 ArtistId=30
EventID=2 ArtistId=40
i.e. the tracked artists are as expected but due to the unconventional (if that is the correct word) joining of the crossref using the crossref as a junction has produced the unexpected result (due, I believe, to the artist that is cross referenced with both events).
Is there something I need to add to the @Relation or to the @Query? Or do I have to write a custom @Query beacause room is not capable for this use case?
It is hard to fathom exactly what your are trying to achieve as you already have the artists for each event (i.e. the trackedArtists).
Response to comment:-
Okay, here is where the additional attributes of the crossref table come into play. I had trouble to retrieve them in form of another Pojo which contains the trackedArtists and the addition attributes from crossref table. So I added the trackedArtists and the artistsAtEvent in form of the EventArtistCrossRef in two separate lists so that I can map them together later by hand.
Adding an additional column for the "other attributes" as per:-
For the Event class:-
....
@ColumnInfo("name")
var name: String = "",
var eother: String = "" /* Added for 2nd demo */
For the TrackedArtist class:-
....
@ColumnInfo(name = "name")
var name: String = "",
var taother: String = "" /* Added for 2nd demo */
And lastly for the *EventArtistCrossRef class:-
....
@ColumnInfo("artist_id", index = true)
var artistId: Long,
var eacother: String /* Added for 2nd Demo */
After adding the other attributes the EventWithEventsAndArtists class is modified by adding a third @Relation BUT not via a junction but via a direct relationship, on the event_id column, between the Event table and the EventTrackingCrossRef table (see var other: ....)
data class EventWithEventsAndArtists(
@Embedded
var event: Event,
@Relation(
parentColumn = "event_id",
entityColumn = "artist_id",
associateBy = Junction(EventArtistCrossRef::class, parentColumn = "event_id", entityColumn = "artist_id")
)
val artists: List<TrackedArtist>,
@Relation(
parentColumn = "event_id",
entityColumn = "artist_id",
associateBy = Junction(
value = EventArtistCrossRef::class,
parentColumn = "event_id",
entityColumn = "artist_id"
)
)
var artistsAtEvent: List<EventArtistCrossRef>,
/* Getting the other information from the EventArtistCrossRef
That is NOT using it as a junction BUT a direct relationship
That is the event_id is used to ascertain/get the rows in the EACRef
where the EAC's event_id
*/
@Relation(
parentColumn = "event_id", entityColumn = "event_id"
)
var other: List<EventArtistCrossRef>
)
Finally altering the Activity code
so the code is now:-
class MainActivity : AppCompatActivity() {
lateinit var db: TheDatabase
lateinit var dao: EventDao
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
db = TheDatabase.getInstance(this)
dao = db.getEventDao()
dao.insert(Event(1,"Event1","E1"))
dao.insert(Event(2,"Event2","E2"))
dao.insert(TrackedArtist(10,"TA10","TA10TA10"))
dao.insert(TrackedArtist(20,"TA20","TA20TA20"))
dao.insert(TrackedArtist(30,"TA30","TA30TA30"))
dao.insert(TrackedArtist(40,"TA40","TA40TA40"))
dao.insert(EventArtistCrossRef(1,10,"EACE1TA10"))
dao.insert(EventArtistCrossRef(1,20,"EACE1TA20"))
dao.insert(EventArtistCrossRef(2,10,"EACE2TA10"))
dao.insert(EventArtistCrossRef(2,30,"EACE2TA30"))
dao.insert(EventArtistCrossRef(2,40,"EACE2TA40"))
for(eweaa in dao.getAllEventsWithEventsAndArtists()) {
val taoutput = StringBuilder()
for (ta in eweaa.artists) {
taoutput.append("\n\tNames is ${ta.name} artistId is ${ta.artistId} taother is ${ta.taother} ") /* Added taother */
}
val aaeoutput = StringBuilder()
for (aae in eweaa.artistsAtEvent) {
aaeoutput.append("\n\tEventID=${aae.eventId} ArtistId=${aae.artistId} aeother is ${aae.eacother}") /* Added eacother */
}
/* ADDED to handle the list of "other"'s (direct relationship between Event and EventArtistCrossRef) */
val otheroutput = StringBuilder()
for (o in eweaa.other) {
otheroutput.append("\n\tEventID =${o.eventId} ArtistID=${o.artistId} OTHER=${o.eacother}")
}
/* Amended to include eother and the additional "other" */
Log.d("DBINFO","Event Name is ${eweaa.event.name} Event ID is ${eweaa.event.eventId} Event eother is ${eweaa.event.eother}" +
".\nThere are ${eweaa.artists.size} tacked artists; they are ${taoutput}" +
".\nThere are ${eweaa.artistsAtEvent.size} artistsAtEvents; they are ${aaeoutput}" +
".\nThere are ${eweaa.other.size} EACs; They are ${otheroutput}")
}
}
}
Log Output NOW:-
D/DBINFO: Event Name is Event1 Event ID is 1 Event eother is E1.
There are 2 tacked artists; they are
Names is TA10 artistId is 10 taother is TA10TA10
Names is TA20 artistId is 20 taother is TA20TA20 .
There are 3 artistsAtEvents; they are
EventID=1 ArtistId=10 aeother is EACE1TA10
EventID=2 ArtistId=10 aeother is EACE2TA10
EventID=1 ArtistId=20 aeother is EACE1TA20.
There are 2 EACs; They are
EventID =1 ArtistID=10 OTHER=EACE1TA10
EventID =1 ArtistID=20 OTHER=EACE1TA20
D/DBINFO: Event Name is Event2 Event ID is 2 Event eother is E2.
There are 3 tacked artists; they are
Names is TA10 artistId is 10 taother is TA10TA10
Names is TA30 artistId is 30 taother is TA30TA30
Names is TA40 artistId is 40 taother is TA40TA40 .
There are 4 artistsAtEvents; they are
EventID=1 ArtistId=10 aeother is EACE1TA10
EventID=2 ArtistId=10 aeother is EACE2TA10
EventID=2 ArtistId=30 aeother is EACE2TA30
EventID=2 ArtistId=40 aeother is EACE2TA40.
There are 3 EACs; They are
EventID =2 ArtistID=10 OTHER=EACE2TA10
EventID =2 ArtistID=30 OTHER=EACE2TA30
EventID =2 ArtistID=40 OTHER=EACE2TA40
As an overview.
When you code @Embedded that is (or makes up part of) the overarching table(s). i.e the parent
When you code @Relation, then the parentColumn refers to the overarching (@Embedded) table(s) and the entityColumn refres to the column as per the entity as per the var/val.
If associateBy is used and a Junction specified then the parentColumn of the Junction defines the column in the junction table (determined according to the class specified by the value) that should match, by value, the parentColumn of the @Relation, the entityColumn similarly defines the column in the junction table that will match, by value, the entityColumn of the @Relation.
However
I believe that you want a single tracked_artist alond with the respective "other" from the crossref.
e.g. (the result prior to the code) :-
2023-08-19 13:27:09.312 12326-12326/a.a.so76926918kotlinroommanytomany D/DBINFO: Event Name is Event1 Event ID is 1 Event eother is E1.
....
There are 2 EXTRAS (EAC with Artist) they are
ArtistId=10 ArtistName is TA10 ArtistOther is TA10TA10 EACOTHER is EACE1TA10 owning eventid=1
ArtistId=20 ArtistName is TA20 ArtistOther is TA20TA20 EACOTHER is EACE1TA20 owning eventid=1
2023-08-19 13:27:09.313 12326-12326/a.a.so76926918kotlinroommanytomany D/DBINFO: Event Name is Event2 Event ID is 2 Event eother is E2.
....
There are 3 EXTRAS (EAC with Artist) they are
ArtistId=10 ArtistName is TA10 ArtistOther is TA10TA10 EACOTHER is EACE2TA10 owning eventid=2
ArtistId=30 ArtistName is TA30 ArtistOther is TA30TA30 EACOTHER is EACE2TA30 owning eventid=2
ArtistId=40 ArtistName is TA40 ArtistOther is TA40TA40 EACOTHER is EACE2TA40 owning eventid=2
.... is the removal of the other outputThis can be achieved but from the perspective of an EventArtistCrossRef with the related TrackedArtist. That is a POJO like:-
data class TrackedArtistWithEACOther(
@Embedded
var eventArtistCrossRef: EventArtistCrossRef,
@Relation(
parentColumn = "artist_id",
entityColumn = "artist_id"
)
var trackedArtist: TrackedArtist /* NO need for a List there will only be 1 related artist */
)
With the above then the following can be used within the EventWithEventsAndArtists like :-
/* Extra perhaps what you really want */
@Relation(
entity = EventArtistCrossRef::class,
parentColumn = "event_id",
entityColumn = "event_id"
)
var extra: List<TrackedArtistWithEACOther>
The following is the portion of the changed Activity code:-
/* Amendment to cater for EXTRA */
val extraoutput = StringBuilder()
for (extra in eweaa.extra) {
extraoutput.append("\n\tArtistId=${extra.trackedArtist.artistId} " +
"ArtistName is ${extra.trackedArtist.name} " +
"ArtistOther is ${extra.trackedArtist.taother} " +
"EACOTHER is ${extra.eventArtistCrossRef.eacother} owning eventid=${extra.eventArtistCrossRef.eventId}")
}
/* Amended to include eother and the additional "other" */
Log.d("DBINFO","Event Name is ${eweaa.event.name} Event ID is ${eweaa.event.eventId} Event eother is ${eweaa.event.eother}" +
".\nThere are ${eweaa.artists.size} tacked artists; they are ${taoutput}" +
".\nThere are ${eweaa.artistsAtEvent.size} artistsAtEvents; they are ${aaeoutput}" +
".\nThere are ${eweaa.other.size} EACs; They are ${otheroutput}" +
".\nThere are ${eweaa.extra.size} EXTRAS (EAC with Artist) they are ${extraoutput}") /* ADDED for EXTRAs */
}