Advanced SQL

This is the part of the query builder that most ORMs leave you writing raw strings for: common table expressions and recursive CTEs, window functions, set operations, DISTINCT ON, row locking, and scalar subqueries — all typed, all column-validated, all rendered for the backend you’re on. Every one of these gates honestly: on a dialect that can’t do it, you get a clear build-time error, never surprising SQL. This page builds on reading data and joins & subqueries.

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

CTEs and recursive CTEs

With(name, sub) prepends a common table expression; reference it as the FROM source with From(name):

active := query.Select[User](db).Filter(query.Col[bool]("active").Eq(true))
rows, err := query.Select[User](db).
  With("active_users", active).
  From("active_users").
  Filter(query.Col[int64]("age").Gt(18)).
  All(ctx)

WithRecursive(name, sub) builds a recursive CTE — the recursive arm refers back to the CTE name (via a raw Join), and the two arms are combined with UnionAll. This is how you walk a tree or graph in one round trip:

anchor := query.Select[Category](db).Where("id = ?", rootID)
recurse := query.Select[Category](db).Join("JOIN subtree ON categories.parent_id = subtree.id")
subtree, err := query.Select[Category](db).
  WithRecursive("subtree", anchor.UnionAll(recurse)).
  From("subtree").
  All(ctx) // the root and all its descendants

CTEs are gated by FeatCTE — every backend supports them — and a CTE body may have a different row type than the enclosing query, so its placeholders renumber into the outer statement automatically.

Window functions

Window functions are projection expressions: you select them into a result struct with Into. A window function is built from a function, an Over(...) spec (PartitionBy + OrderBy), and a result alias:

type Ranked struct {
  Region string
  Amount int64
  Rank   int64 `db:"rank"`
}
ranked, err := query.Into[Sale, Ranked](ctx,
  query.Select[Sale](db),
  query.Col[string]("region").Field(),
  query.Col[int64]("amount").Field(),
  query.RowNumber().Over(
    query.Over().
      PartitionBy(query.Col[string]("region").Field()).
      OrderBy(query.Desc(query.Col[int64]("amount"))),
    "rank"))

The functions: ranking RowNumber() / Rank() / DenseRank(), offset Lag(col, n) / Lead(col, n), and running aggregates WindowSum / WindowAvg / WindowCount / WindowMin / WindowMax(col). Each is finished with .Over(window, alias), and the window is query.Over().PartitionBy(...Field).OrderBy(...term). Window functions need a modern engine (SQLite 3.25+, Postgres, MySQL 8+, SQL Server).

Scalar subqueries

ScalarSubquery(alias, sub) puts a subquery in the SELECT list as a single per-row value — the typed answer to a computed column beyond IN/EXISTS. The subquery must select one column and yield at most one row; correlate it with a raw Where referencing the outer table, and its bind parameters renumber into the outer statement automatically:

type WithCount struct {
  Name       string
  OpenOrders int64 `db:"open_orders"`
}
openByUser := query.Select[Order](db).Project("count(*)").
  Where("orders.user_id = users.id AND orders.status = ?", "open")
rows, err := query.Into[User, WithCount](ctx,
  query.Select[User](db),
  query.Col[string]("name").Field(),
  query.ScalarSubquery("open_orders", openByUser))

Set operations

Combine two compatible selects (same column shape). Union removes duplicate rows; UnionAll keeps them. The receiver’s ORDER BY / LIMIT apply to the whole compound:

cheapElectronics := query.Select[Product](db).Filter(query.And(
  query.Col[string]("category").Eq("electronics"),
  query.Col[float64]("price").Lt(50),
))
allBooks := query.Select[Product](db).Filter(query.Col[string]("category").Eq("books"))

combined, err := cheapElectronics.Union(allBooks).OrderBy("name").All(ctx)

Intersect (rows in both) and Except (rows in the first but not the second) round out the operators, each with an …All variant that keeps duplicates:

both, _ := a.Intersect(b).All(ctx)  // + IntersectAll
only, _ := a.Except(b).All(ctx)     // + ExceptAll

INTERSECT / EXCEPT are supported on SQLite, Postgres, and SQL Server; on MySQL they raise a clear build error (MySQL only added them in 8.0.31, so LiteORM doesn’t advertise them there). A row lock can’t be set on a compound arm — apply it to the whole compound.

DISTINCT ON (Postgres)

Distinct() adds plain SELECT DISTINCT. On Postgres, DistinctOn(cols...) keeps the first row of each distinct combination of the given typed columns — pair it with an Order whose leading terms match to choose which row:

// the latest event per kind
latest, err := query.Select[Event](db).
  DistinctOn(query.Col[string]("kind").Field()).
  Order(query.Asc(query.Col[string]("kind")), query.Desc(query.Col[int64]("seq"))).
  All(ctx)

DistinctOn raises a clear build error on the dialects that don’t support it.

Row locking

On Postgres and MySQL, a SELECT can take row locks — take them inside a transaction. ForUpdate() takes exclusive locks, ForShare() shared ones; SkipLocked() skips already-locked rows instead of blocking, and NoWait() errors instead. SkipLocked/NoWait imply a lock, defaulting to FOR UPDATE.

tx, _ := db.Begin(ctx)
job, err := query.Select[Job](tx).
  Filter(query.Col[string]("status").Eq("queued")).
  OrderBy("id").Limit(1).
  ForUpdate().SkipLocked().      // the classic work-queue claim
  First(ctx)
// … process job, update status, tx.Commit(ctx)

Locking is gated by dialect: SQLite (no row locks) and SQL Server (which uses table hints instead) raise a clear build error rather than emit SQL that wouldn’t mean what you intended. For the optimistic (version-column) alternative and retry-on-conflict, see concurrency & locking.

When the builder stops

For SQL beyond even these — a construct the typed API doesn’t model — query.Raw[T] runs your hand-written statement and scans the rows into a struct. See raw SQL.