Streaming & aggregates

The query builder reads more than full-row slices: stream rows one at a time with Iter, pull a single column into a slice with Pluck, and compute whole-set or grouped aggregates without ever writing SQL by hand. This page builds on reading data and the Product model from the hub.

For exhaustive API detail, see the reference at pkg.go.dev/liteorm.org/query.

When a result set is large and you’d rather not hold it all in memory, use .Iter(ctx). It returns an iter.Seq2[T, error] you range over directly; rows are scanned lazily and the underlying rows are closed when you stop — including an early break.

n := 0
for p, err := range query.Select[Product](db).OrderBy("price").Iter(ctx) {
  if err != nil {
    return err
  }
  fmt.Println(p.Name, p.Price)
  if n++; n == 3 {
    break // streaming stops early; rows are closed
  }
}

This is the reflection-free streaming path — a constant-memory alternative to All for large scans. See performance for how it compares to batched reads.

To pull one column into a slice — the “give me all the emails / ids” read — use Pluck, which projects one typed column and scans the values directly (no full-row structs):

emails, _ := query.Pluck(ctx, query.Select[User](db).Filter(active), query.Col[string]("email"))
// emails is []string, honoring the builder's filters/order/limit

PluckExpr[T, V](ctx, b, expr, args...) does the same for a raw scalar expression — MAX(x), COALESCE(a, b), LENGTH(t), rowid — and PluckExprFirst returns just the first value (V’s zero if there are no rows).

Distinct() adds SELECT DISTINCT. GroupByCols(cols...) groups by typed, validated, dialect-quoted columns (GroupBy(cols...) is the raw-string escape hatch); Having(frag, args...) adds a raw, AND-joined HAVING condition (the frag carries positional ? markers bound by args).

rows, err := query.Select[Product](db).
  Distinct().
  GroupByCols(query.Col[string]("category").Field()).
  Having("count(*) > ?", 2).
  All(ctx)

For a whole-set aggregate, the typed terminals build SELECT AGG(col) over your filters and return the scalar (a result over no rows comes back as the zero value, not an error):

revenue, _ := query.Sum(ctx, query.Select[Order](db).Filter(paid), query.Col[int64]("total"))
avgPrice, _ := query.Avg(ctx, query.Select[Product](db), query.Col[float64]("price")) // returns float64
cheapest, _ := query.Min(ctx, query.Select[Product](db), query.Col[float64]("price"))

Sum/Min/Max return the column’s type, Avg returns float64, and CountCol returns int64. (.Count(ctx) remains the row-count terminal.) A whole-set aggregate can’t combine with GroupBy / DistinctOn / a set operation — use Into for grouped aggregates instead.

For a grouped aggregate, project the grouped columns and the aggregate expressions into a result struct with Into — column-validated and dialect-quoted, the typed counterpart of Raw:

type byCategory struct {
  Category string `db:"category"`
  Revenue  int64  `db:"revenue"`
}
stats, err := query.Into[Product, byCategory](ctx,
  query.Select[Product](db).GroupByCols(query.Col[string]("category").Field()),
  query.Col[string]("category").Field(),
  query.SumAs(query.Col[float64]("price"), "revenue"))

The aggregate projection helpers are SumAs/AvgAs/MinAs/MaxAs/CountAs(col, alias); Name("col") projects a plain column and Expr("…") is the raw escape hatch within a projection. The result struct’s columns must match the projection’s names and aliases. Into is also how you select window functions, scalar subqueries, and ExistsField — see advanced SQL and joins & subqueries. For a fully custom shape, Raw stays available.

• Reading data — the filters and ordering these reads honor.
• Advanced SQL — window functions and scalar subqueries, also via Into.
• Performance — iter.Seq2 streaming vs batched reads.
• Raw SQL — when the projection shape outgrows Into.