Nested many to many relationships

[mikededo]

Hello!

So I'm trying to create an application which some nested many-to-many relationships. I've tried many things, following the examples in the documentation but I struggling a lot. I will add some of the relationships, not to add all the database.

class Exercices extends Table {
  /// Autogenerated id
  IntColumn get id => integer().autoIncrement()();

  /// Exercise name
  TextColumn get name => text().withLength(min: 1, max: 250)();

  /// Favourite exercise
  BoolColumn get favourite => boolean().withDefault(const Constant(false))();

  /// Created at timestamp
  DateTimeColumn get createdAt => dateTime()();

  /// Deleted at timestamp
  DateTimeColumn get deletedAt => dateTime().nullable()();
}

class Workouts extends Table {
  /// Autogenerated id
  IntColumn get id => integer().autoIncrement()();

  /// Workout name
  TextColumn get name => text().withLength(min: 1, max: 250)();

  /// Counter that says how many times a workout has been permormed
  IntColumn get timesDone => integer().withDefault(const Constant(0))();

  /// Created at timestamp
  DateTimeColumn get createdAt => dateTime()();

  /// Deleted at timestamp
  DateTimeColumn get deletedAt => dateTime().nullable()();
}

class MuscleGroups extends Table {
  /// Autogenerated id
  IntColumn get id => integer().autoIncrement()();

  /// Muscle group name
  TextColumn get name => text().withLength(min: 1, max: 250)();

  /// Counter that says how many times a workout has been permormed
  IntColumn get color => integer()();

  /// Created at timestamp
  DateTimeColumn get createdAt => dateTime()();

  /// Deleted at timestamp
  DateTimeColumn get deletedAt => dateTime().nullable()();
}

/// Many to many class of [Workouts] and [Exercices]
@DataClassName('WorkoutExercise')
class WorkoutExercices extends Table {
  /// Workout id
  IntColumn get workout =>
      integer().customConstraint('NOT NULL REFERENCES workouts(id)')();

  /// Exercise id
  IntColumn get exercise =>
      integer().customConstraint('NOT NULL REFERENCES exercices(id)')();

  @override
  Set<Column> get primaryKey => {workout, exercise};
}

/// Many to many class of [Exercices] and [MuscleGroups]
class ExerciseMuscleTags extends Table {
  /// Exercise id
  IntColumn get exercise =>
      integer().customConstraint('NOT NULL REFERENCES exercices(id)')();

  /// Muscle group id
  IntColumn get muscleGroup =>
      integer().customConstraint('NOT NULL REFERENCES muscle_groups(id)')();

  @override
  Set<Column> get primaryKey => {exercise, muscleGroup};
}

(If anything from the code is not clear, ask me in the comments!). As you can see, if I would like to get all the information from a Workout, for instance, I would like to have it in a class like:

class ExerciseWithGroups {
  final Exercise exercise;
  final List<MuscleGroup> muscleGroups;

  ExerciseWithGroups({
    @required this.exercise,
    @required this.muscleGroups,
  });
}

class WorkoutWithExercices {
  final Workout workout;
  final List<ExerciseWithGroups> exercices;

  WorkoutWithExercices({
    @required this.workout,
    @required this.exercices,
  });  
}

Inside the class WorkoutWithExercices I would be able to get all the information from an Exercise, including a List of MuscleGroups.
I have been able to create one Stream of many-to-many relationships but I can't find a way of a many-to-many relationship inside another one.

[mikededo]

Could it be solved with moor files? If so, how would you do it?

[simolus3]

In general, queries like these are difficult to solve in sql since they require many result sets. Moor files don't really help here, you'll need to perform some merging in Dart.

Do you need to filter your query for specific workouts/exercises/muscle groups? If not, the best option would probably be to just watch everything across all tables. Then, you could use combineLatest from rxdart to group the independent rows into a list of WorkoutWithExercises.

[mikededo]

I thought of that, using Rx.combineLatest..., however, do you think it will take much time?
Because, if done this way, I would only need to map the different values and add manually the many-to-many relationships.

[mikededo]

Do you need to filter your query for specific workouts/exercises/muscle groups?

In any case, this could be done manually, since I may do a showAll, showFavourties, and so on.

[simolus3]

however, do you think it will take much time?

To implement? No, it should be fairly straightforward. In the combining method, I would

1. create a map of ids to workouts/exercises/groups by iterating over the respective table
2. use groupBy from dartx to list all muscle groups id per exercise id and then map that to the full object by looking it up in the map from 1. Now you have a map from exercise ids to ExerciseWithGroups. By repeating this step for workouts, you can build your list of WorkoutWithExercises

Doing this over and over again might have an impact on performance, but creating maps over the id should help with fast lookups. However, I wouldn't create this stream multiple times. Instead of creating it each time it's needed, I would recommend to store in a field and use that. Otherwise you'd run the logic for each listener.

[mikededo]

My main concern is how bad can impact performance. I will take that into account. However, since it is a personal app, it should not have a huge, huge amount of data.

Using flutter_bloc (or any other state management pattern) would be a possible way of not rebuilding the Stream?

[simolus3]

The stream won't do anything unless it has listeners, so you might as well store it in the database class. Storing the stream in a bloc should work too.

[mikededo]

A StreamSubscription, for instance.

[mikededo]

Hey!

I have finally solved. I have not used the groupBy function, however I will try to use it (I don't know if it's faster than what I've done). The result is here, if anyone is ever interested:

Stream<List<WorkoutWithExercices>> watchWorkouts({
    List<int> workoutIds,
}) {
  // Select all workouts
  final workoutQuery = workoutIds == null
      ? select(db.workouts).watch()
      : (select(db.workouts)..where((item) => item.id.isIn(workoutIds)))
          .watch();

  // Select all exercises
  final exerciseStream = select(db.exercices).watch();

  // Select all muscle groups
  final muscleGroupStream = select(db.muscleGroups).watch();

  // Many-to-many talbes
  final workoutToExerciseStream = select(db.workoutExercices).watch();
  final exerciseToMuscleGroupStream = select(db.exerciseMuscleTags).watch();

  return Rx.combineLatest5(
    workoutQuery,
    exerciseStream,
    muscleGroupStream,
    workoutToExerciseStream,
    exerciseToMuscleGroupStream,
    (
      List<Workout> workouts,
      List<Exercise> exercices,
      List<MuscleGroup> muscleGroups,
      List<WorkoutExercise> workoutExercices,
      List<ExerciseMuscleTag> exerciseMuscleTags,
    ) {
      // Item mapping
      final Map<int, Workout> idToWorkout = {
        for (Workout item in workouts) item.id: item
      };

      final Map<int, Exercise> idToExercise = {
        for (Exercise item in exercices) item.id: item
      };

      final Map<int, MuscleGroup> idToMuscleGroup = {
        for (MuscleGroup item in muscleGroups) item.id: item
      };

      // Mapping all exercise id with the list of the muscle groups 
      // involved in that exercise
      final Map<int, List<MuscleGroup>> muscleGroupsInExercise = {};
      for (ExerciseMuscleTag item in exerciseMuscleTags) {
        muscleGroupsInExercise
            .putIfAbsent(idToExercise[item.exercise].id, () => [])
            .add(idToMuscleGroup[item.muscleGroup]);
      }

      // Map the exercise id with its [ExerciseWithTags] object
      final Map<int, ExerciseWithTags> exercicesWithTags = {};
      muscleGroupsInExercise.forEach(
        (id, list) => exercicesWithTags.putIfAbsent(
          id,
          () => ExerciseWithTags(
            exercise: idToExercise[id],
            muscleGroups: list,
          ),
        ),
      );

      // Map each workout with the list of exercices involved in
      // that workout for fastest searchers
      final Map<Workout, List<ExerciseWithTags>> exercicesInWorkoutIds = {};
      for (WorkoutExercise item in workoutExercices) {
        exercicesInWorkoutIds
            .putIfAbsent(idToWorkout[item.workout], () => [])
            .add(exercicesWithTags[item.exercise]);
      }

      // Finall put all items into the list as [WorkoutWithExercices]
      List<WorkoutWithExercices> result = [];
      exercicesInWorkoutIds.forEach(
        (key, value) => result.add(
          WorkoutWithExercices(
            workout: key,
            exercices: value,
          ),
        ),
      );

      return result;
    },
  );
}

[mikededo]

@simolus3, one last thing. In order to reduce code duplication, do you recomend using Dao classes inside other Dao classes?

For example, I also have a table named Programs which it has n workouts in it (another many-to-many relationship). So, instead of repeating all I've written to nest the MuscleGroups into the Exercices and then the ExercicesWithMuscleGroups into the Workouts, to add them to the programs, I would reuse the function from the WorkoutDao that already does so and use combineStream to join them.

[simolus3]

I don't have strong opinions on how daos should be used, for me they're just a tool to split database code into smaller modules. I don't see see any issues with that approach as long as you don't have circular dependencies between daos.