Subqueries, sets, and CTEs

A query can sit inside another one as a list of values, a single value, or a table. Global filters and soft deletes still apply inside it.

A list of values

ant.SubOffunc SubOf[V any](q Selectable, field *V) Sub[V]SubOf makes q a subquery selecting field. turns a query and a column into something InSub can use.

authors := ant.SubOf(db.Posts.Where(ant.Eq(&P.Published, true)), &P.UserID)

wrote, err := db.Users.Where(ant.InSub(&U.ID, authors)).All(ctx)
quiet, err := db.Users.Where(ant.NotInSub(&U.ID, authors)).All(ctx)

Exists

A subquery can refer to the outer row with EqCol.

theirs := db.Posts.Where(ant.EqCol(&P.UserID, &U.ID))

wrote, err := db.Users.Where(ant.Exists(theirs)).All(ctx)
quiet, err := db.Users.Where(ant.NotExists(theirs)).All(ctx)

For a relation, ant.Has says the same thing more briefly.

A single value

ant.SubExprfunc SubExpr[V any](q Selectable, value Expr[V]) Sub[V]SubExpr makes q a subquery selecting an expression. runs a query for a single value, which you can use anywhere an expression goes.

oldest := ant.SubExpr(db.Users.Where(), ant.Max(&U.Age)).Expr()

users, err := db.Users.Where(ant.Col(&U.Age).EqExpr(oldest)).All(ctx)

It works for ordering, and for updates too.

posts := ant.SubExpr(db.Posts.Where(ant.EqCol(&P.UserID, &U.ID)), ant.Count()).Expr()

busiest, err := db.Users.Order(posts.Desc()).All(ctx)

From a subquery

adults := db.Users.Where(ant.Gt(&U.Age, 18))

users, err := db.Users.Query().From(adults).Where(ant.Lt(&U.Age, 35)).All(ctx)

Includefunc (t *Table[T]) Include[R any](rel *[]R, shape ...func(*Query[R]) *Query[R]) *Query[T]Include starts a query that loads a one-to-many relation. can't be part of a subquery, and returns ant.ErrIncludeInSubqueryvar ErrIncludeInSubquery = errors.New("ant: subquery cannot use Include")ErrIncludeInSubquery reports a query with Include used as a subquery..

Union, intersect, except

These combine queries of one model. Conditions and ordering added afterward apply to the combined rows, so the result is a normal query.

young := db.Users.Where(ant.Lt(&U.Age, 35))
old := db.Users.Where(ant.Gt(&U.Age, 25))

either, err := young.Union(old).OrderBy(&U.Name).All(ctx)
repeats, err := young.UnionAll(old).All(ctx)
both, err := young.Intersect(old).All(ctx)
onlyYoung, err := young.Except(old).All(ctx)

Common table expressions

FromCTEfunc (q *Query[T]) FromCTE(name string, sub *Query[T]) *Query[T]FromCTE names sub in a WITH clause, and reads from it. names a subquery in a WITH clause.

named := db.Users.Where(ant.Like(&U.Name, "A%"))

users, err := db.Users.Query().FromCTE("named", named).OrderBy(&U.Name).All(ctx)

Recursion

Recursivefunc (q *Query[T]) Recursive(rel *[]T) *Query[T]Recursive extends q with rows reached by repeatedly following a one-to-many relation of the same model, such as a folder and everything under it. follows a model's relation to itself as far as it goes, and RecursiveOnefunc (q *Query[T]) RecursiveOne(rel **T) *Query[T]RecursiveOne extends q with rows reached by repeatedly following a many-to-one relation of the same model, such as a folder and all its ancestors. walks up instead. A loop in the data doesn't hang it, because rows it has already seen are dropped.

type Folder struct {
	ID       int64
	ParentID *int64
	Name     string
	Parent   *Folder
	Children []Folder
}

var F = ant.Fields[Folder]()

tree, err := db.Folders.Where(ant.Eq(&F.ID, id)).
	Recursive(&F.Children).
	OrderBy(&F.Name).
	All(ctx)

ancestors, err := db.Folders.Where(ant.Eq(&F.ID, id)).
	RecursiveOne(&F.Parent).
	All(ctx)

Rows held in memory

ant.Valuesfunc Values[T any](h Handle, rows ...T) *Query[T]Values reads rows held in memory as if they were a table, for joining against a list or using it in a subquery. treats rows in your program as a table, so you can join against a list. It needs a dialect with migration support, which means PostgreSQL for now, and an empty list is ant.ErrNoRowsvar ErrNoRows = errors.New("ant: no rows given")ErrNoRows reports an operation given no rows to work on..

type Wanted struct {
	UserID int64
	Label  string
}

var W = ant.Fields[Wanted]()

wanted := ant.Values(db, Wanted{alice.ID, "first"}, Wanted{bob.ID, "second"})

rows, err := ant.JoinSub(db.Users.Query(), wanted, ant.EqCol(&U.ID, &W.UserID)).
	Select(func(u *User, w *Wanted) string { return w.Label + ": " + u.Name }).
	All(ctx)

Table functions

ant.FromFuncfunc FromFunc[T any](h Handle, call string, args ...any) *Query[T]FromFunc reads from a table-valued function, written with ? for its arguments, as in generate_series(?, ?). reads a function that returns rows, and queries build on it.

type Number struct {
	N int64
}

var N = ant.Fields[Number]()

numbers, err := ant.
	FromFunc[Number](db, "generate_series(?::bigint, ?::bigint) AS numbers(n)", 1, 5).
	Where(ant.Gt(&N.N, 3)).
	All(ctx)

See it tested: tests/features/querying