How to get the related list of tags via pivot table

[sharoonamjid]

I want to be able to get the list of related tags of TaskCategory. Tags are related to the TaskCategory via Taggable pivot table.

I have the following tables:

TaskCategory table

@DataClassName("TaskCategory")
class TaskCategories extends Table {
  IntColumn get id => integer()();

  TextColumn get title => text().nullable()();

  @override
  Set<Column> get primaryKey => {id};
}

Task table: child of TaskCategory.

@DataClassName("Task")
class Tasks extends Table {
  IntColumn get id => integer()();

  TextColumn get title => text().nullable()();

  @JsonKey('task_category_id')
  IntColumn get taskCategoryId => integer().nullable()();

  @override
  Set<Column> get primaryKey => {id};
}

Tags Table: it can be attached to any other model not just Task, that's why no related id is defined here.

@DataClassName("Tag")
class Tags extends Table {
  IntColumn get id => integer()();

  TextColumn get name => text().nullable()();

  @override
  Set<Column> get primaryKey => {id};
}

Pivot table for Tag and any other model that need to have tag attached to it. This table will be responsible to attach or remove tags from models like Tasks or TaskCategories or any other model that need some tags.

@DataClassName("Taggable")
class Taggables extends Table {
  @JsonKey('tag_id')
  IntColumn get tagId => integer()();

  @JsonKey('taggable_model_id')
  IntColumn get taggableModelId => integer()();  // it can be id of Task or TaskCategory model or any other model id that need to have tag attached to it.

  @JsonKey('taggable_model_type')
  TextColumn get taggableModelType => text()();  // it store the type of model in string e.g 'task' for Task model and 'task_category' for TaskCategory model so that we can get tags for the model based on model's id and type

  @override
  Set<Column> get primaryKey => {tagId, tagableModelId, tagableModelType};
}

This is how I get multiple related tasks of taskCategory so that I can display the TaskCategories along with it's Tasks in same place. But I don't know how to also get the multiple related tags of taskCategory.

TaskCategoryWithTasksAndTags

class TaskCategoryWithTasksAndTags {
  TaskCategoryWithTasksAndTags({
    required this.taskCategory,
    required this.tasks,
    required this.tags,
  });

  final TaskCategory taskCategory;
  final List<Task?> tasks;
  final List<Tag?> tags;
}

  Stream<List<TaskCategoryWithTasksAndTags>>
      watchTaskCategoryWithTasksAndTags() {
    final taskCategoriesStream = select(taskCategories).watch()
    final tasksStream = select(tasks).watch();
    final tags = // how to get taskCategories's related tags through taggables pivot table?

    return Rx.combineLatest3(
        taskCategoriesStream, tasksStream, tags, (
      List<TaskCategory> taskCategories,
      List<Task?> taskList,
      List<Tag?> tagList,
    ) {
      return taskCategories.map((taskCategory) {
        final tasks = taskList
            .where(
              (task) =>
                  task?.taskCategoryId ==
                  taskCategory.id,
            )
            .take(6)
            .toList();

        final tags = tagList.where() // need help to get the tags attached/related to the taskCategory

        return TaskCategoryWithTasksAndTags(
          taskCategory: taskCategory,
          tasks: tasks,
          tags: tags,
        );
      }).toList();
    });
  }

[sharoonamjid]

Any suggestion please?

[sharoonamjid]

Thank you very much @simolus3 I will try to write what you suggested. Please keep an eye on this discussion. Thank you. Good day.

[sharoonamjid]

I wrote this but stucked in getting the tags for the specific task category

Stream<List<TaskCategoryWithTasksAndTags>?>
watchTaskCategoriesWithTasksAndTags() {
  final taskCategoriesStream = watchTaskCategories();

  return taskCategoriesStream.switchMap((taskCategories) {
    final idToTaskCategory = {
      for (var taskCategory in taskCategories)
        taskCategory.id: taskCategory
    };

    final taskCategoryIds = idToTaskCategory.keys;
    final tasksStream = _watchTasks();
    final tagsStream = _watchTaskCategoryTags(taskCategoryIds);

    return Rx.combineLatest2(tasksStream, tagsStream,
            (List<Task>? tasksList,
            List<Tag>? tagsList) {
          return taskCategories.map((taskCategory) {
            final tasks = tasksList
                ?.where((task) => task.taskCategoryId == taskCategory.id);

            // can't get the tags of specific task category because tag doesn't 
            // have any direct reference of task category like tasks have,
            // how can I filter tags to get tags of specific task category?
            final tags = tagsList
                ?.where((tag) => ?? ??);

            return TaskCategoryWithTasksAndTags(
                taskCategory: taskCategory, tasks: tasks, tags: tags);
          }).toList();
        });
  }
}

Stream<List<Tag>?> _watchTaskCategoryTags(taskCategoryIds) {
  final taggableQuery = select(taggables).join(
    [
      innerJoin(
        tags,
        tags.id.equalsExp(taggables.taggableId) &
        taggables.taggableType.equals(ModelType.taskCategory),
      )
    ],
  )..where(taggables.taggableId.isIn(taskCategoryIds));

  return taggableQuery.watch().map((rows) {

    final idToTags = <int, List<Tag>>{};

    for (var row in rows) {
      final tag = row.readTable(tags);
      final id = row.readTable(taggables).taggableId;

      idToTags.putIfAbsent(id, () => []).add(tag);
    }
  });
}

Stream<List<Task>?> _watchTasks() {
  return select(tasks).watch();
}

[sharoonamjid]

@simolus3 Can you please help me with this please.

[simolus3]

You could change _watchTaskCategoryTags to return a list of tags for each category id. It looks like you're already constructing this map, you're just not returning it. You could change this to:

Stream<Map<int, List<Tag>>> _watchTaskCategoryTags(taskCategoryIds) {
  final taggableQuery = select(taggables).join(
    [
      innerJoin(
        tags,
        tags.id.equalsExp(taggables.taggableId) &
        taggables.taggableType.equals(ModelType.taskCategory),
      )
    ],
  )..where(taggables.taggableId.isIn(taskCategoryIds));

  return taggableQuery.watch().map((rows) {

    final idToTags = <int, List<Tag>>{};

    for (var row in rows) {
      final tag = row.readTable(tags);
      final id = row.readTable(taggables).taggableId;

      idToTags.putIfAbsent(id, () => []).add(tag);
    }
    return idToTags;
  });
}

When, you can find tags with final tags = tagsList[tagsCategory.id] ?? const [] .

[sharoonamjid]

Thank you very much. It worked!! 👍