The devil in the detail, as far as Room is concerned, is with the POJOs (for handling the junction(s)).
your POJO's don't appear to consider this as per
data class Details(val description: String,
val stats: Stats,
val venue: Venue,
val performers: List<Performer> /* <<<<<<<<<< */)
and
data class UIEventDetailed(val id:Int,
val title:String,
val image:String,
val performer:List<Performer>, /* <<<<<<<<<< */
val description:String,
val averagePrice: Int,
val highestPrice: Int,
val listingCount: Int,
val lowestPrice: Int,
val medianPrice: Int,
)
To handle the junctions (via Room's convenience methodology) the junction is handled by the POJO using an @Relation with the associateBy parameter defining the Junction (note that the entity parameter of the Junction should specify the Parent class if the column names differ (it is suggested that columns names are always unique as that then rules out potential issues with ambiguities)).
- by saying via Room's convenience methodology it should be noted that Room uses subqueries (hence the @Transaction as used/demonstrated) that
- will retrieve ALL children of a parent, and
- will retrieve them in the order that is determined by the SQlite query optimiser.
- this irrespective of SQL that species JOINs and WHERE clauses that do not affect the parents. That is the Room only considers the parents returned from such a query.
- hence in the query in the Dao all that is required is just
SELECT * FROM event but it still retrieves all the children.
Demo of the devil in the detail
The following is a condensed, working, version somewhere along the lines of you question.
It consists of 3 core tables (Event, Detail and Performer) and 3 Junctions (mapping/reference/associative and other descriptions tables (perhaps over the top but able to cater with many-many(n-n) as well as 1-n and 1-1)).
As per:-
@Entity
data class Event(
@PrimaryKey
val eventid: Long?=null,
val title: String,
/* etc */
)
@Entity
data class Performer(
@PrimaryKey
val performerid: Long?=null,
val performerName: String
/* etc */
)
@Entity
data class Detail(
@PrimaryKey
val detailid: Long?=null,
val venueName: String
/* etc */
)
/* Long the lines of the comment:-
Events and Performers have many to many relationship.
Hence I created the junction.
The eventid is the parent column whereas the performerId is the entity column.
*/
@Entity(
primaryKeys = ["eventid_map","performerid_map"]
)
data class EventPerformerJunction(
val eventid_map: Long,
@ColumnInfo(index = true)
val performerid_map: Long
)
/* ADDED/ASSUMED JUNCTION BETWEEN DETAIL AND PERFORMER */
@Entity(
primaryKeys = ["detailid_link","performerid_link"]
)
data class DetailPerformerJunction(
val detailid_link: Long,
@ColumnInfo(index = true)
val performerid_link: Long
)
/* JUNCTION BEWTEEN EVENT AND DETAIL (allowing many details per Event and a detail to be in many events perhaps overboard) */
@Entity(
primaryKeys = ["eventid_ref","detailid_ref"]
)
data class EventDetailJunction(
val eventid_ref: Long,
@ColumnInfo(index = true)
val detailid_ref: Long
)
- note that column names are unique as suggested
And then POJO's:-
data class EventWithPerformerListPOJO(
@Embedded
val event: Event,
/* all the other columns omitted for brevity */
@Relation(
entity = Performer::class,
parentColumn = "eventid",
entityColumn = "performerid",
associateBy = Junction(
value = EventPerformerJunction::class,
parentColumn = "eventid_map",
entityColumn = "performerid_map"
)
)
val peformerList: List<Performer>
)
/* POJO for details with the performers */
data class DetailWithPerformersListPOJO(
@Embedded
val detail: Detail,
@Relation(
entity = Performer::class,
parentColumn = "detailid",
entityColumn = "performerid",
associateBy = Junction(
value = DetailPerformerJunction::class,
parentColumn = "detailid_link",
entityColumn = "performerid_link"
)
)
val performers: List<Performer>
)
And finally the POJO for the Final Result that hierarchically handles the detailsWithPerformersListPOJO within a Detail:-
data class EventWithListOfDetailsWithPerformersListPOJO (
@Embedded
val event: Event,
/* The List of performers at the Event level */
@Relation(
entity = Detail::class,
parentColumn = "eventid",
entityColumn = "detailid",
associateBy = Junction(
value = EventDetailJunction::class,
parentColumn = "eventid_ref",
entityColumn = "detailid_ref"
)
)
/* List of the details with each detail having a list of performers */
val details: List<DetailWithPerformersListPOJO>,
@Relation(
entity = Performer::class,
parentColumn = "eventid",
entityColumn = "performerid",
associateBy = Junction(
value = EventPerformerJunction::class,
parentColumn = "eventid_map",
entityColumn = "performerid_map"
)
)
val performers: List<Performer>
)
It should be noted that for brevity the main thread has been used, rather than MVVM, so no suspends on the Daos, which are:-
interface TheDAOs {
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(event: Event): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(detail: Detail): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(performer: Performer): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(eventPerformerJuntion: EventPerformerJunction): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(eventDetailJunction: EventDetailJunction): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(detailPerformerJunction: DetailPerformerJunction): Long
/*<<<<<<<<<< The query being demonstrated >>>>>>>>>>*/
@Transaction
@Query("SELECT * FROM event")
fun getAllEventSWithDetailsWithThePerformersAsPerDetail(): List<EventWithListOfDetailsWithPerformersListPOJO>
}
The @Database annotated class used for the demo:-
@Database(entities = [
Event::class,
Detail::class,
Performer::class,
EventDetailJunction::class,
DetailPerformerJunction::class,
EventPerformerJunction::class],
exportSchema = false,
version = 1
)
abstract class TheDatabase: RoomDatabase() {
abstract fun getTheDAOs(): TheDAOs
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/convenience */
.build()
}
return instance as TheDatabase
}
}
}
In an activity, to actually demonstrate the following:-
const val TAG = "DBINFO"
class MainActivity : AppCompatActivity() {
lateinit var db: TheDatabase
lateinit var dao: TheDAOs
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
db = TheDatabase.getInstance(this)
dao = db.getTheDAOs()
/* Add some events */
val e1=dao.insert(Event(title = "Event 1"))
val e2=dao.insert(Event(title = "Event 2"))
/* Add some Performers */
val p1=dao.insert(Performer(performerName = "Performer 1"))
val p2=dao.insert(Performer(performerName = "Performer 2"))
val p3=dao.insert(Performer(performerName = "Performer 3"))
val p4=dao.insert(Performer(performerName = "Performer 4"))
/* Add some details */
val d1=dao.insert(Detail(venueName = "Venue 1"))
val d2=dao.insert(Detail(venueName = "Venue 2"))
val d3=dao.insert(Detail(venueName = "Venue 3"))
val d4=dao.insert(Detail(venueName = "Venue 4"))
/* Tie performers to details */
dao.insert(DetailPerformerJunction(d1,p1))
dao.insert(DetailPerformerJunction(d1,p2))
dao.insert(DetailPerformerJunction(d1,p3))
dao.insert(DetailPerformerJunction(d2,p4))
dao.insert(DetailPerformerJunction(d2,p2))
dao.insert(DetailPerformerJunction(d4,p3))
dao.insert(DetailPerformerJunction(d4,p3)) /* IGNORED AS DUPLICATE */
dao.insert(DetailPerformerJunction(d4,p4))
/* Note has no performers */
/* Tie details to events */
dao.insert(EventDetailJunction(e1, d1))
dao.insert(EventDetailJunction(e1,d3))
dao.insert(EventDetailJunction(e2,d2))
dao.insert(EventDetailJunction(e2,d4))
/* Tie Performers to Events */
dao.insert(EventPerformerJunction(e1,p4))
dao.insert(EventPerformerJunction(e1,e2))
dao.insert(EventPerformerJunction(e2,p1))
dao.insert(EventPerformerJunction(e2,p3))
/* See the result */
for (ewdwp in dao.getAllEventSWithDetailsWithThePerformersAsPerDetail()) {
Log.d(TAG,"Event is ${ewdwp.event.title} ID is ${ewdwp.event.eventid} it has ${ewdwp.details.size} Details. They are:-")
for (d in ewdwp.details) {
Log.d(TAG,"\tDetail is ${d.detail.venueName} ID is ${d.detail.detailid} it has ${d.performers.size} details. They are:-")
for (p in d.performers) {
Log.d(TAG,"\t\tPeformer is ${p.performerName} ID is ${p.performerid}")
}
}
Log.d(TAG,"\t the Event also has ${ewdwp.performers.size} Performers (ex detail): They are:-")
for (p in ewdwp.performers) {
Log.d(TAG,"\tEx Detail Performer is ${p.performerName} ID is ${p.performerid}")
}
}
}
}
When run for the first time (not designed to be run more than once) then the log includes (the expected results):-
D/DBINFO: Event is Event 1 ID is 1 it has 2 Details. They are:-
D/DBINFO: Detail is Venue 1 ID is 1 it has 3 details. They are:-
D/DBINFO: Peformer is Performer 1 ID is 1
D/DBINFO: Peformer is Performer 2 ID is 2
D/DBINFO: Peformer is Performer 3 ID is 3
D/DBINFO: Detail is Venue 3 ID is 3 it has 0 details. They are:-
D/DBINFO: the Event also has 2 Performers (ex detail): They are:-
D/DBINFO: Ex Detail Performer is Performer 2 ID is 2
D/DBINFO: Ex Detail Performer is Performer 4 ID is 4
D/DBINFO: Event is Event 2 ID is 2 it has 2 Details. They are:-
D/DBINFO: Detail is Venue 2 ID is 2 it has 2 details. They are:-
D/DBINFO: Peformer is Performer 2 ID is 2
D/DBINFO: Peformer is Performer 4 ID is 4
D/DBINFO: Detail is Venue 4 ID is 4 it has 2 details. They are:-
D/DBINFO: Peformer is Performer 3 ID is 3
D/DBINFO: Peformer is Performer 4 ID is 4
D/DBINFO: the Event also has 2 Performers (ex detail): They are:-
D/DBINFO: Ex Detail Performer is Performer 1 ID is 1
D/DBINFO: Ex Detail Performer is Performer 3 ID is 3
Note
For brevity and simplicity and to avoid potential confusion Foreign Keys have not been specified. Foreign keys are suggested as they help to enforce and maintain referential integrity.
For example:-
@Entity(
primaryKeys = ["eventid_map","performerid_map"],
foreignKeys = [
ForeignKey(
/* Enforces RI (referential integrity) */
entity = Event::class,
parentColumns = ["eventid"],
childColumns = ["eventid_map"],
/* For help in maintaining RI Optional within a FKey */
onDelete = ForeignKey.CASCADE, /* deletes children of a deleted parent automatically */
onUpdate = ForeignKey.CASCADE /* will update the children if the value of the column that is referenced in the parent is changed */
),
ForeignKey(
entity = Performer::class,
parentColumns = ["performerid"],
childColumns = ["performerid_map"],
onDelete = ForeignKey.CASCADE,
onUpdate = ForeignKey.CASCADE
)
]
)
data class EventPerformerJunction(
val eventid_map: Long,
@ColumnInfo(index = true)
val performerid_map: Long
)
