Help with one-to-many query

[NatanAmorim]

Hello I have been looking for days for a official way of doing a one-to-many select but everything I find seems like a hack.

Here's some of the best i could find:
discussion#1491: one to many relation
issue#523: One-to-Many query assistance
issue#1491: Selecting the last records in a one-to-many relationship

I checked the Official docs, videos and some offical examples, but everyone one seems to do it differently, while having no suggestion in the docs.

Here's some simple tables for an example.

@DataClassName('UserEntity')
class Users extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get username => text().unique()();
}

@DataClassName('PostEntity')
class Posts extends Table {
  IntColumn get id => integer().autoIncrement()();
  IntColumn get userId => integer().references(Users, #id)();
  TextColumn get content => text()();
}

class UserWithPosts {
  final UserEntity user;
  final List<PostEntity> posts;

  UserWithPosts({
    required this.user,
    required this.posts,
  });
}

Here's how i'm getting the data I want, can do this with a leftOuterJoin and only one query?

Future<List<UserWithPosts>> getUsersWithPosts() async {
  /// My actual query is more complicated than this, this is why `dbquery`
  /// exists and it's not just `final rows = await select(users).get();`
  final dbQuery = select(users);
  final rows = await dbQuery.get();

  final List<PostEntity> allPosts = await select(posts).get();

  return rows.map((row) {
    return UserWithPosts(
      row,
      allPosts.where((p) => p.userId == row.id).toList(),
    );
  }).toList();
}

[dickermoshe]

The new manager API makes this easy.
You need the latest version of drift.

final results = await db.managers.users.withReferences((prefetch)=>prefetch(posts:true)).get();
for (final (user, ref) in results){
  posts = ref.posts.prefechedData;
}

[NatanAmorim]

You're correct, it even has an identical example in the official documentation, I don't know how I didn't see that before, thanks a lot, i'm learning it and testing right now.

[NatanAmorim]

Yup, I tested and it worked perfectly, it's also cool that drift is already using records introduced not too long ago in Dart 3.0, things do move really fast in the dart/flutter world 🤔

Anyway if anyone sees this and get confused on how to iterate records, you can do:

return results.map((result) {
  return UserWithPosts(
    result.$1,
    result.$2.posts.prefechedData ?? [],
  );
}).toList();

or

List<UserWithPosts> usersWithPosts = [];

for (final (user, ref) in results) {

  usersWithPosts.add(
    UserWithPosts(
    user,
    ref.posts.prefechedData ?? [],
  ));
}

return usersWithPosts;

Thx again dickermoshe!