How to handle Room relation, embedded lists, and live data?

Question

Im making a workout app. In this app, you can create multiple programmes, that contains multiple workouts that contain multiple exercises.

I manage all of this in a Room database

Programme.kt

@Entity(tableName = "programmes")
data class Programme(
    @PrimaryKey
    val programmeId:String,
    val name:String,
    var nextWorkoutId: String,
    var lastWorkout:Long,
    @Ignore
    val workouts:List<Workout>
)

Workout.kt

@Entity(tableName = "workouts", indices = [Index("workoutId")])
data class Workout(
    @PrimaryKey
    val workoutId: String,
    val name: String,
    @Ignore
    val exercises: List<Exercise>
)

ProgrammeWithWorkouts

data class ProgrammeWithWorkouts(
    @Embedded val programme: Programme,
    @Relation(
        parentColumn = "programmeId",
        entityColumn = "workoutId",
    )
    val workouts:List<WorkoutsWithExercises>
)

@Entity(primaryKeys = ["programmeId","workoutId"],
indices = [Index("programmeId","workoutId")])
data class ProgrammeWorkoutCrossRef(
    val programmeId:String,
    val workoutId:String
)

And then I want to listen to changes via live data:

@Transaction
@Query("SELECT * FROM programmes")
    fun getProgrammesWithWorkoutsLiveData(): LiveData<List<ProgrammesWithWorkouts>>

Unfortunatly this throw an error:

constructor WorkoutWithRoutines in class ProgrammeWithWorkouts cannot be applied to given types;
            _item = new ProgrammeWithWorkouts();
                    ^
  required: Programme,List<WorkoutsWithExercises>
  found: no arguments
  reason: actual and formal argument lists differ in length

Is there anyway this is possible?

Answer 1

The typical way that many-many relationships are handled is with an intermediate table that maps the relationships. Such a table will have two core columns one that is a unique identifier of one of the rows in one of the tables the other a unique identifier of one of the rows in the other table.

As you have two levels of many-many you would have the core tables Programme, Workout and Exercise. The Programme - Workout is a many-many, the Workout to the Exercise is also a many-many. As such you would have 2 mapping tables.

When it comes to Room then you use @Embedded for one side of the relationship and @Relation for the other within a POJO that represents the "complete".

• I see @Ignore coded as if this magically does something, this is not really required and can be a pain. Rather I would suggest considering the Entity purely as the interface between the non object orientated database and the final/used Object and hence the POJO being the "complete" object.

  • @Ignore could be of use for supplementary data not stored but derived, perhaps when extracted via a derived column or perhaps via the class's functions.

Perhaps consider the following example (I use Long's for the id's, they are more efficient and faster):-

So first the Three core tables/entities:-

• Note that this was adapted from another answer so they may differ a little from yours but the principle applies.

Exercise :-

@Entity(
    indices = [Index(value = ["exerciseName"],unique = true)] // enforce unique exercise name.
)
data class Exercise(
    @PrimaryKey
    val exerciseId: Long? = null,
    val exerciseName: String
)

• in the other answer the use of UNIQUE for the exercise name was included to show it's effect with OnConflictStrategy; left in for convenience.

Workout

@Entity
data class Workout(
    @PrimaryKey
    val workoutId: Long? = null,
    val workoutName: String
)

Programme

@Entity(tableName = "programmes")
data class Programme(
    @PrimaryKey
    val programmeId:Long? = null,
    val name:String,
    var nextWorkoutId: String,
    var lastWorkout:Long
)

• As explained the effectively useless @Ignore's have been omitted

Next are the 2 mapping tables

WorkoutExerciseMap

@Entity(
    primaryKeys = ["workoutIdMap","exerciseIdMap"], // a combination of  workout/exercise is primary key
    indices = [Index("exerciseIdMap")],  // Room issues warning if not indexed
    foreignKeys = [
        // Foreign keys, each defines a constraint (rule) saying value to be store MUST exist in the parent table
        // i.e. the value to be stored in the workoutIdMap MUST be the id of an existing Workout
        ForeignKey(
            entity = Workout::class, // the entity/table that the FK points to
            parentColumns = ["workoutId"], // the column in the parent table
            childColumns = ["workoutIdMap"], // column in this table where
            onDelete = ForeignKey.CASCADE, // if a Workout is deleted then delete the children
            onUpdate = ForeignKey.CASCADE // if a workoutId is changed then change the children
        ),
        ForeignKey(entity = Exercise::class,parentColumns = ["exerciseId"],childColumns = ["exerciseIdMap"])
    ]
)
data class WorkoutExerciseMap(
    val workoutIdMap: Long,
    val exerciseIdMap: Long
)

• see comments
• Note that Foreign Keys are not required nor do they define a relationship, instead they support the relationship in helping to ensure referential integrity (in brief, no orphans).

ProgrammeWorkoutMap

@Entity(
    primaryKeys = ["programmeIdMap","workoutIdMap"],
    indices = [Index("workoutIdMap")],
    foreignKeys = [
        ForeignKey(
            entity = Programme::class,
            parentColumns = ["programmeId"],
            childColumns = ["programmeIdMap"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
    ForeignKey(
        entity = Workout::class,
        parentColumns = ["workoutId"],
        childColumns = ["workoutIdMap"],
        onDelete = ForeignKey.CASCADE,
        onUpdate = ForeignKey.CASCADE
    )
    ]
)
data class ProgrammeWorkoutMap (
    val programmeIdMap: Long,
    val workoutIdMap: Long
)

For the WorkoutExercise mapping, extraction, a POJO WorkoutWithExercises with the single Workout @Embedded and the 0-N exercises with an @Relation

• Note how I am working up through the hierarchy from the bottom (Exercise) and try to be descriptive with the class names (hopefully to make it easier to understand)

:-

@Entity(
    primaryKeys = ["workoutIdMap","exerciseIdMap"], // a combination of  workout/exercise is primary key
    indices = [Index("exerciseIdMap")],  // Room issues warning if not indexed
    foreignKeys = [
        // Foreign keys, each defines a constraint (rule) saying value to be store MUST exist in the parent table
        // i.e. the value to be stored in the workoutIdMap MUST be the id of an existing Workout
        ForeignKey(
            entity = Workout::class, // the entity/table that the FK points to
            parentColumns = ["workoutId"], // the column in the parent table
            childColumns = ["workoutIdMap"], // column in this table where
            onDelete = ForeignKey.CASCADE, // if a Workout is deleted then delete the children
            onUpdate = ForeignKey.CASCADE // if a workoutId is changed then change the children
        ),
        ForeignKey(entity = Exercise::class,parentColumns = ["exerciseId"],childColumns = ["exerciseIdMap"])
    ]
)
data class WorkoutExerciseMap(
    val workoutIdMap: Long,
    val exerciseIdMap: Long
)

To get the next level, Programme with Workout, you actually get Programme with WorkoutWithExercises, so ProgrammeWithWorkoutsWithExercises :-

data class ProgrammeWithWorkoutsWithExercises (
    @Embedded
    val programme: Programme,
    @Relation(
        entity = Workout::class,
        parentColumn = "programmeId",
        entityColumn = "workoutId",
        associateBy = Junction(
            value = ProgrammeWorkoutMap::class,
            parentColumn = "programmeIdMap",
            entityColumn = "workoutIdMap"
        )
    )
    val workouts: List<WorkoutWithExercises>

)

• NOTE although WorkoutWithExercises objects are being extracted, there is no such table so the entity is Workout NOT WorkoutWithExercises

The dao's for the above (and the ensuing working demo) are :-

@Dao
abstract class AllDao {

    /*
     As exercise has a unique index on exercisename skip if same exercise name is used
     otherwise duplicating name will result in an exception
     */
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(exercise: Exercise): Long
    @Insert
    abstract fun insert(workout: Workout): Long
    @Insert
    abstract fun insert(programme: Programme): Long
    @Insert
    abstract fun insert(workoutExerciseMap: WorkoutExerciseMap): Long
    @Insert
    abstract fun insert(programmeWorkoutMap: ProgrammeWorkoutMap): Long

    @Query("SELECT * FROM workout") /* Not Used */
    abstract fun getAllWorkouts(): List<Workout>
    @Query("SELECT * FROM workout WHERE workout.workoutId=:workoutId") /* Not used in Demo */
    abstract fun getWorkoutById(workoutId: Long): Workout
    @Query("SELECT * FROM Exercise")
    abstract fun getAllExercises(): List<Exercise>
    @Query("SELECT * FROM exercise WHERE exercise.exerciseId=:exerciseId") /* Not Used in Demo */
    abstract fun getExerciseById(exerciseId: Long): Exercise
    @Query("SELECT * FROM workout") /* Not used  in Demo*/
    @Transaction
    abstract fun getAllWorkoutsWithExercises(): List<WorkoutWithExercises> 
    /* Only extract used in Demo */
    @Query("SELECT * FROM programmes")
    @Transaction
    abstract fun getAllPorgrammesWithWorkoutsWithExercises(): List<ProgrammeWithWorkoutsWithExercises>
}

The @Database in the TheDatabase includes the 5 entities as per :-

@Database(entities = [Programme::class,Workout::class,ProgrammeWorkoutMap::class,Exercise::class,WorkoutExerciseMap::class],version = 1)

The demo runs on the the main thread and the code in the activity is :-

    db = TheDatabase.getDatabaseInstance(this)
    dao = db.getAllDao()


    var ex1 = dao.insert(Exercise(exerciseName = "Exercise1"))
    var ex2 = dao.insert(Exercise(exerciseName = "Exercise2"))
    var ex3 = dao.insert(Exercise(exerciseName = "Exercise3"))
    var ex4 = dao.insert(Exercise(exerciseName =  "Exercise4"))
    var ex5 = dao.insert(Exercise(exerciseName = "Exercise5"))

    var wo1 = dao.insert(Workout(workoutName =  "Workout1"))
    var wo2 = dao.insert(Workout(workoutName = "Workout2"))

    var ex6 = dao.insert(Exercise(exerciseName = "Exercise6"))
    var ex7 = dao.insert(Exercise(exerciseName = "Exercise7"))
    var wo3 = dao.insert(Workout(workoutName = "Workout3"))
    var wo4 = dao.insert(Workout(workoutName =  " Workout4"))
    var wo5 = dao.insert(Workout(workoutName = "Workout5"))

    // Add 4 exercises to Workout1
    dao.insert(WorkoutExerciseMap(wo1,ex7))
    dao.insert(WorkoutExerciseMap(wo1,ex5))
    dao.insert(WorkoutExerciseMap(wo1,ex3))
    dao.insert(WorkoutExerciseMap(wo1,ex1))

    // Add 3 Exercises to Workout2
    dao.insert(WorkoutExerciseMap(wo2,ex2))
    dao.insert(WorkoutExerciseMap(wo2,ex4))
    dao.insert(WorkoutExerciseMap(wo2,ex6))

    // Add 2 Exercises to Workout3
    dao.insert(WorkoutExerciseMap(wo3,ex3))
    dao.insert(WorkoutExerciseMap(wo3,ex4))

    // Add 1 Exercise to Workout 4
    dao.insert(WorkoutExerciseMap(wo4,ex5))

    // Don't add anything to Workout 5

    // Add some Programmes
    var p1 = dao.insert(Programme(name = "Prog1", nextWorkoutId = "????",lastWorkout = 100L))
    var p2 = dao.insert(Programme(name = "Prog2",nextWorkoutId = "????", lastWorkout = 200L))
    var p3 = dao.insert(Programme(name = "Prog3", nextWorkoutId = "????", lastWorkout =  300L))

    //Map Workouts to Programmes (none for p3)

    dao.insert(ProgrammeWorkoutMap(p1,wo3))
    dao.insert(ProgrammeWorkoutMap(p1,wo5))
    dao.insert(ProgrammeWorkoutMap(p1,wo2))

    dao.insert(ProgrammeWorkoutMap(p2,wo1))
    dao.insert(ProgrammeWorkoutMap(p2,wo4))

    for(pww: ProgrammeWithWorkoutsWithExercises in dao.getAllPorgrammesWithWorkoutsWithExercises()) {
        Log.d(TAG,"Programme is ${pww.programme.name}")
        for(wwe: WorkoutWithExercises in pww.workouts) {
            Log.d(TAG,"\tWorkout is ${wwe.workout.workoutName} (in Programme ${pww.programme.name}) ")
            for (e: Exercise in wwe.exercises) {
                Log.d(TAG,"\t\tExercise is ${e.exerciseName} (in Workout ${wwe.workout.workoutName} that is in Programme ${pww.programme.name})")
            }
        }
    }

The demo, when first run (it isn't designed to run multiple times, just enough to the show that the principle works) results in the following being output to the log:-

D/WOEINFO: Programme is Prog1
D/WOEINFO:  Workout is Workout2 (in Programme Prog1) 
D/WOEINFO:      Exercise is Exercise2 (in Workout Workout2 that is in Programme Prog1)
D/WOEINFO:      Exercise is Exercise4 (in Workout Workout2 that is in Programme Prog1)
D/WOEINFO:      Exercise is Exercise6 (in Workout Workout2 that is in Programme Prog1)
D/WOEINFO:  Workout is Workout3 (in Programme Prog1) 
D/WOEINFO:      Exercise is Exercise3 (in Workout Workout3 that is in Programme Prog1)
D/WOEINFO:      Exercise is Exercise4 (in Workout Workout3 that is in Programme Prog1)
D/WOEINFO:  Workout is Workout5 (in Programme Prog1) 
D/WOEINFO: Programme is Prog2
D/WOEINFO:  Workout is Workout1 (in Programme Prog2) 
D/WOEINFO:      Exercise is Exercise1 (in Workout Workout1 that is in Programme Prog2)
D/WOEINFO:      Exercise is Exercise3 (in Workout Workout1 that is in Programme Prog2)
D/WOEINFO:      Exercise is Exercise5 (in Workout Workout1 that is in Programme Prog2)
D/WOEINFO:      Exercise is Exercise7 (in Workout Workout1 that is in Programme Prog2)
D/WOEINFO:  Workout is  Workout4 (in Programme Prog2) 
D/WOEINFO:      Exercise is Exercise5 (in Workout  Workout4 that is in Programme Prog2)
D/WOEINFO: Programme is Prog3