Filter and paginate

You’ll build a post listing that accepts optional filters, sorts, and pages through results, first with offsets and then with keyset cursors.

Prerequisites

The blog schema from Define a schema, migrated.

Steps

1. Basic predicates

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

final ctx = db.context();

await posts().where(PostTable.authorId.eq(1)).all(ctx);
await posts().where(PostTable.title.ilike('%dart%')).all(ctx);
await posts().where(PostTable.id.inList([1, 2, 3])).all(ctx);
await posts().where(PostTable.publishedAt.isNull).all(ctx);
await posts()
    .where(PostTable.publishedAt.between(DateTime.utc(2026, 1, 1), DateTime.utc(2026, 7, 1)))
    .all(ctx);
await posts().where(PostTable.tags.contains(['dart'])).all(ctx);

2. Combine conditions

Repeated where calls are AND-ed. Use .or and .not for anything else:

final q = posts()
    .where(PostTable.authorId.eq(1).or(PostTable.authorId.eq(2)))
    .where(PostTable.title.ilike('%draft%').not);

3. Build filters from optional inputs

class PostFilter {
  const PostFilter({this.authorId, this.search, this.tag, this.publishedOnly = true});
  final int? authorId;
  final String? search;
  final String? tag;
  final bool publishedOnly;
}

Query<Post> filtered(PostFilter f) {
  final q = posts();
  if (f.publishedOnly) q.where(PostTable.publishedAt.isNotNull);
  if (f.authorId != null) q.where(PostTable.authorId.eq(f.authorId!));
  if (f.search case final s? when s.isNotEmpty) {
    final escaped = s.replaceAll(r'\', r'\\').replaceAll('%', r'\%').replaceAll('_', r'\_');
    q.where(PostTable.title.ilike('%$escaped%'));
  }
  if (f.tag != null) q.where(PostTable.tags.contains([f.tag!]));
  return q;
}

The search text is a bound parameter. Escaping %, _ and \ makes it match literally instead of acting as wildcards.

4. Filter on enum columns

The builder binds Dart enums without converting them, so compare enum columns with a RawSql fragment and the label:

Expr<bool> hasStatus(PostStatus s) =>
    RawSql<bool>('"posts"."status" = \$1::post_status', [s.sqlValue]);

await posts().where(hasStatus(PostStatus.published)).all(ctx);

5. Sort

posts()
    .orderBy(PostTable.publishedAt, desc: true)
    .orderBy(PostTable.id, desc: true); // tie-breaker for a stable order

Let callers choose the sort only from a fixed set:

const sortColumns = {
  'title': PostTable.title,
  'published': PostTable.publishedAt,
};
Query<Post> sorted(Query<Post> q, String key, bool desc) =>
    q.orderBy(sortColumns[key] ?? PostTable.publishedAt, desc: desc);

6. Offset pagination

Good for small tables and “jump to page N” interfaces:

Future<({List<Post> items, int total})> page(PostFilter f, int page, int size) async {
  final total = await filtered(f).count(ctx);
  final items = await filtered(f)
      .orderBy(PostTable.publishedAt, desc: true)
      .orderBy(PostTable.id, desc: true)
      .limit(size)
      .offset((page - 1) * size)
      .all(ctx);
  return (items: items, total: total);
}

count ignores ordering, limit and offset. Build the query twice (or in a function as here) rather than reusing one builder, since clauses accumulate.

7. Keyset pagination

Faster at depth and stable while rows are inserted. Pass the last id of the previous page:

Future<({List<Post> items, int? next})> feed(PostFilter f, {int? before, int size = 20}) async {
  final items = await filtered(f)
      .after(PostTable.id, before, descending: true)
      .limit(size)
      .all(ctx);
  final next = items.length == size ? items.last.id : null;
  return (items: items, next: next);
}

final first = await feed(const PostFilter());
final second = await feed(const PostFilter(), before: first.next);

after adds both the comparison and the ORDER BY, so don’t add another leading orderBy. The column must be unique; the primary key is the safe choice.

8. Stream large result sets

For exports and batch jobs, stream reads rows through a cursor instead of loading them all:

await for (final post in filtered(const PostFilter(publishedOnly: false)).stream(ctx)) {
  sink.writeln(jsonEncode(post.toJson()));
}

It holds one connection in a transaction until the stream ends. Preloads don’t apply to streams.

Verify it works

Print the SQL a filter produces:

final c = filtered(const PostFilter(authorId: 3, search: '50%')).compile();
print(c.sql);
// SELECT ... FROM "posts" WHERE ((("posts"."published_at" IS NOT NULL) AND ("posts"."author_id" = $1)) AND ("posts"."title" ILIKE $2))
print(c.params); // [3, %50\%%]