How to get aggregated data in moor_flutter?

Let's say I have 2 simple tables Users and Orders:

Users has columns Id
Orders has columns Id and UserId

How do I get all orders of a user easily using moor_flutter and return it as a stream of the following model?

class UserModel {
  final String id;
  final List<OrderModel> orderModels;

  UserModel(this.id, this.orders);
}

class OrderModel {
  final String id;

  OrderModel(this.id);
}

This is the official documentation but it is not covering this use case.

Equivalent call with EFCore and C# would be:

public async Task<IEnumerable<UserModel>> GetUserOrders(String userId)
{
    return await _db.Users
        .Include(user  => user.Orders)
        .Where(user => user.Id == userId)
        .Select(user => new UserModel
        {
            Id = user.Id,
            OrderModels = user.Orders.Select(order => new OrderModel
            {
                Id = null,
            }).ToList()
        })
    .ToListAsync();
}

Solution

This feels like a hacky workaround but what I ended up doing is that I created 2 classes called HabitWithLogs and HabitModel. I put my query result into HabitWithLogs instances and then group them into HabitModel instances.

Data classes:

class HabitWithLog {
  final Habit habit;
  final HabitLog? habitLog;

  HabitWithLog({required this.habit, required this.habitLog}) : assert(habitLog == null || habitLog.habitId == habit.id);
}

class HabitModel {
  final Habit habit;
  final List<HabitLog> habitLogs;

  HabitModel({required this.habit, required this.habitLogs});
}

Dao method:

Future<List<HabitModel>> getAllHabits() async {
  // Get habits and order
  final query0 = (_db.select(_db.habits)..orderBy([(t) => OrderingTerm(expression: t.order, mode: OrderingMode.asc)]));

  // Join with habit logs
  final query1 = query0.join([
    leftOuterJoin(_db.habitLogs, _db.habitLogs.habitId.equalsExp(_db.habits.id)),
  ]);

  // Naive way that return the same habit multiple times
  final hwlList = query1.map((rows) => HabitWithLog(
        habit: rows.readTable(_db.habits),
        habitLog: rows.readTableOrNull(_db.habitLogs),
      ));

  // Group hwlList by habits
  final groups = (await hwlList.get()).groupListsBy((hwl) => hwl.habit);

  // Map grouping
  return groups.entries
      .map((group) => HabitModel(
            habit: group.key,
            habitLogs: (group.value[0].habitLog == null) ? List<HabitLog>.empty() : group.value.map((hwl) => hwl.habitLog!).toList(),
          ))
      .toList();
}

Mapping the stream feels terrible and should not be the only way to achieve this.