Room Database, structuring and querying entity with list

Question

Here is my setup. I have a WorkoutPlan object that can contain a list of Workout objects in it. The way I'm currently modeling it is by have a third table that handles mapping the two together. A WorkoutPlan can contain many workouts, and a Workout can be used by many WorkoutPlans.

@Entity(tableName = "workoutPlans")
data class DbWorkoutPlan(@ColumnInfo(name = "name")
                         val name: String,
                         @ColumnInfo(name = "date")
                         val date: Date) {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    var id: Int = 0
}

@Entity(tableName = "workouts")
data class DbWorkout(@ColumnInfo(name = "name")
                     val name: String,
                     @ColumnInfo(name = "date")
                     val data: Date) {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    var id: Int = 0
}

@Entity(tableName = "DbWorkoutPlanWorkoutJoin",
    primaryKeys = arrayOf("workoutPlanId", "workoutId"),
    foreignKeys = arrayOf(ForeignKey(entity = DbWorkoutPlan::class,
                                     parentColumns = arrayOf("id"),
                                     childColumns = arrayOf("workoutPlanId")),
                          ForeignKey(entity = DbWorkout::class,
                                     parentColumns = arrayOf("id"),
                                     childColumns = arrayOf("workoutId"))))
data class DbWorkoutPlanWorkoutJoin(@ColumnInfo(name = "workoutPlanId")
                                    val workoutPlanId: Int,
                                    @ColumnInfo(name = "workoutId")
                                    val workoutId: Int)

So that is my data setup for the tables. I'm not sure if it's correct. On the returned data side I have this.

data class DbWorkoutPlanResult(@Embedded
                               val workoutPlan: WorkoutPlan,
                               @Relation(parentColumn = "id", entityColumn = "workoutId")
                               val workoutIds: List<DbWorkout>)

So I want to get back a DbWorkoutPlanResult containing one WorkoutPlan and a list of all the Workouts it has.

I know I'm not doing this right, and the complexity is increasing quickly. Does anyone know what I have done wrong in my setup? And what would I have for a query? My best attempt is this

@Query("SELECT * " +
               "FROM DbWorkoutPlanWorkoutJoin " +
               "INNER JOIN workoutPlans " +
               "ON DbWorkoutPlanWorkoutJoin.workoutPlanId = workoutPlans.id " +
               "INNER JOIN workouts " +
               "ON DbWorkoutPlanWorkoutJoin.workoutId = workouts.id ")
fun getWorkoutPlans(): Flowable<List<DbWorkoutPlanResult>>

Thanks in advance.

Answer 1

Using @Relation annotation you can create a 1:N (one to many) relation. Like, in your case, a single plan can have multiple workouts, but each workout can belong to only a single plan. This is clearly not what you want!

For your needs, which I assume are like this: get a list of POJOs containing a planand list of associated workouts, you need to use a separate JOIN Table (which I guess you already are).

A simple way to get the results would be to divide the operations into two queries:

1. Get List<DbWorkoutPlan> of all plans
2. Query the Join Table and fetch all List<DbWorkout> for each DbWorkoutPlan

---

Example code

First define the models

@Entity(tableName="plans") class DbWorkoutPlan {
  @PrimaryKey
  private long id;
  // ...

  @Ignore private List<DbWorkout>; // do not persist this, also create getter/setter for this field
}

@Entity(tableName="workouts") class DbWorkout {
  @PrimaryKey
  private long id;
  // ...
}

@Entity(
  tableName="plan_workout_join"
  primaryKeys = {"workoutPlanId", "workoutId"},
  foreignKeys = {
    @ForeignKey(entity = DbWorkoutPlan.class, parentColumns = "id", childColumns = "plan"),
    @ForeignKey(entity = DbWorkout.class, parentColumns = "id", childColumns = "workout")
  }
) class PlanWorkoutJoin {
  private long plan;
  private long workout;
}

Now in the DAO,

@Query("SELECT * FROM plans")
List<DbWorkoutPlan> getAllPlans();

@Query("SELECT * FROM workouts WHERE workouts.id IN (SELECT workout FROM plan_workout_join WHERE plan_workout_join.plan=:plan)")
List<DbWorkout> getWorkoutsForPlan(long plan);

Now you can query like,

List<DbWorkoutPlan> plans = dao.getAllPlans();
for(DbWorkoutPlan plan : plans){
  List<DbWorkout> workouts = dao.getWorkoutsForPlan(plan.getId());
  plan.setWorkouts(workouts);
}
// ... continue

P.S. You'll obviously need to modify this a bit if you are using RxJava, but the core idea remains the same