Subqueries and CTEs

This page covers nested queries with IN (SELECT ...), scalar correlated subqueries projected as columns (Subquery / Exists), Common Table Expressions (WITH), CTE join types, deep join paths, and mixing CTEs with custom joins.

Subqueries in Filters

Pass a query object to @in to create a server-side IN (SELECT ...) predicate:

# Build the subquery
subquery = M.Status.objects
subquery.filter("status" => "Engine")
subquery.values("statusid")

# Use it in the main query
query = M.Result.objects
query.filter("statusid__@in" => subquery)
query.values("resultid", "statusid", "statusid__status", "grid", "driverid")
df = query |> DataFrame

This generates:

SELECT ... FROM "result" as "Tb" ...
WHERE "Tb"."statusid" IN (SELECT "Tb"."statusid" FROM "status" as "Tb" WHERE "Tb"."status" = $1)

Subquery Column Constraint

An @in subquery must project exactly one column. PormG validates this before generating SQL and throws a FilterError if the subquery selects more than one field.

# Wrong — two columns will throw FilterError
bad_sub = M.Status.objects.values("statusid", "status")
query.filter("statusid__@in" => bad_sub)
# ERROR: 'statusid__@in' requires a subquery that returns exactly one column
#        (currently selects 2 columns: statusid, status)
#        call .values("field_name") on the subquery to narrow it to one column.

# Correct — exactly one column
good_sub = M.Status.objects.filter("status" => "Engine").values("statusid")
query.filter("statusid__@in" => good_sub)

SQL Function Projections

You can use a SQL function alias as the single column. PormG counts a "alias" => Function(...) pair as one column, so the validator accepts it:

using PormG.Functions: Max

# Subquery that returns the single highest driver id
top_id_sub = M.Driver.objects.values("top_id" => Max("driverid"))

# Returns only the driver whose id equals the maximum id
query = M.Driver.objects.filter("driverid__@in" => top_id_sub)
df = query |> DataFrame

Generated SQL:

SELECT * FROM "drivers" AS "Tb"
WHERE "Tb"."driverid" IN (
    SELECT MAX("R1"."driverid") AS top_id FROM "drivers" AS "R1"
)

A Subquery Can Declare Its Own CTE

A subquery consumed by @in / @nin, Subquery(...) or Exists(...) can declare a CTE of its own. The WITH renders inside the subquery's parentheses, and its values bind where their text sits, on every backend:

fast_laps = M.Lap_times.objects
fast_laps.filter("milliseconds__@lt" => 75_000)      # sub-75-second laps
fast_laps.values("raceid", "milliseconds")

inner = M.Result.objects
inner.with("fast" => fast_laps, join_field = "raceid" => "raceid")   # ← CTE inside the subquery
inner.filter("fast__milliseconds__@isnull" => false)                 # a race that had one
inner.values("driverid")

query = M.Driver.objects
query.filter("nationality" => "Brazilian", "driverid__@in" => inner)
query.values("driverid", "surname")

Generated SQL (SQLite), bound ["Brazilian", 75000]:

SELECT "Tb"."driverid" AS "driverid", "Tb"."surname" AS "surname"
FROM "driver" AS "Tb"
WHERE "Tb"."nationality" = ? AND
   "Tb"."driverid" IN (WITH "fast" AS (
      SELECT "R1"."raceid" AS "raceid", "R1"."milliseconds" AS "milliseconds"
      FROM "lap_times" AS "R1"
      WHERE "R1"."milliseconds" < ?
   )
   SELECT "R2"."driverid" AS "driverid"
   FROM "result" AS "R2"
   LEFT JOIN "fast" AS "R2_1" ON "R2"."raceid" = "R2_1"."raceid"
   WHERE "R2_1"."milliseconds" IS NOT NULL)

An Exists(...) subquery works the same way, in a filter or projected as a column (correlated with OuterRef, below). The WITH opens the EXISTS (…) body:

fast_start = M.Result.objects
fast_start.with("fast" => fast_laps, join_field = "raceid" => "raceid")
fast_start.filter("driverid" => OuterRef("driverid"),
                  "fast__milliseconds__@isnull" => false)

query = M.Driver.objects
query.filter("nationality" => "Brazilian")
query.values("driverid", "surname", "has_fast" => Exists(fast_start))

Generated SQL (SQLite), bound [75000, "Brazilian"]:

SELECT "Tb"."driverid" AS "driverid", "Tb"."surname" AS "surname",
  EXISTS (WITH "fast" AS (
      SELECT "R1"."raceid" AS "raceid", "R1"."milliseconds" AS "milliseconds"
      FROM "lap_times" AS "R1"
      WHERE "R1"."milliseconds" < ?
   )
   SELECT 1
   FROM "result" AS "R2"
   LEFT JOIN "fast" AS "R2_1" ON "R2"."raceid" = "R2_1"."raceid"
   WHERE "R2"."driverid" = "Tb"."driverid" AND "R2_1"."milliseconds" IS NOT NULL
   LIMIT 1) AS "has_fast"
FROM "driver" AS "Tb"
WHERE "Tb"."nationality" = ?

Likewise, a CTE can only be referenced by a statement that actually emits a WITH clause. Reads (list, first, count, exists) do; update(...) does not, so referencing a CTE from it raises a QueryBuildError rather than building SQL against a relation the statement never declares. bulk_update(...) does not emit one either, and refuses a handler that declares a CTE up front with an UnsafeMutationError — the type delete() uses below, because the refusal is about the statement being a mutation.

delete() refuses a CTE-scoped queryset with an UnsafeMutationError: the DELETE it emits declares no WITH of its own, so the CTE would never be declared for the rows it removes. Resolve the CTE first and filter on its result:

# ✗ refused
query = M.Result.objects
query.with("fast" => fast_laps, join_field = "raceid" => "raceid")
query.filter("fast__milliseconds__@isnull" => false)   # a race with a sub-75-second lap
query.delete()

# ✓ filter on the ids instead
fast_race_ids = M.Lap_times.objects
fast_race_ids.filter("milliseconds__@lt" => 75_000)
fast_race_ids.values("raceid")

query = M.Result.objects
query.filter("raceid__@in" => fast_race_ids)
query.delete()

Subqueries with Additional Filters

Combine subquery @in with other filter conditions:

subquery = M.Status.objects
subquery.filter("status" => "Engine")
subquery.values("statusid")

query = M.Result.objects
query.filter("statusid__@in" => subquery, "driverid__@lte" => 7)
query.values(
    "resultid",
    "statusid",
    "statusid__status",
    "grid",
    "driverid",
    "raceid__date__@quarter"
)
query.order_by("raceid__date__quarter")
df = query |> DataFrame

Scalar correlated subqueries

A scalar correlated subquery computes one value per outer row — "each driver's standings count", "each driver's latest position" — inside its own sub-SELECT, correlated to the outer query with OuterRef. Project it as a column with "alias" => Subquery(inner) inside values():

# Inner query: correlated via OuterRef, projects exactly ONE column
standings = M.Driver_standings.objects
standings.filter("driverid" => OuterRef("driverid"))
standings.values("t" => Count("driverstandingsid"))

query = M.Driver.objects
query.values(
    "surname",
    "total_standings" => Subquery(standings),   # scalar subquery column
    "has_standings"   => Exists(standings),     # boolean subquery column
)
df = query |> DataFrame

Generated SQL (PostgreSQL; SQLite renders ? placeholders, and MySQL ? placeholders and backtick-quoted names):

SELECT
    "Tb"."surname" as "surname",
  (SELECT
    COUNT("R1"."driverstandingsid") as "t"
FROM "driver_standings" as "R1"
WHERE "R1"."driverid" = "Tb"."driverid"
) as "total_standings",
  EXISTS (SELECT 1
FROM "driver_standings" as "R2"
WHERE "R2"."driverid" = "Tb"."driverid"
LIMIT 1) as "has_standings"
FROM "driver" as "Tb"

OuterRef("driverid") binds the inner query's filter to the outer query's column — that is the correlation. Exists(...) (already familiar from filters) can likewise be projected as a per-row boolean column, which reads back as a Bool on every engine.

Why: the fan-out-safe aggregate

This is the supported fix the aggregate fan-out guard points to. A to-many join repeats each outer row once per related row, so a naive join aggregate over a base column is silently multiplied — PormG raises instead. Moving the aggregate into a correlated Subquery dissolves the fan-out entirely: the aggregate runs in its own scalar subquery and the outer rows are never row-multiplied.

It also composes — multiple independent aggregates in one query, something a join-based rewrite cannot do without multiplying the counts against each other:

standings = M.Driver_standings.objects
standings.filter("driverid" => OuterRef("driverid"))
standings.values("t" => Count("driverstandingsid"))

results = M.Result.objects
results.filter("driverid" => OuterRef("driverid"))
results.values("t" => Count("resultid"))

query = M.Driver.objects
query.values(
    "surname",
    "n_standings" => Subquery(standings),   # each count stays exact —
    "n_results"   => Subquery(results),     # no interaction between the two relations
)
df = query |> DataFrame

Latest-value scalars (non-aggregate)

The inner projection does not have to be an aggregate — any one-column query works. Use the inner query's own order_by + limit(1) for the "latest related value per row" pattern:

# Each driver's most recent standings position
latest = M.Driver_standings.objects
latest.filter("driverid" => OuterRef("driverid"))
latest.values("position")
latest.order_by("-driverstandingsid")
latest.limit(1)

query = M.Driver.objects
query.values("surname", "latest_position" => Subquery(latest))
df = query |> DataFrame

For an outer row with no related rows, an aggregate scalar returns its natural value (Count → 0), while a plain-column scalar is SQL NULL → missing in the DataFrame.

A subquery as a function argument

A Subquery is one value per outer row, so it can be the argument of any scalar function. That covers the multi-argument ones (Coalesce, Greatest, Least, NullIf, Power), the one-argument ones (Lower, Upper, Trim, LTrim, RTrim, Length, Abs, Round, Floor, Ceil, Sqrt, Exp, Ln), Cast, Extract and ToChar, and the window value functions (Lag, Lead, FirstValue, LastValue, NthValue). To turn the missing above into a default, wrap the subquery:

using PormG.Functions: Coalesce

latest = M.Driver_standings.objects
latest.filter("driverid" => OuterRef("driverid"))
latest.values("position")
latest.order_by("-driverstandingsid")
latest.limit(1)

query = M.Driver.objects
query.values("surname", "latest_position" => Coalesce(Subquery(latest), 0))
df = query |> DataFrame

The one-argument functions take it the same way. Each driver's points in the standings, averaged over every race they appear in and rounded to one decimal place:

using PormG.Functions: Avg, Round

avg_points = M.Driver_standings.objects
avg_points.filter("driverid" => OuterRef("driverid"))
avg_points.values("t" => Avg("points"))

query = M.Driver.objects
query.filter("driverid__@lte" => 3)
query.values("surname", "avg_points" => Round(Subquery(avg_points), 1))
df = query |> DataFrame

An aggregate cannot wrap a subquery: Max(Subquery(latest)) raises QueryBuildError when it is built. The subquery is already one value per row, so put the aggregate inside it, as Why: the fan-out-safe aggregate above shows.

A subquery reads back as its one column does, on every engine (#888). When that column is one of a model column's own values (the column itself, or Max/Min over it), the result has the column's Julia type: a DateField gives a Date, where SQLite alone would hand back the stored text. Each driver's most recent race date:

using PormG.Functions: Max

last_race = M.Driver_standings.objects
last_race.filter("driverid" => OuterRef("driverid"))
last_race.values("t" => Max("raceid__date"))

query = M.Driver.objects
query.filter("driverid__@lte" => 3)
query.values("surname", "last_race" => Subquery(last_race))
df = query |> DataFrame   # df.last_race holds Dates.Date values on every engine

A function over the subquery follows its usual rule with that type: Coalesce(Subquery(last_race), Date(1900, 1, 1)) is a Date too, and NullIf takes the subquery's type. A computed inner number over a float or a decimal (Avg, Sum, Round, arithmetic), and an Avg over integers, reads back as the type it means, the same on every engine: a Float64 or a Decimal (see The type of a computed number, #1121). Any other computed inner column comes back as the engine delivers it; wrap it in Cast(Subquery(…), "date") (or the type it holds) to name the type yourself.

A subquery as a filter value

A Subquery can also be the value a filter compares against, as in Django's filter(grid=Subquery(...)). The comparison renders as column = (SELECT …), evaluated once per outer row. Each race's fastest lap in 2009:

using PormG.Functions: Min

fastest = M.Lap_times.objects
fastest.filter("raceid" => OuterRef("raceid"))
fastest.values("m" => Min("milliseconds"))

query = M.Lap_times.objects
query.filter("raceid__year" => 2009, "milliseconds" => Subquery(fastest))
query.values("raceid__name", "driverid__surname", "lap", "milliseconds")
query.order_by("raceid__date")
df = query |> DataFrame   # 17 rows: one per race (Rosberg's lap 48 in Australia, …)

SELECT "Tb_1"."name" as "raceid__name", "Tb_2"."surname" as "driverid__surname",
  "Tb"."lap" as "lap", "Tb"."milliseconds" as "milliseconds"
FROM "lap_times" as "Tb"
 INNER JOIN "race" AS "Tb_1" ON "Tb"."raceid" = "Tb_1"."raceid"
 INNER JOIN "driver" AS "Tb_2" ON "Tb"."driverid" = "Tb_2"."driverid"
WHERE "Tb_1"."year" = $1 AND
   "Tb"."milliseconds" = (SELECT MIN("R1"."milliseconds") as "m"
FROM "lap_times" as "R1"
WHERE "R1"."raceid" = "Tb"."raceid")
ORDER BY "Tb_1"."date" ASC NULLS LAST

The ordering lookups take it too. The 2009 podiums of drivers who started behind their race's average grid position:

using PormG.Functions: Avg

avg_grid = M.Result.objects
avg_grid.filter("raceid" => OuterRef("raceid"))
avg_grid.values("g" => Avg("grid"))

query = M.Result.objects
query.filter("raceid__year" => 2009, "grid__@gt" => Subquery(avg_grid), "positionorder__@lte" => 3)
query.values("raceid__name", "driverid__surname", "grid", "positionorder")
df = query |> DataFrame   # Trulli from grid 20 in Australia, Hamilton from grid 17 in Brazil

The same subquery works in every place a filter value goes:

• An F comparison: query.filter(F("milliseconds") == Subquery(fastest)) renders the same predicate as the pair, and so does a function or a Joined(...) handle on the left.
• Inside Q(...)/Qor(...), and in a When condition.
• A join condition: a pair in cjoin_on(...; on = [...]), on(...) or cjoin(...; filters = [...]). Its values bind in the join's place in the statement, so it is safe on SQLite's and MySQL's positional parameters too.
• An alias: values("best" => Max("grid")); filter("best__@gt" => Subquery(...)) compares in HAVING.

The rules are those of a projected scalar subquery (below), with two differences:

• The grouped-correlation check depends on the clause. A WHERE filter or a join condition is evaluated before GROUP BY, so it may correlate on a column the outer query does not group by — and so may one wrapped in a function there, such as filter("grid" => Coalesce(Subquery(…), 0)). A comparison on an aggregate alias (HAVING), or in a projection's When condition, is evaluated after grouping, and is checked by the #194 guard (below) the same as a projected subquery. The exceptions are a When inside an aggregate (Sum(Case([When(…)]))) and one inside a projection the query groups by whole. Both are evaluated before grouping, as the guard's notes below explain. Exists(…) follows the same rule.
• Lookups that need something other than one value refuse it. @in/@nin raise a FilterError that names the membership spelling, which takes the query itself, unwrapped: "grid__@in" => query. The pattern lookups (@contains, @startswith, …), @isnull and @range raise a FilterError too, as they do for a column expression.

Rules and limitations

• Exactly one column. The inner query must project exactly one column via .values(...) (the inner alias is cosmetic). Zero or several columns raise a QueryBuildError at build time. The same one-column rule applies to @in subqueries, where it surfaces as a FilterError — the type names which argument you got wrong (a projection here, a filter there). Catch PormGError to handle both.

• The alias is mandatory. Always project as a pair: "alias" => Subquery(...). A bare Subquery(...) inside values() raises.

• At most one row. The database requires a scalar subquery to return ≤ 1 row ("more than one row returned by a subquery used as an expression" at execution otherwise). An aggregate always satisfies this; for a plain column add order_by + limit(1) — PormG emits a build-time warning for the non-aggregate/no-limit case but does not block it.

• OuterRef binds the immediately enclosing query. A Subquery/Exists may sit inside another subquery in any position: a filter, a projection, a When, a join condition. Its OuterRef correlates to the subquery around it, never further out, which is Django's rule. Reaching two levels up (Django's OuterRef(OuterRef(…))) is not supported, and raises QueryBuildError. Each level renders under its own alias, so the binding is visible in the SQL (#938):

  # A driver's pole positions: the races where they started from that race's best grid.
  best_grid = M.Result.objects.
      filter("raceid" => OuterRef("raceid"), "grid__@gt" => 0).   # the race of the result row below
      values("g" => Min("grid"))
  poles = M.Result.objects.
      filter("driverid" => OuterRef("driverid"), "grid" => Subquery(best_grid)).   # the driver
      values("n" => Count("resultid"))
  query = M.Driver.objects.
      filter("surname" => "Senna").
      values("forename", "surname", "poles" => Subquery(poles))
  # … (SELECT MIN("R2"."grid") … WHERE "R2"."raceid" = "R1"."raceid" AND "R2"."grid" > $1) …
  #   … WHERE "R1"."driverid" = "Tb"."driverid" …
  df = query |> DataFrame   # Ayrton Senna 65, Bruno Senna 0

  The rule holds when both enclosing levels have the column, and it does not warn (#952). Swap the outer M.Driver above for M.Result (each Senna result, with its driver's pole count), and best_grid's OuterRef("raceid") still names the middle result's race, not the outer row's, although both have a raceid. This is Django's rule, and here it is also what the query means: the best grid of each race the counted result ran in. To use the outer row's value further in, pass it down a level. Correlate the middle query on it, and the inner reference then carries the same value:

  # Each of Ayrton Senna's results, flagged 1 when that race was one of his poles.
  pole_here = M.Result.objects.
      filter("driverid" => OuterRef("driverid"), "raceid" => OuterRef("raceid"),   # the outer row's race…
             "grid" => Subquery(best_grid)).      # …so best_grid's OuterRef("raceid") is that race too
      values("n" => Count("resultid"))
  query = M.Result.objects.
      filter("driverid__forename" => "Ayrton", "driverid__surname" => "Senna").
      values("raceid", "grid", "pole" => Subquery(pole_here))
  df = query |> DataFrame   # one row per result; sum(df.pole) == 65

• Correlate on base columns. OuterRef("driverid") against a plain outer column is the canonical, supported case. A joined-path OuterRef (e.g. OuterRef("constructorid__name")) adds a join to the query it binds and is not part of the validated #92 surface.

• OuterRef inside an expression. An outer column may be wrapped in a scalar function or a window column inside the correlated query — Lower(OuterRef("surname")), Cast(OuterRef("driverid"), "text"), Lag(OuterRef("driverid"), over = …) — and resolves against the outer row exactly as a bare OuterRef does. Outside a correlated build (a top-level values() or filter()) an OuterRef raises QueryBuildError, wrapped or not.

• Outer GROUP BY — guarded at build time (#194). When the outer query aggregates and a correlated Subquery/Exists that is evaluated after grouping — in the SELECT list, HAVING or ORDER BY — correlates on a column that is not in the group set, PormG raises a QueryBuildError naming the ungrouped column. The query does not say which row of the group to correlate against, so PormG refuses it on every engine, before any SQL runs. (Left to the database, PostgreSQL refuses it — subquery uses ungrouped column … from outer query — and so does MySQL, while SQLite evaluates the subquery against an arbitrary row of each group and returns a plausible-looking wrong number.) The legitimate shape is unaffected: group by driverid and correlate on OuterRef("driverid") and the query builds silently on every backend. Three notes on the edges: a whole-table aggregate (values("n" => Count(...), "s" => Subquery(...)), no GROUP BY at all) is refused too, because it is the same shape with an empty group set; a column that reaches the group set only through order_by counts as grouped, because it genuinely is; and a subquery evaluated before grouping is not guarded, because correlating it on an ungrouped column is legal on every engine. That covers three places (#932):

  • a WHERE filter or a join condition;
  • the argument of an aggregate, which is evaluated once per input row. Conditional aggregation is the usual case: values("constructorid", "poles" => Sum(Case([When("grid" => Subquery(…), then = 1)], default = 0))), where the subquery is the race's smallest positive grid, and aggregate(...) over the same Sum;
  • a projection or an order_by term that the query groups by whole, such as a Case with no aggregate in it, or Coalesce(Subquery(…), 0). The subquery is evaluated per input row to form the group key.

  A window function is not an aggregate here: it runs after grouping, so a subquery in its argument is checked.

  One deliberate strictness: PostgreSQL also accepts correlating on any column of a table whose primary key is grouped (every column is then functionally dependent on it), but PormG does not infer that dependency: it asks the query to name the column it groups by, on every engine — add the correlated column to values(...) if you hit it.

• A CTE and a subquery. A subquery sees only the CTEs it declares itself.

  • A Subquery, __@in/__@nin or Exists subquery may declare its own .with(...): the WITH renders inside its parentheses and binds in text order on every backend (#1292, #1523).

  • A subquery has its own CTE namespace, so it cannot reference a CTE its enclosing query declares — CTE("ev", "status") and "ev__status" inside it both raise an UnknownFieldError that says the CTE belongs to the enclosing query (#444). Put that condition in the enclosing query's filter(...).

  • A CTE declared on the outer query beside a projected subquery that does not reference it is ordinary and builds on every backend.

  • A CTE declared inside another CTE's body is also legal: it renders in the leading WITH, where its values bind in text order.

  • A CTE body cannot correlate to an enclosing query. Each body is built as a query of its own, so an OuterRef(...) in the body has nothing to resolve against, even when the CTE belongs to a correlated subquery. (A subquery inside the body still correlates to the body, as anywhere else.) It raises a QueryBuildError that names the CTE (#1557). When the CTE belongs to a correlated subquery, project the column from the CTE and correlate in the subquery that declares it:

    # ✗ refused: the OuterRef sits in the CTE body
    fast = M.Lap_times.objects
    fast.filter("milliseconds__@lt" => 75_000, "driverid" => OuterRef("driverid"))
    fast.values("raceid", "milliseconds")
    
    # ✓ the CTE projects driverid; the subquery that declares it correlates on it
    fast = M.Lap_times.objects
    fast.filter("milliseconds__@lt" => 75_000)
    fast.values("raceid", "driverid", "milliseconds")
    
    inner = M.Result.objects
    inner.with("fast" => fast, join_field = "raceid" => "raceid")
    inner.filter("driverid" => OuterRef("driverid"), "fast__driverid" => OuterRef("driverid"))
    
    query = M.Driver.objects
    query.filter(Exists(inner))
    query.values("driverid", "surname")   # drivers with a sub-75-second lap in a race they entered

CTE-aggregate vs scalar Subquery

Both are explicit, fan-out-safe ways to attach related aggregates. Pick by shape:

Performance. A correlated subquery is evaluated once per outer row (SQLite in particular never decorrelates it). For a filtered outer set — one driver, a season's entrants — it is ideal. For a full-table scan projecting several aggregates over large related tables, the CTE aggregate (computed once, joined once) usually wins; measure before choosing at scale.

Positioning: explicit, not magic

Django offers two paths for related-set aggregates: the implicit annotate(Count("relation")) — which silently row-multiplies when two annotations are combined (the documented "Cartesian product trap") — and the explicit Subquery() + OuterRef() its own docs recommend as the fix. PormG ships only the explicit path (with the same recognizable names) and goes one step further: the #74 guard turns the silent-fan-out form into a hard error. This is the same explicit-subquery camp as jOOQ (DSL.field(select …)) and SQLAlchemy (.scalar_subquery()), with correlation always spelled out via OuterRef — never inferred.

Common Table Expressions (CTEs)

CTEs (SQL WITH clauses) are useful when a query becomes easier to reason about in stages. PormG supports CTEs through the .with() method.

Why Use CTEs?

• Readability — Break complex queries into named stages.
• Aggregation — Pre-compute aggregates and join them back to the main query.
• Reuse — Reference the same subquery multiple times without duplication.

Basic CTE with JOIN

Define a subquery, give it a name, and join it to the main query via join_field:

using PormG.Functions: Count

# Define the CTE: count results per driver (where status = 1)
driver_stats = M.Result.objects
driver_stats.filter("statusid" => 1)
driver_stats.values("driverid", "total_results" => Count("resultid"))

# Main query: join the CTE to Result via driverid
main_query = M.Result.objects
main_query.with("stats" => driver_stats, join_field="driverid" => "driverid")

main_query.filter("resultid__@lte" => 100)
main_query.values("resultid", "driverid", "stats__total_results")
df = main_query |> DataFrame

The .with() method:

1. Emits the subquery as a WITH stats AS (SELECT ...) clause.
2. Creates a LEFT JOIN stats ON result.driverid = stats.driverid.
3. Makes the CTE's columns reachable as "stats__<column>" paths (or CTE("stats", ...), which is the same column).

The projected column is named stats__total_results — the CTE name and the column path joined by a double underscore — so that is what the DataFrame column is called.

# The CTE body renders `"race_name" AS "name"` — `name` is the column it exposes.
races_91 = M.Race.objects.filter("year" => 1991).values("raceid", "name")

q = M.Result.objects
q.with("r91" => races_91, join_field="raceid" => "raceid")
q.filter("r91__name" => "Brazilian Grand Prix")   # → the CTE's "name", not "race_name"
q.values("resultid", "race" => "r91__name")

Referencing a CTE's columns

A CTE's columns are reached with a "<cte>__<column>" path — the same __ dialect every other column reference in PormG speaks:

q.with("fast" => fast_laps, join_field = "raceid" => "raceid")

q.values("resultid", "best_lap" => "fast__milliseconds")
q.filter("fast__milliseconds__@lt" => 90_000)
q.order_by("-fast__milliseconds")

The part after the CTE's name is a path, not a bare column, and it carries the full __ vocabulary — a hop out of the CTE through a projected ForeignKey, a JSON sub-path, or an operator/transform suffix:

q.values("s" => "ev__parent__sku")                 # through a projected FK
q.filter("ev__meta__driver" => "senna")            # JSON sub-path
q.filter("ev__seen__@yyyy_mm__@lte" => "1991-10")  # operator suffix

An unaliased projection is named by joining the two halves with a double underscore, so values("tb_dup__dup_count") produces a column called tb_dup__dup_count.

SQL functions, aggregates and window clauses take the path wherever they take a field path:

q.values("l"   => Lower("ev__sku"),
         "c"   => Cast("ev__qty", "text"),
         "tot" => Sum("ev__qty"),
         "rk"  => Rank(over = WindowOver(partition_by = "ev__sku",
                                         order_by     = "-ev__seen")))

CTE(name, path) — the disambiguator

The same column, written as a reference object — the same kind as F, OuterRef and Subquery. The two spellings render byte-identical SQL and share one join, so a query may mix them freely:

q.values("x" => "ev__sku")           # ≡
q.values("x" => CTE("ev", "sku"))

Reach for the object in two places:

1. When the CTE's name also names something on the model. That is what it exists for — see the warning below.

2. On the right of a filter pair, where a bare string is a value, not a column. A correlation has to be written as a handle (or an F):

   q.filter("surname" => CTE("d91", "surname"))   # the CTE's column — a correlation
   q.filter("surname" => "d91__surname")          # a VALUE: the literal text "d91__surname"

   The second line is not an error on a text column — it compares surname against that literal and quietly matches nothing. (On a numeric column the field's type check catches it and raises InvalidValueError.) The handle is the only spelling that correlates, whatever the type.

Ordering direction needs no object: the ordinary - prefix works in both order_by(...) and a window's order_by, so there is one DESC dialect rather than two. desc = true is the object's equivalent, and is meaningful in exactly those two places; anywhere else it raises.

q.with("driverid" => driver_totals)      # "driverid" is ALSO a ForeignKey of Result
q.values("points", "driverid__surname")  # → AmbiguousFieldError

q.with("totals" => driver_totals)
q.values("points",
         "driverid__surname",                    # the ForeignKey
         "n" => "totals__total_results")         # the CTE — or CTE("totals", "total_results")

Where a CTE column may not appear. Inside on(...), cjoin(...) or cjoin_on(...) — a JOIN's ON clause targets the joined model, and a CTE is joined by its own .with() declaration. That holds however it is spelled: the CTE(...) handle, the "<cte>__<column>" string, and the handle as the operand of an F comparison (F("sku") == CTE("ev", "sku")) all raise FilterError. Put the predicate in .filter(...) instead. F itself accepts no CTE column at all, in any clause.

Correlating a CTE without join_field

When you omit join_field, the CTE is emitted but not keyed to the main table. Referencing one of its columns then CROSS JOINs the CTE, and the filter you write supplies the correlation verbatim in WHERE — the natural, Django-flavored way to express a correlated CTE:

# Races from the 1991 season
races_91 = M.Race.objects.filter("year" => 1991).values("raceid")

# Winners of those races — correlate Result.raceid with the CTE's raceid
q = M.Result.objects
q.with("r91" => races_91)                       # no join_field
q.filter("raceid" => CTE("r91", "raceid"),      # ← the correlation
         "positionorder" => 1)
q.values("resultid", "raceid")
df = q |> DataFrame

renders (SQLite shown; PostgreSQL uses $N placeholders, and MySQL backtick-quoted names):

WITH "r91" AS (SELECT "raceid" FROM "race" WHERE "year" = ?)
SELECT "R1"."resultid", "R1"."raceid"
FROM "result" AS "R1"
CROSS JOIN "r91" AS "R1_1"
WHERE "R1"."raceid" = "R1_1"."raceid" AND "R1"."positionorder" = ?

A CTE(...) on the right of a filter pair means "this column", exactly as F(...) does for a model column — so no F() wrapper is needed. The two compose where you want an operator: filter(F("date") >= CTE("r91", "start")). The handle sits on the left of an operator too, as F(...) does (#1529): filter(CTE("r91", "start") <= F("date")). F("r91__start") is not that spelling, because a __ path cannot reach a CTE from inside F(...) (#492), so the handle is the way to write a CTE column with an operator.

The handle takes arithmetic as F(...) does (#1539): + - * / with a number, a column, an F(...) expression, a function or another handle, on either side. Each driver's points behind their own best finish in a race:

best = M.Result.objects.values("driverid", "best" => Max("points"))

df = M.Result.objects.
    with("best" => best, join_field = "driverid" => "driverid", join_type = "INNER").
    values("resultid", "points", "behind_best" => CTE("best", "best") - F("points")).
    filter("raceid" => 18).
    order_by("resultid") |> DataFrame

A date, timestamp or duration column of a CTE is typed as one, so its arithmetic is what it is on the model column, on every engine: with start a date, CTE("r91", "start") + Day(7) shifts it and F("date") - CTE("r91", "start") is the day count, as is a date literal on the left, Date(1991, 3, 10) - CTE("r91", "start") (#1549). A duration column of a CTE takes a duration as an interval value, as a DurationField does (CTE("laps", "best") - Second(1), #1553). A duration added to a CTE column that is no date, timestamp or duration raises InvalidValueError, as F("grid") + Day(1) does. A CTE column over a JSON key takes no arithmetic until it is cast (Cast(CTE("ev", "n"), "integer") + 1), because a key's value has no declared type to compute on.

Notes:

• CROSS JOIN + WHERE is inner by nature — only rows the correlation matches are returned. To keep unmatched main-table rows (a nullable left join against a CTE), use an explicit join_field (+ join_type="LEFT") as shown above instead.
• Multiple correlations and inequality/range predicates work too, e.g. filter("date__@gte" => CTE("r91", "start"), "date__@lte" => CTE("r91", "end")).
• Cartesian-product guard. Referencing a CTE column with no constraining filter is a Cartesian product; PormG emits a @warn naming the CTE. Add a correlating filter(...), or pass join_field.
• A CTE name may equal a cjoin_on alias. An alias has its own namespace and is reached only through Joined(alias, column), never as a __ path, so nothing is ambiguous: both relations are emitted, the CTE under a generated alias, and each stays addressable (#474, #484). Before #474 that case was refused; before that again it silently returned wrong rows.
• A CTE name that equals anything reached by a __ path is different — a model field, a reverse accessor, a many-to-many field, or a cjoin/on() join path (those name model relations, so they collide just as a field does). The shared path then has two readings and raises AmbiguousFieldError; CTE(name, col) selects the CTE side (#492). A CTE name that collides with nothing changes no field path at all.
• A projection alias never shares a CTE column's name. A CTE column is spelled "<cte>__<col>", and values(...) refuses an alias containing __ with QueryBuildError (#757). So filter("ev__points" => 1) always names the CTE column. Alias the projection with a single underscore instead, such as "ev_points" => F("points") * 2 (#723).
• A CTE may not be named after a physical table. .with("driver" => ...) raises QueryBuildError at the call when driver is the db_table of any registered model, or a many-to-many join table (#479). This one is SQL's rule, not PormG's: a WITH name hides any real table of the same name for the whole statement (PostgreSQL, SQLite and MySQL alike, and SQLite additionally rejects a CTE body that reads its own name as a circular reference). PormG generates its joins unqualified from db_table, so such a CTE would silently redirect every generated join to that table. Pick a name that is not a table.
• join_type is validated at the call. .with(..., join_type = "CROSS") — or any string outside "LEFT" / "INNER" / "RIGHT" / "FULL" — raises QueryBuildError where you wrote it. A CTE join renders <join_type> JOIN … ON …, which a CROSS cannot be. For a deliberate cross product declare the CTE without join_field and reference it, as above (#44, #474).

CTE with Multiple Aggregated Fields

using PormG.Functions: Count, Sum

# CTE with multiple aggregates
stats = M.Result.objects
stats.filter("raceid__@lte" => 100)
stats.values(
    "driverid",
    "total_results"         => Count("resultid"),
    "total_grid_positions"  => Sum("grid")
)

# Join CTE to Driver model
query = M.Driver.objects
query.with("driver_stats" => stats, join_field="driverid" => "driverid")

query.filter("driverid__@lte" => 50)
query.values(
    "driverid",
    "forename",
    "surname",
    "driver_stats__total_results",
    "driver_stats__total_grid_positions",
)
df = query |> DataFrame

How a CTE Column Is Typed

The outer query treats each CTE column as a field, and that field's type decides how a value compared with the column is bound. PormG derives the type from what the body's projection means, the same on every engine, so a Period compared with a summed duration binds as a duration and a boolean compared with a comparison binds as a boolean (#1034):

A projection whose type PormG cannot name raises QueryBuildError when the query is built: F arithmetic on a value that is not a number (a text column plus 1, or a JSON key lookup's value), Value(missing), and a function or a Subquery over such a value. Give it an output_field or wrap it in Cast to name its type (Cast(Subquery(s), "integer") for a Subquery). Before #1460 every function but Count, Sum, Avg, Min, Max and Case raised there, whatever it returned.

using PormG.Functions: Upper

# The 2009 Australian GP (race 1), each driver's surname in capitals: `Upper` is text, so "BUTTON"
# binds as text with no declaration.
names = M.Result.objects
names.filter("raceid" => 1)
names.values("resultid", "upper_name" => Upper("driverid__surname"))

query = M.Result.objects
query.with("n" => names, join_field = "resultid" => "resultid")
query.filter("raceid" => 1, "n__upper_name" => "BUTTON")
query.values("resultid", "n__upper_name")
df = query |> DataFrame   # one row: "BUTTON"

This type is for binding a value compared with the column, and for a function over the column in the outer query, which treats it as a column of that type: Sum(CTE("r", "ahead")) over a comparison is refused as a Sum over a BooleanField is, and Extract(CTE("r", "fast"), "EPOCH") over a summed duration reads it as a duration (on PostgreSQL and MySQL; SQLite has no EPOCH part). Reading the column back is a separate question, and the answer is the body's own. A column the body selects, such as "date", Max("date") or Cast(x, "date"), reads back with the same Julia type the body would give it (a Date on every engine). A number the body computes, such as Avg("points") or F arithmetic, reads back as the type it means on every engine, a Float64 or a Decimal (#1121); the table above binds it as the same type, though an Avg of a DecimalField keeps that field's declared width there. This is the same rule values(...) follows outside a CTE (see The type of a computed number).

The type you declare can be a text, integer, bigint, float, numeric, boolean, date or blob type, and on PostgreSQL also uuid, inet or cidr, and (#1194) time, timestamp, timestamptz, interval or bytea. SQLite renders uuid, inet and cidr as text, so a CTE column declared as one is refused there, and it refuses the cast to a time, timestamp, interval or bytea type itself. That includes the aliases int2, int4, int8, float4 and float8, and any field object with one of those types, such as PositiveIntegerField(), which is typed as a plain integer because a cast does not enforce the sign. A single-precision float (real, float4) is refused on SQLite, which has no 4-byte float to round to (#1215, see Functions and Dates), and so is text of a declared length (varchar(n), char(n), CharField(max_length = n), SlugField, URLField), which SQLite does not cut (#1252, #1438). MySQL casts uuid to CHAR(36) text, so a CTE column declared as one is refused there too; it refuses inet and cidr with BackendCapabilityError and the cast to an interval, bytea or blob type itself, while a time or timestamp type casts to TIME(6) or DATETIME(3) (MySQL Guide). A declared type is also what the SQL casts the value to, on every engine (a boolean is ((x) <> 0) on MySQL): a date on Cast, Case, Coalesce, Greatest or Least renders date(…) on SQLite. Concat only takes a text type, on every engine (see Functions and Dates).

A Case column takes its output_field when you pass one. Otherwise PormG types it from its branches, meaning every then and the default:

• A plain value is typed by its Julia type. 1 is an integer, 1.5 a float, "win" text and true a boolean.
• F("points") is typed as the points field. Arithmetic on numbers stays a number.
• A window or aggregate is typed as in the table above.
• NULL branches are skipped. That covers missing and Case's own default, the string "NULL".

The branches must agree. Integers and floats mix, giving a float. Anything else raises QueryBuildError and asks for output_field: text beside a number, only NULL branches, or a value PormG cannot type (text arithmetic, a JSON key lookup's value). A function branch is typed as the table above types it: Lower and Concat are text (#1460). PormG does not guess a type here, because the type decides how the value is bound, and a wrong guess changes the result without any error. Before #812 an expression branch made the column text. The filter below then bound the string "7", and SQLite returned no rows.

using PormG.Functions: Case, When

# Podium points in the 2009 Australian GP (race 1), 0 for everyone else.
podium = M.Result.objects
podium.filter("raceid" => 1)
podium.values("resultid",
    "podium_pts" => Case([When("positionorder__@lte" => 3, then = F("points"))], default = 0))

query = M.Result.objects
query.with("podium" => podium, join_field = "resultid" => "resultid")
query.filter("raceid" => 1, "podium__podium_pts__@gt" => 7)   # 7 binds as a number
query.values("resultid", "driverid__surname", "podium__podium_pts")
df = query |> DataFrame   # Button (10.0) and Barrichello (8.0)

When PormG cannot type a branch, or to state the type yourself, pass output_field. It also becomes the SQL cast, so every row really holds that type:

using PormG.Functions: Lower
using PormG.Models: CharField

winner = M.Result.objects
winner.filter("raceid" => 1)
winner.values("resultid",
    "winner" => Case([When("positionorder" => 1, then = Lower("driverid__surname"))],
                     default = "", output_field = CharField()))

query = M.Result.objects
query.with("w" => winner, join_field = "resultid" => "resultid")
query.filter("raceid" => 1, "w__winner" => "button")
query.values("resultid", "w__winner")
df = query |> DataFrame   # one row: "button"

Here the declaration is optional: Lower is text, as the default "" is, so the column is text without it too. Before #1460 this Case raised QueryBuildError without it.

F arithmetic needs no declaration when its operands are numbers. Here grid and positionorder are integers, so gain is an integer and the filter binds 5 as a number:

# Drivers who gained at least 5 places in the 2009 Australian GP (race 1).
gained = M.Result.objects
gained.filter("raceid" => 1)
gained.values("resultid", "gain" => F("grid") - F("positionorder"))

query = M.Result.objects
query.with("g" => gained, join_field = "resultid" => "resultid")
query.filter("raceid" => 1, "g__gain__@gte" => 5)
query.values("driverid__surname", "grid", "positionorder", "g__gain")
query.order_by("-g__gain")
df = query |> DataFrame   # 6 rows, from Trulli (20th → 3rd, 17) down to Alonso (10th → 5th, 5)

Multiple CTEs

Attach multiple CTEs to the same query:

# CTE 1: Recent races
recent_races = M.Race.objects
recent_races.filter("year__@gte" => 2020)
recent_races.values("raceid", "name", "year")

# CTE 2: Top drivers
top_drivers = M.Driver.objects
top_drivers.filter("driverid__@lte" => 100)
top_drivers.values("driverid", "forename", "surname")

# Main query with both CTEs
query = M.Result.objects
query.with("recent" => recent_races, join_field="raceid" => "raceid")
query.with("top_d" => top_drivers, join_field="driverid" => "driverid")

query.values("resultid", "recent__name", "top_d__forename", "points")
query.filter("recent__name__@isnull" => false, "top_d__forename__@isnull" => false)
df = query |> DataFrame

Each .with() call adds another WITH clause and JOIN to the final SQL.

Choosing Join Types for CTEs

By default, CTEs use LEFT JOIN. Use join_type="INNER" to filter out non-matching rows:

using PormG.Functions: Sum

high_scorers = M.Result.objects
high_scorers.filter("points__@gte" => 10)
high_scorers.values("driverid", "max_points" => Sum("points"))

query = M.Driver.objects
query.with(
    "high_scorers" => high_scorers,
    join_field="driverid" => "driverid",
    join_type="INNER"   # Only include drivers who have high scores
)

query.values("driverid", "forename", "max_points" => "high_scorers__max_points")
query.filter("driverid__@lte" => 100)
df = query |> DataFrame

Available CTE Join Types

The SQL builder can also render "RIGHT" and "FULL" join keywords, but these are not the primary documented workflow.

Deep Join Paths in CTE Links

The join_field can contain a multi-level path with __. PormG builds the intermediate joins needed to connect the main query to the CTE:

using PormG.Functions: Count

# CTE: count drivers per nationality
nat_stats = M.Driver.objects
nat_stats.values("nationality", "driver_count" => Count("driverid"))

# Main query: join CTE via a deep path (Result → Driver → nationality)
query = M.Result.objects
query.with("stats" => nat_stats, join_field="driverid__nationality" => "nationality")

query.filter("raceid__year" => 2023)
query.values("raceid__name", "driverid__surname", "stats__driver_count")
df = query |> DataFrame

PormG automatically:

1. Joins Result → Driver (via the driverid ForeignKey).
2. Links Driver.nationality to stats.nationality (the CTE join column).

CTE Without a JOIN

If you call .with("name" => subq) without providing join_field, PormG still emits the CTE in the WITH clause but does not join it to the main table:

subq = M.Status.objects.filter("status" => "Engine").values("statusid")

query = M.Result.objects
query.with("engine_statuses" => subq)   # Declared but not joined

This is valid SQL, and the CTE's columns are still reachable with CTE(...) — doing so CROSS JOINs it, which is the correlation shape above. Without either a join_field or a correlating filter you get a Cartesian product. The main use case is combining a CTE declaration with a subquery filter:

query = M.Result.objects
query.with("sub" => subq)
query.filter("statusid__@in" => subq)   # Reuse the subquery in a filter

Mixing CTEs and Custom Joins

PormG allows CTEs and custom joins (.cjoin()) in the same query. Parameter ordering is deterministic across these combinations:

# 1. Define the CTE
top_const = M.Constructor.objects.
    filter("constructorid__@lte" => 5).
    values("constructorid", "name")

# 2. Build the chain with CTE, cjoin, filters, and values
df = M.Result.objects.
    with("tc" => top_const, join_field="constructorid" => "constructorid").
    cjoin("driverid" => "Driver", filters=["nationality" => "German"]).
    filter("positionorder" => 1).
    values("resultid", "tc__name", "driverid__surname") |> DataFrame

Chaining Multiple Custom Joins

df = M.Result.objects.
    cjoin("driverid" => "Driver", filters=["nationality" => "Brazilian", "forename" => "Ayrton"]).
    cjoin("raceid" => "Race", filters=["year" => 1991]).
    filter("positionorder" => 1).
    values("resultid", "driverid__surname", "raceid__name") |> DataFrame

See Custom Joins for the full .cjoin() and .on() documentation.

Summary

Next Steps

• Custom Joins — Full cjoin() and on() documentation.
• Field Expressions — Use F() for arithmetic and computed columns.
• Q Objects — Complex boolean logic in filters.