7

I have a table Category which is Related to a Task table in a one-to-many relationship and I am attempting to perform a join using Moor.

I would like to return a list for the list of tasks that match a category. How do I do it?

      Stream<List<CategoryWithTask>> watchAllCategories() {
        return (select(categories)
              ..orderBy(([
                (c) => OrderingTerm(expression: c.name),
              ])))
            .join([leftOuterJoin(tasks, tasks.categoryId.equalsExp(categories.id))])
            .watch()
            .map((rows) => rows.map(
                  (row) {
                    return CategoryWithTask(
                        category: row.readTable(categories),
                        task: row.readTable(tasks)); // How do I modify this line to return a list of tasks corresponding to a category?
                  },
                ).toList());
      }
  • From the Moor Readme.md file, "If you have any questions, feedback or ideas, feel free to create an issue" https://github.com/simolus3/moor/issues/new – Ted Henry Feb 17 '20 at 00:44
  • Your code seems all right. Are you sure this does not give the expected output? – Augustin R Feb 19 '20 at 11:36
  • @AugustinR, my initial code was dealing with single entries for the join. I managed after help from the Moor Flutter support, see my latest response. – Barnett Temwa Msiska Feb 19 '20 at 15:09

1 Answers1

16

I managed to find a way with the support of the guys at Flutter Moor after creating an issue on Github. Below is the working code;

  Stream<List<CategoryWithTasks>> watchAllCategories() {
    return (select(categories)
          ..orderBy(([
            (c) => OrderingTerm(expression: c.name),
          ])))
        .join([leftOuterJoin(tasks, tasks.categoryId.equalsExp(categories.id))])
        .watch()
        .map((rows) {
          final groupedData = <Category, List<Task>>{};

          for (final row in rows) {
            final category = row.readTable(categories);
            final task = row.readTable(tasks);

            final list = groupedData.putIfAbsent(category, () => []);
            if (task != null) list.add(task);
          }

          return [
            for (final entry in groupedData.entries)
              CategoryWithTasks(category: entry.key, tasks: entry.value)
          ];
        });
  }
  • Hi, If you were to retrieve name in the tasks Table from Categories Table, and display it on a list. How would go about it ? – Olal May 08 '20 at 09:56
  • A faster version of this would be a map from `categoryId` to `CategoryWithTasks`. – runeks Jul 10 '20 at 11:03
  • How to do the same thing but to itself for example categories to it's parent/children like in here https://stackoverflow.com/questions/66734984/flutter-moor-how-to-self-join – hesham shawky Mar 21 '21 at 18:42