Eager loading

You’ll load a page of posts together with their authors, comments and the comments’ authors in four queries, and serialise the result.

Prerequisites

The blog schema from Define a schema, with reverse_name set on the foreign keys you want to traverse backwards.

Steps

1. Preload a belongs-to relation

import 'package:blog/models/blog.gisila.g.dart';
import 'package:gisila_orm/gisila.dart';

final ctx = db.context();

final list = await posts()
    .orderBy(PostTable.id, desc: true)
    .limit(20)
    .preload([Post.author])
    .all(ctx);

for (final p in list) {
  print('${p.title} by ${p.authorLoaded?.name}');
}

Single relations are read with <name>Loaded and are null when the foreign key is null or the relation wasn’t preloaded.

2. Preload a has-many relation

final list = await authors().preload([Author.posts]).all(ctx);
for (final a in list) {
  print('${a.name}: ${a.postsList.length} posts');
}

Collections are read with <name>List, an empty list when there are no children.

3. Several relations and nested chains

final list = await posts()
    .where(PostTable.publishedAt.isNotNull)
    .limit(20)
    .preload([
      Post.author,
      Post.comments.then(Comment.author),
    ])
    .all(ctx);

That’s four queries in total: posts, their authors, their comments, the comments’ authors. then loads the next level onto the children of the previous one; it can be chained further (Author.posts.then(Post.comments.then(Comment.author))).

Don’t also list Post.comments on its own; the chain already loads it.

4. Preload with `one` and `first`

final post = await posts()
    .where(PostTable.id.eq(postId))
    .preload([Post.author, Post.comments])
    .first(ctx);

5. Serialise with children

Generated toJson covers the model’s own columns. Add relations yourself:

Map<String, Object?> postJson(Post p) => {
      ...p.toJson(),
      'author': p.authorLoaded?.toJson(exclude: ['email']),
      'comments': [
        for (final c in p.commentsList)
          {...c.toJson(), 'author': c.authorLoaded?.toJson(only: ['id', 'name'])},
      ],
    };

6. Distinguish “not loaded” from “empty”

if (!post.isPreloaded('comments')) {
  throw StateError('load comments before rendering');
}

7. Preload onto rows you already have

final result = await ctx.execute(
  'SELECT * FROM posts WHERE to_tsvector(body) @@ plainto_tsquery(\$1)',
  parameters: ['dart'],
);
final found = Hydrator<Post>(Post.fromRow).hydrateAll(result);
await const Preloader().applyTo(found, [Post.author], ctx);

When to write a query instead

Preloading loads every child row with all columns. For “latest 3 comments per post” or “number of comments per post”, query directly:

final c = comments()
    .where(CommentTable.postId.inList([for (final p in list) p.id!]))
    .groupBy(CommentTable.postId)
    .compile(overrideSelect: '"comments"."post_id", COUNT(*)::int AS "n"');
final counts = {
  for (final r in await ctx.execute(c.sql, parameters: c.params))
    r.toColumnMap()['post_id'] as int: r.toColumnMap()['n'] as int,
};

Verify it works

Count the queries with an observer:

var queries = 0;
final db = await Database.connect(config, onQuery: (_, __, ___, ____) => queries++);
await posts().limit(20).preload([Post.author, Post.comments.then(Comment.author)]).all(db.context());
print(queries); // 4, however many posts there are