Query Builder topic

Query Builder

Query is the entry point for reads: from(source) starts a query, refine it with .where(...), .join(...), .orderBy(...), .limit(...), then finish with .map(decoder).

final q = from(Users.table)
    .where(Users.age > 18)
    .orderBy(Users.name.asc())
    .limit(20)
    .map((r) => User(r.get(Users.id), r.get(Users.name), r.get(Users.age), r.get(Users.active)));
// q is a MappedQuery<User> — pass it to db.fetch, or use .load(db).
  • from(QuerySource) — a TableRef or a TableAlias. Single-table queries are Query<Tbl>, which keeps where compile-time scoped.
  • Projection is automatic (all columns of the involved tables). Narrow it with .select([Users.name, Users.age]).
  • .map((RowReader r) => …) decides the result shape — scalar, record, data class, nested objects. .mapWith(rowMapper) is sugar for .map(rowMapper.read).

Two-tier join safety

  • Single-table queries are fully compile-time scoped: from(Users.table).where(Posts.id.eq(1)) does not compile.
  • Joined queries relax to Query<Object?>; QueryBuilder validates at build time that every referenced table/alias is in the FROM/JOIN clause, throwing StateError otherwise.

Joins

// FK-driven: ON is derived from the Ref (posts.author_id = users.id).
from(Posts.table)
    .leftJoin(Users.table, onFk: Posts.authorId)
    .map((r) => '${r.get(Posts.title)} <- ${r.get(Users.name)}');

// Explicit ON with eqColumn (required for self-joins).
final mgr = Users.table.aliased('mgr');
from(Users.table)
    .innerJoin(mgr, on: Users.managerId.eqColumn(mgr.col(Users.id)))
    .map((r) => '${r.get(Users.name)} -> ${r.get(mgr.col(Users.name))}');
  • innerJoin / leftJoin take either on: (an Expression<bool>) or onFk: (a Ref column).
  • Joins chain freely; the projection auto-expands with the joined table's columns.
  • Self-joins use aliases: Users.table.aliased('mgr'), then mgr.col(Users.id). RowReader keys by the alias, so mgr.id ≠ users.id.
  • Generated relation queries (@Relation) do all of this for you — see Annotations & Codegen.

Reading rows

RowReader reads a result row by selection key — columns by table.name (alias-aware), aggregates by alias — never by positional index. Reading a column that isn't in the projection throws StateError.

.map((r) => '${r.get(Posts.title)} (${r.get(Posts.views)})')

MappedQuery and RowMapper wrap a Query with a row-shape mapping function. MappedQueryExecute adds basalt-style terminals:

Method Effect
.load(db) all rows
.first(db) first row, throws if empty
.optional(db) first row or null

Aggregates & grouping

Aggregate helpers are selections — pass them to .select([...]) and read them back with the same handle:

final total = countAll();
final n = await from(Users.table).select([total]).map((r) => r.get(total)).first(db);

final sumAge = Users.age.sum();
final avgAge = Users.age.avg();
final (s, a) = await from(Users.table)
    .select([sumAge, avgAge])
    .map((r) => (r.get(sumAge), r.get(avgAge)))
    .first(db);

GROUP BY + HAVING:

final perAuthor = Posts.views.sum();
final rows = await from(Posts.table)
    .select([Posts.authorId, perAuthor])
    .groupBy([Posts.authorId])
    .having(perAuthor.gt(100))
    .map((r) => (r.get(Posts.authorId), r.get(perAuthor)))
    .load(db);

SELECT DISTINCT via .distinct():

final kinds = await from(Users.table)
    .select([Users.active])
    .distinct()
    .map((r) => r.get(Users.active))
    .load(db);

Aggregate result types: count/countAll/countDistinct → int; sum/min/max → nullable, typed by the operand (int columns → int?, double columns and expressions → double?); avg → double?. When as: is omitted the alias derives from the column name (sum_age); expression operands require an explicit as:.

@HasMany fold queries (one SQL + JOIN)

Codegen for @HasMany emits a ${Class}Query companion extending FoldMappedQuery<T>: one SELECT with LEFT JOINs for children (and nested children), then a generated static fold(List<RowReader>) member that dedupes the cartesian product into parent rows with List<Child> fields.

// Generated — one round-trip via Connection.fetch, fold in Dart.
final rows = await CustomerProfileRowQuery()
    .order(Customers.name.asc())
    .limit(50)
    .load(db);
  • .mapFold(folder) — after manual JOINs, same pattern without codegen (a generated XQuery.fold is reusable here).
  • .load(db) / .optional(db) / .first(db) — on FoldMappedQuery via FoldMappedQueryExecute (folder(await db.fetch(this))).
  • Parent limit / offset — not SQL LIMIT on flat JOIN rows; the serializer adds WHERE root_pk IN (SELECT … ORDER BY … LIMIT/OFFSET). Call .withRootPk(rootPk) before .limit() / .offset().
  • Do not use .distinct() or GROUP BY on a fold query — deduping belongs in the folder.

Associations (grouped child loads)

loadGroupedByFk loads the children of many parents in one query and groups them by foreign key — avoids N+1:

final users = await from(Users.table).mapWith(UserQuery.mapper).load(db);
final postsByAuthor = await loadGroupedByFk(
  db, Posts.table, Posts.authorId, users.map((u) => u.id).toList(), readPost);
// Map<int, List<Post>>; every author id is present (empty list if no posts).

basalt-style aliases

basalt basalt_dart
users.filter(p) from(Users.table).filter(p) — ANDs repeated calls
.order(col.asc()) .order(Users.col.asc()) — alias for orderBy
col.eq_any([...]) Users.col.eqAny([...]) — alias for isIn
query.load(conn) query.load(db)
query.first(conn) query.first(db) — throws if no rows
query.first(conn).optional() query.optional(db) — null if no rows
users.find(1) from(Users.table).findBy(Users.id, 1) 
final adults = await UserQuery()
    .filter(Users.age.ge(18))
    .order(Users.name.asc())
    .load(db);

Classes

FoldMappedQuery<R> Query Builder
A JOIN Query whose SQL rows are folded into fewer parents via folder.
MappedQuery<R> Query Builder
A Query finished with a decoder — the executable SelectQuery.
Ordering Query Builder
One ORDER BY term.
Query<Scope> Query Builder
Immutable SELECT builder.
RowMapper<R> Query Builder
A reusable, codegen-friendly row decoder for a data class. @Queryable(Users) emits one of these (a single read built from RowReader.get calls). They compose freely — a Comment reader can call a Post reader on the same RowReader to nest objects, with no arity-specific machinery.
RowReader Query Builder
Reads typed values out of one result row, addressed by selection (a column or an aggregate), not by position. This is what makes a single map decoder work for any number of columns/tables: r.get(Users.name) returns a String regardless of where name sits in the projection, joins stay unambiguous (each column is keyed by table.name), and aggregates are keyed by their alias.

Extensions

FoldMappedQueryExecute on FoldMappedQuery<R> Query Builder
Terminal helpers for FoldMappedQuery — one SQL round-trip, fold in Dart.
MappedQueryExecute on MappedQuery<R> Query Builder
basalt-style terminal helpers so a finished query reads like query.load(db) / query.first(db) / query.optional(db).

Functions

loadGroupedByFk<Tbl, K, C>(Connection db, QuerySource<Tbl> childSource, TableColumn<K, Tbl> fk, List<K> parentKeys, C readChild(RowReader reader)) → Future<Map<K, List<C>>> Query Builder
Loads the children whose foreign key fk is one of parentKeys, grouped by that key — the belonging_to(parents).grouped_by(parents) pattern in a single query (avoids N+1). Every key in parentKeys is present in the result, mapping to an empty list when it has no children.