Aggregates and subqueries

This guide is about the two ways Ecto answers questions about sets of rows rather than individual ones: aggregates (how many posts? what is the average visit count?) and subqueries (questions whose answer depends on the result of another query). Both translate to lode: aggregates via repo.count and repo.aggregate, and subqueries as a FROM source (from_subquery), as WHERE x IN (subquery), as WHERE EXISTS (...) with correlated references (expr.outer), and as a JOIN source (query.join_subquery). This guide is rigorous about which is which.

The running example is a posts table that records how many times each post has been visited. Just enough schema to query:

import lode/schema.{type Schema}
import lode/types/primitive
import lode/value.{VInt, VString}
import gleam/dict
import gleam/result

pub type Post {
  Post(id: Int, title: String, visits: Int)
}

pub fn post_schema() -> Schema(Post) {
  schema.new(
    source: "posts",
    fields: [
      schema.primary_key(name: "id", of: primitive.id()),
      schema.field(name: "title", of: primitive.string()),
      schema.field(name: "visits", of: primitive.integer()),
    ],
    load: fn(row) {
      use id <- result.try(schema.require_int(row, "id"))
      use title <- result.try(schema.require_string(row, "title"))
      use visits <- result.try(schema.require_int(row, "visits"))
      Ok(Post(id:, title:, visits:))
    },
    dump: fn(p: Post) {
      dict.from_list([
        #("id", VInt(p.id)),
        #("title", VString(p.title)),
        #("visits", VInt(p.visits)),
      ])
    },
  )
}

Queries reference columns through typed accessors. Ecto generates these from the schema’s macro; here they are plain functions (codegen can emit them — see Getting Started). We need one for visits:

import lode/query/expr.{type FieldRef, FieldRef}

fn post_visits() -> FieldRef(Post, Int) {
  FieldRef(binding: 0, name: "visits", lode_type: primitive.integer())
}

Aggregates

Ecto offers Repo.aggregate/3, a one-call helper: Repo.aggregate(Post, :count) for a row count and Repo.aggregate(from(p in Post, where: p.visits > 0), :avg, :visits) for an average. lode has both: repo.count for counts and repo.aggregate for the scalar aggregates. We’ll take them in turn.

Counting

For counting rows there is repo.count:

import lode/query
import lode/repo.{type Repo}
import lode/error.{type LodeError}

pub fn post_count(r: Repo) -> Result(Int, LodeError) {
  repo.count(repo: r, query: query.from(source: "posts", alias: "p"))
}

It composes with where, exactly as Ecto’s Repo.aggregate(query, :count) does — count only the posts with visits, say:

pub fn visited_post_count(r: Repo) -> Result(Int, LodeError) {
  repo.count(
    repo: r,
    query: query.from(source: "posts", alias: "p")
      |> query.where(expr.gt(field: post_visits(), to: 0)),
  )
}

repo.count pushes SELECT count(*) down to the database, so the server returns a single number rather than the whole result set. There is also repo.exists(repo:, query:) -> Result(Bool, LodeError) for the common “is there at least one?” question (Ecto’s Repo.exists?).

Note — counting with limit/group_by. A bare count(*) can’t honor a limit, offset, group_by, or distinct, and repo.count doesn’t wrap the query in a subquery automatically — when the query carries any of those it falls back to materializing the rows and counting them in Gleam (correct, but it transfers the result set). To count a bounded set in the database, wrap it yourself: repo.count(r, query.from_subquery(bounded, "t")). Plain filtered counts (the common case) take the cheap count(*) path directly.

Sum, average, min, and max

For the other aggregates there is repo.aggregate. You pass the aggregate expression — expr.sum(field), expr.avg(field), expr.min(field), expr.max(field), expr.count(field), or expr.count_all() — and get the scalar back as an Option(Value) (None when the aggregate is SQL NULL, e.g. sum/avg/min/max over zero rows):

import gleam/option.{type Option}
import lode/value.{type Value}

pub fn average_visits(r: Repo) -> Result(Option(Value), LodeError) {
  let q =
    query.from(source: "posts", alias: "p")
    |> query.where(expr.gt(field: post_visits(), to: 0))
  repo.aggregate(repo: r, query: q, by: expr.avg(post_visits()))
}

repo.aggregate renders the aggregate (avg(p."visits")) and reads the single scalar back for you — no result schema required. sum/min/max of an Int column come back as VInt; avg as a numeric (VFloat from the in-memory adapter, Postgres numeric). Like count, it does not wrap the query in a subquery, so any limit/offset/group_by on q is not honored.

When you’d rather have the result typed and named — because it’s read in several places — select the aliased aggregate into a small result schema and load it through repo.one:

pub type Avg {
  Avg(value: Float)
}

fn avg_schema() -> Schema(Avg) {
  schema.new(
    source: "posts",
    fields: [schema.field(name: "avg", of: primitive.float_type())],
    load: fn(row) {
      // `avg(int)` comes back as a numeric; read it as a float column.
      case schema.get(row, "avg") {
        Ok(value.VFloat(f)) -> Ok(Avg(value: f))
        // Empty table: avg of no rows is NULL.
        _ -> Ok(Avg(value: 0.0))
      }
    },
    dump: fn(_a: Avg) { dict.new() },
  )
}

pub fn average_visits_typed(r: Repo) -> Result(Option(Avg), LodeError) {
  let q =
    query.from(source: "posts", alias: "p")
    |> query.where(expr.gt(field: post_visits(), to: 0))
    |> query.select([expr.as_(expr.avg(post_visits()), "avg")])
  repo.one(repo: r, schema: avg_schema(), query: q)
}

expr.as_(expr.avg(post_visits()), "avg") renders avg(p."visits") AS "avg", and the loader reads the column by that name (and can defend against the NULL of an empty set). For one-off scalars, or SQL the typed builder can’t render at all (see the subqueries section), repo.query_raw with a hand-written SELECT avg(...) works too — read the value out of the returned row by column name.

Grouped aggregates

Aggregates earn their keep grouped. Ecto writes from(p in Post, group_by: p.published, select: {p.published, count(p.id)}); here query.group_by(by:) and a multi-expression select do the same. Count posts per visit count, say — group by visits, select the group key alongside count(*):

query.from(source: "posts", alias: "p")
|> query.group_by(by: [expr.col(post_visits())])
|> query.select([expr.col(post_visits()), expr.count_all()])

That renders SELECT p."visits", count(*) FROM "posts" AS p GROUP BY p."visits". To read it back, use the same tiny-schema trick with two fields (the group key and the count, aliased so they have stable names), or repo.query_raw. And query.having(condition:) filters the groups — keep only visit counts shared by more than one post:

query.from(source: "posts", alias: "p")
|> query.group_by(by: [expr.col(post_visits())])
|> query.having(expr.gt(field: post_visits(), to: 0))
|> query.select([expr.col(post_visits()), expr.count_all()])

having takes a boolean Expr exactly like where. (Filtering on the aggregate itself — HAVING count(*) > 1 — isn’t expressible with the typed comparison combinators, which compare a FieldRef against a literal; write that condition with expr.fragment("count(*) > ?", [...]) or in raw SQL.)

Subqueries

A subquery answers a question whose input is the result of another query. lode supports the two most common shapes directly in the typed builder: a subquery as a FROM source, and WHERE x IN (subquery).

Aggregating over a bounded set (from_subquery)

Suppose you want the average visit count over only the two most-visited posts. The naive query is wrong:

SELECT avg(visits) FROM posts ORDER BY visits DESC LIMIT 2

SQL applies avg across the whole table, and LIMIT then truncates the single-row aggregate result — aggregates ignore LIMIT. The fix is to limit first in a subquery, then aggregate over that bounded set. In Ecto:

top = from(p in Post, order_by: [desc: p.visits], limit: 2)
Repo.aggregate(subquery(top), :avg, :visits)

In lode, query.from_subquery makes the inner query the source, and repo.aggregate runs over it:

pub fn avg_visits_of_top_2(r: Repo) -> Result(Option(Value), LodeError) {
  let top =
    query.from(source: "posts", alias: "p")
    |> query.order_by([query.desc(expr.col(post_visits()))])
    |> query.limit(to: 2)
  repo.aggregate(
    repo: r,
    query: query.from_subquery(top, "t"),
    by: expr.avg(post_visits()),
  )
}

This renders SELECT avg(t."visits") FROM (SELECT * FROM "posts" AS p ORDER BY p."visits" DESC LIMIT 2) AS t. The same shape wraps a distinct-ed or paginated set: build the inner query, from_subquery it, then select or aggregate over the result. The inner query’s $n parameters are threaded into the outer query’s parameter sequence in textual order, so this stays fully typed and parameterized.

Membership (WHERE x IN (subquery))

query.where_in_subquery (and where_not_in_subquery) test a field against the single column a subquery selects — Ecto’s where: x in subquery(q). The field and the subquery’s selected column are ordinary typed accessors (expr.col(...), like post_visits() above):

// posts written by an author who is currently active
let active_author_ids =
  query.from(source: "users", alias: "u")
  |> query.select([expr.col(user_id())])
  |> query.where(expr.eq(user_active(), True))

query.from(source: "posts", alias: "p")
|> query.where_in_subquery(expr.col(post_author_id()), active_author_ids)

This renders ... WHERE p."author_id" IN (SELECT u."id" FROM "users" AS u WHERE (u."active" = $1)), again threading the subquery’s parameters into the outer $n sequence.

Correlated / EXISTS subqueries

query.where_exists(q, subquery) / where_not_exists render WHERE EXISTS (...) / NOT EXISTS (...), and a subquery references the enclosing query’s columns with expr.outer(alias, name) (Ecto’s parent_as):

let has_comment =
  query.from("comments", "c")
  |> query.where(expr.BinOp(
    expr.Eq, expr.Col(0, "post_id"), expr.outer("p", "id")))
query.from("posts", "p") |> query.where_exists(has_comment)
// SELECT * FROM "posts" AS p WHERE EXISTS (
//   SELECT * FROM "comments" AS c WHERE (c."post_id" = "p"."id"))

The outer alias renders verbatim (the subquery is lexically nested, so it’s in scope), and the subquery’s $n parameters thread into the outer statement. A subquery can also be a JOIN source — query.join_subquery(q, kind, subquery, alias, on) renders <kind> JOIN (<subquery>) AS alias ON <on>. #correlated-subqueries

Window functions

expr.over(call, partition_by:, order_by:) projects a window application in select (Ecto’s over/2) — the callee is a ranking function (expr.row_number(), expr.rank(), expr.dense_rank()) or any aggregate expression, and the window’s own ordering is built with expr.win_asc/expr.win_desc:

query.from("scores", "s")
|> query.select([
  expr.col(team()),
  expr.as_(
    expr.over(
      expr.row_number(),
      partition_by: [expr.col(team())],
      order_by: [expr.win_desc(expr.col(points()))],
    ),
    "rn",
  ),
])
// SELECT s."team", row_number() OVER (
//   PARTITION BY s."team" ORDER BY s."points" DESC) AS "rn"
// FROM "scores" AS s

To reuse one window across several projections, declare it by name (Ecto’s windows:): query.window(q, "w", partition_by:, order_by:) renders a standard WINDOW w AS (...) clause between HAVING and ORDER BY, and expr.over_named(expr.sum(points()), "w") projects sum(s."points") OVER w. Both dialects render identical standard SQL — SQLite has had window functions since 3.25, and the bundled amalgamation is 3.50.x. Frame clauses (ROWS BETWEEN ...) are not modeled — drop to repo.query_raw for frames — and window expressions are SQL-only: the in-memory adapter does not evaluate them. #window-functions

Common table expressions

query.with_cte(q, name:, as_:, recursive:) attaches a named CTE (Ecto’s with_cte/3 — the label is as_ because as is a Gleam keyword), rendered as a single WITH "name" AS (...) prefix before the statement. Reference the CTE by name as an ordinary from/join source, and note the parameter order: the CTE’s parameters render first, so the outer query’s own literals take the later $n numbers:

let adults =
  query.from("users", "u") |> query.where(expr.gt(user_age(), 18))
query.from("adults", "a")
|> query.with_cte(name: "adults", as_: adults, recursive: False)
|> query.where(expr.lt(adult_id(), 100))
// WITH "adults" AS (SELECT * FROM "users" AS u WHERE (u."age" > $1))
// SELECT * FROM "adults" AS a WHERE (a."id" < $2)

A recursive CTE’s body is base |> query.union_all(step), with the step referencing the CTE’s own name as an ordinary join source (the recursion):

let base =
  query.from("categories", "c") |> query.where(expr.is_nil(cat_parent()))
let step =
  query.from("categories", "c")
  |> query.join(query.InnerJoin, "tree", "t",
    expr.eq_col(cat_parent(), tree_id()))
  |> query.select([Col(0, "id"), Col(0, "parent_id"), Col(0, "name")])
query.from("tree", "t")
|> query.with_cte(name: "tree", as_: base |> query.union_all(step),
  recursive: True)
// WITH RECURSIVE "tree" AS (
//   SELECT * FROM "categories" AS c WHERE (c."parent_id" IS NULL)
//   UNION ALL
//   SELECT c."id", c."parent_id", c."name" FROM "categories" AS c
//     INNER JOIN "tree" AS t ON (c."parent_id" = t."id"))
// SELECT * FROM "tree" AS t

Inside a CTE body, union operands render without parentheses — SQLite rejects parenthesized compound operands, and the recursive form requires the bare base UNION ALL step shape — so a CTE body’s union operands must not carry their own order_by/limit. Plain and recursive CTEs work on both engines (SQLite has had CTEs since 3.8.3). CTEs are SELECT-only (update_all/delete_all ignore them), column aliases (WITH name (cols) AS ...) are not modeled — name the columns with an explicit select in the body — and the in-memory adapter does not evaluate CTEs; drop to repo.query_raw for the unmodeled shapes. #query-cte

Closing remarks

Aggregates are usable today: counts via repo.count (mind the materialization caveat for limit/group_by), and sum/avg/min/max/grouped aggregates via repo.aggregate or aggregate expressions in select. Subqueries are covered in the typed builder in every position — a FROM source, WHERE x IN (...), correlated EXISTS (via expr.outer), and a JOIN source. Window functions are covered too — expr.over, or query.window + expr.over_named (see Window functions above). CTEs are covered as well — query.with_cte for plain and recursive WITH (see Common table expressions above); window frames are the remaining raw-SQL territory: use repo.query_raw/query_raw_as for those. #window-functions #query-cte

For building filter and ordering logic at runtime, see Dynamic queries. For the full catalogue of what differs from Elixir’s Ecto, see Divergences from Ecto.