Filters and Aggregates

This page covers lookup operators, filtering, exclusion patterns, grouping, and HAVING clauses.

The __@ Suffix System

PormG uses the __@ suffix on field names for both comparison operators and transform functions:

• field__@operator — Compare a field to a value (e.g., "points__@gt" => 10).
• field__@function — Transform a field before comparing or selecting (e.g., "dob__@year" => 1960).

These work in both filter() and values().

Comparison Operators

Exact match, @contains, @startswith and @endswith compare case-sensitively on every engine. PostgreSQL's default collation is deterministic. On SQLite, = uses the default BINARY collation, and LIKE is case-sensitive because PormG sets PRAGMA case_sensitive_like = ON on every connection. On MySQL, every text column PormG creates or adds and each connection use the utf8mb4_0900_bin collation, which compares code points; a column PormG did not create compares by its own collation (Backend Capabilities, MySQL Guide).

Three field types have lookups of their own, beyond this table: a JSONField (see JSON / JSONB Lookups and Operators below), an ArrayField (see Array Lookups below), and the network address fields (@net_contained, @net_contains, …, see Fields → Network containment lookups).

Negating Pattern and Range Lookups

The pattern and range lookups each have a negated twin — same value handling, the SQL operator flipped to NOT LIKE / NOT ILIKE / <> / !~ / !~* / NOT BETWEEN. Use these to express "does not match" without inverting the logic by hand.

Transform Functions (Modifiers)

The period transforms come in two shapes, and which one you want depends on whether the year should be part of the answer. @quarter and @quadrimester extract the period number — 1 through 4 and 1 through 3 — so filter("date__@quarter" => 1) selects the first quarter of every year. @yyyy_q and @yyyy_quad build the year-qualified label ("1991-Q1"), which is what you want as a values() grouping key when each year's quarters must stay separate. A value outside the period's range raises InvalidValueError rather than matching nothing. The same goes for the time parts @hour, @minute and @second — see Time of day — and for the week parts, whose numbering is pinned in Weeks.

A label filter compares the whole "YYYY-Qn" string, so filter("date__@yyyy_q" => "1991-Q1") selects one quarter of one season, where filter("date__@quarter" => 1) selects that quarter of every season. Membership works too: filter("date__@yyyy_q__@in" => ["1991-Q1", "1991-Q2"]).

Basic Comparisons

Exact Match

df = M.Driver.objects.filter("nationality" => "Brazilian") |> DataFrame

Greater Than / Less Than

query = M.Result.objects
query.filter("positionorder__@lt" => 3)
df = query |> DataFrame

Not Equal

query = M.Result.objects
query.filter("statusid__status__@ne" => "Retired")

Range Filters

@range translates to SQL BETWEEN and accepts exactly two bounds (as a Vector or Tuple):

# Vector syntax
query = M.Driver.objects.filter("driverid__@range" => [1, 5])
df = query |> DataFrame

# Tuple syntax
query = M.Driver.objects.filter("driverid__@range" => (10, 15))
df = query |> DataFrame

String Matching

The contains, startswith and endswith lookups, with their case-insensitive, accent-insensitive and negated twins, match a text value. PormG wraps the value in % and escapes any % or _ inside it, so it matches literally. That cannot be done to a column, so a column expression on the right ("surname__@contains" => F("forename")) raises a FilterError on every backend. The @iexact pair and the PostgreSQL-only @regex and @iunaccent_exact families do take a column, because they use their right-hand side as it is.

The value is a fragment of the column's text, not a whole value of the column's type, so it is bound as text whatever the column holds, as in Django. "date__@startswith" => "2009" matches every race in 2009, and so does "date__@yyyy_mm__@startswith" => "2009", though an exact @yyyy_mm needs a whole "2009-03". A date column, or a transform that yields a date (@date), is matched as its YYYY-MM-DD text on every backend, and a date part (@year, @hour, an Extract) as its digits: "date__@year__@startswith" => "200" selects the 2000s. An integer column, a relation's key included, is matched as its digits too: "grid__@startswith" => "1" selects grid slots 1 and 10–19 on every backend. So is an integer alias, CTE column or Subquery (a Count, a Max("grid")), an integer Case alias with no output_field, a Value(1) alias, arithmetic over a date part (F("date__@year") + 1) and a day count (F("date") - F("date")), and a date shifted by whole days (F("date") + Day(1)) is matched as its YYYY-MM-DD text. A time column is matched as its HH:MM:SS text, with a fraction only when the time has one: "time__@startswith" => "14:" selects the races that start at 14:00 to 14:59 on every backend. On MySQL a TimeField is a TIME(6), which the server prints with six fractional digits (14:00:00.000000); PormG trims the trailing zeros there, so the text is 14:00:00, or 14:00:00.25, as on the other backends.

A float or decimal column raises a FilterError on every backend, because each engine prints it differently: 2.0 is 2 on PostgreSQL and 2.0 on SQLite, so one fragment would select different rows. So does an alias, a CTE column or a Subquery whose value is not an integer (an Avg, a Sum over a decimal), and a number whose type PormG cannot name, whose text it cannot vouch for. Compare it as a number ("fastestlapspeed__@gte" => 200), or match the engine's own text explicitly through a cast: values("t" => Cast("fastestlapspeed", "text")); filter("t__@startswith" => "2"). A Case alias with a float default, and a Value(2.5) alias, raise the same way.

A timestamp, duration, boolean or binary column raises a FilterError on every backend, and so does an alias PormG types as one (a Max("start_at"), a Value(true), a duration such as F("start_at") - F("start_at") or F("fastestlaptime") * 2, a timestamp such as F("date") + Hour(1)). Their text differs per engine: a timestamp prints in the session's time zone on PostgreSQL and as PormG's UTC text on SQLite, a duration follows PostgreSQL's IntervalStyle and is a count of microseconds on MySQL, and a boolean is true on PostgreSQL and 1 on SQLite and MySQL. A binary column holds bytes, not text. Match a part of a timestamp through a transform ("start_at__@date__@startswith" => "2009-03", "start_at__@year" => 2009), or compare the value itself: "start_at__@gte" => DateTime(2009), a duration with @gte and a Dates.Period, a boolean with => true. The engine's own text of a timestamp or a duration is matched through a cast, as for a float; a boolean's text cast is itself refused on SQLite and MySQL, so write the text you mean with a Case.

Case-Sensitive (@contains)

query = M.Result.objects
query.filter("raceid__circuitid__name__@contains" => "Monaco")
count = query.count()

Case-Insensitive (@icontains)

query = M.Result.objects
query.filter("raceid__circuitid__name__@icontains" => "monaco")
count = query.count()

Prefix / Suffix (@startswith, @endswith, @istartswith, @iendswith)

Anchored matches — the wildcard is added on one side only. @startswith renders LIKE 'value%' and @endswith renders LIKE '%value'; the i forms are their case-insensitive twins and render ILIKE instead:

# Surnames beginning with "Ver" (e.g. Verstappen)
M.Driver.objects.filter("surname__@startswith" => "Ver")

# Surnames ending in "sen" (e.g. Magnussen)
M.Driver.objects.filter("surname__@endswith" => "sen")

# Case-insensitive: matches "Verstappen" whatever case the query term is in
M.Driver.objects.filter("surname__@istartswith" => "ver")
M.Driver.objects.filter("surname__@iendswith" => "SEN")

All four escape % and _ in the bound value, so user input is matched literally.

Case-Insensitive Equality (@iexact)

@iexact matches the whole value, ignoring case. It is LOWER(col) = LOWER(val) on PostgreSQL and MySQL and pormg_lower(col) = pormg_lower(val) on SQLite, so accented text folds the same way on every backend, as with @icontains. @niexact is its negated twin (<>). Both work on every backend.

# Finds "Hamilton" whatever case the query term is in, but only as the whole surname
M.Driver.objects.filter("surname__@iexact" => "HAMILTON")

# A column on the right: results where the driver shares the team's nationality
M.Result.objects.filter("driverid__nationality__@iexact" => F("constructorid__nationality"))

The value is compared with =, not LIKE, so a % or _ in it is an ordinary character and no escaping happens. To match accents loosely as well, use the PostgreSQL-only @iunaccent_exact below. On PostgreSQL a btree index on lower(col) serves @iexact — see Expression and partial indexes.

Accent-Insensitive (@iunaccent_contains, @iunaccent_exact)

PostgreSQL only. These lookups match while ignoring both diacritics and case, so an ASCII query finds accented data:

# Substring: finds "Räikkönen" from the ASCII spelling
M.Driver.objects.filter("surname__@iunaccent_contains" => "raikkonen")

# Equality: finds "Räikkönen" regardless of accents/case, but only as a whole value
M.Driver.objects.filter("surname__@iunaccent_exact" => "RAIKKONEN")

They require the unaccent extension and its immutable_unaccent helper, declared once in connection.yml and installed by migrate() — see PostgreSQL Extensions. On SQLite and MySQL these lookups raise a BackendCapabilityError.

There is no @iunaccent_in; OR the equality lookup with Qor for accent-insensitive set membership:

M.Driver.objects.filter(Qor(
    "surname__@iunaccent_exact" => "raikkonen",
    "surname__@iunaccent_exact" => "hakkinen",
))

Regular Expressions (@regex, @iregex)

PostgreSQL only. These lookups match a column against a POSIX regular expression, using PostgreSQL's ~ (case-sensitive) and ~* (case-insensitive) operators. @nregex and @niregex are their negated twins (!~ / !~*).

# Surnames starting with "Ver" — the same rows as "surname__@startswith" => "Ver"
M.Driver.objects.filter("surname__@regex" => "^Ver")

# Case-insensitive, with alternation: surnames containing a doubled "s" or "t"
M.Driver.objects.filter("surname__@iregex" => "(ss|tt)")

# Every driver whose surname does NOT end in "nen"
M.Driver.objects.filter("surname__@nregex" => "nen\$")

• The pattern is a String in PostgreSQL's POSIX syntax, and it is bound as a query parameter like any other value. It can also be another column, for example "surname__@iregex" => F("forename"). It is passed through untouched: no % is added, and % / _ have no special meaning. A Julia Regex (r"^Ver") is refused with a FilterError. Julia regexes are PCRE, which is a different dialect.
• An invalid pattern is reported by PostgreSQL when the query runs, as a StatementError carrying the server's invalid regular expression message. PormG does not parse the pattern itself.
• On SQLite and MySQL these lookups raise a BackendCapabilityError when the query is built. SQLite has no built-in regular expressions, and MySQL has no POSIX ones. PormG does not emulate them, because an emulation would evaluate the pattern in a different regex dialect than PostgreSQL does. The same filter would then silently return different rows on different engines, and an error is safer than that. If an app must run on more than one engine, use @contains / @startswith / @endswith: they behave the same everywhere.

JSON / JSONB Lookups and Operators

For a JSONField column you can reach inside the stored document with a __ path, and (on PostgreSQL) test containment and key existence.

Path lookups (field__key, field__0__key)

Append the JSON keys to the field name with __. A numeric segment is a JSON array index. The value is extracted as text and compared to your filter value. Works on every backend (PostgreSQL #>>, SQLite json_extract, MySQL JSON_UNQUOTE(JSON_EXTRACT(…))). A key that holds JSON null extracts to NULL, as a missing key does.

Assume Constructor has a metadata JSONField holding, e.g., {"principal": "Toto Wolff", "wins": 121, "drivers": [{"name": "Russell"}]}:

# Equality on a nested key
M.Constructor.objects.filter("metadata__principal" => "Toto Wolff")

# Array index then key: drivers[0].name
M.Constructor.objects.filter("metadata__drivers__0__name" => "Russell")

# Numeric comparison — PostgreSQL casts the extracted text to numeric; SQLite compares natively, and
# MySQL compares the text with the number as a double.
# The value is a number, or base-10 numeric text ("100"); other text raises InvalidValueError.
M.Constructor.objects.filter("metadata__wins__@gte" => 100)

# Presence: a missing key extracts to NULL
M.Constructor.objects.filter("metadata__sponsor__@isnull" => true)

# Project an extracted value (the alias is the dotted path)
M.Constructor.objects.values("name", "metadata__principal")

Supported comparisons on a path lookup: = (default), @ne, @gt, @gte, @lt, @lte, and @isnull. Path lookups also work in .values(...); an ordering over one names its type (below).

A projection alias over a path lookup holds the key's value, and a filter on it compares as the path lookup itself does, with the same comparisons:

q = M.Constructor.objects
q.values("name", "principal" => F("metadata__principal"))
q.filter("principal" => "Toto Wolff")      # as filter("metadata__principal" => "Toto Wolff")

A CTE column whose body projects a key holds the key's value too, and a filter on it binds the same way (#1472):

body = M.Constructor.objects
body.values("constructorid", "wins" => "metadata__wins")
q = M.Constructor.objects
q.with("m" => body, join_field = "constructorid" => "constructorid")
q.values("name", "m__wins")
q.filter("m__wins__@gt" => 100)            # a numeric comparison, as "metadata__wins__@gt" => 100

So does a projection alias over a Subquery that projects a key (#1492):

wins = M.Constructor.objects
wins.filter("constructorid" => OuterRef("constructorid"))
wins.values("w" => "metadata__wins")
q = M.Constructor.objects
q.values("name", "wins" => Subquery(wins))
q.filter("wins__@gt" => 100)               # a numeric comparison, as "metadata__wins__@gt" => 100

All three take exactly the path lookup's comparisons. @in, @nin, @range, @nrange and the pattern lookups (@contains, @startswith, …) raise a FilterError on every engine, on the path lookup and on an alias, CTE column or Subquery over it (#1499). A key's value has no declared type, and PostgreSQL extracts it as text, so @range over 9 and 100 would compare text there and skip a row holding 15. Project the key with the type you mean and filter that name:

q = M.Constructor.objects
q.values("name", "wins" => Cast("metadata__wins", "numeric"))
q.filter("wins__@range" => (9, 100))       # numbers on every engine

A path lookup compares the extracted value against a value. A column expression on the right ("metadata__principal" => F("name")) raises a FilterError on every backend. The engines extract different types (PostgreSQL and MySQL text, SQLite the native JSON type), so no single comparison against a column means the same thing on all of them.

The comparison operators over a path lookup compare the same way, in a filter, a Q, a When, a projection or a join condition (#1511):

M.Constructor.objects.filter(F("metadata__wins") > 100)    # as filter("metadata__wins__@gt" => 100)

A column on the right of the key (F("metadata__wins") > F("constructorid")) raises the same FilterError. So does a value that carries the key (Coalesce("metadata__wins", 0) > 100), as a filter on its alias does. Cast the key to say what it compares: Cast("metadata__wins", BigIntegerField()) > F("constructorid").

A key compares as its lookup does only on the left. On the right of a comparison (F("constructorid") == F("metadata__wins"), or the pair "constructorid" => F("metadata__wins")), a key raises a FilterError on every backend, as does a value that carries one (F("grid") > Coalesce("metadata__wins", 0)) (#1528). This applies even when both sides hold text, because the key's value still has no declared type. Cast it on that side too: F("constructorid") == Cast("metadata__wins", BigIntegerField()).

Arithmetic over a key

A key's value has the type the data gives it, so PormG does not know whether wins holds an integer, a float or text. State the number you mean with a cast before computing on it:

q = M.Constructor.objects
q.values("name", "half" => Cast("metadata__wins", "numeric") / 2)    # 60.5 on every engine
q.values("name", "half" => Cast("metadata__wins", BigIntegerField()) / 2)    # 60 on every engine

Without the cast, arithmetic on a key (+, -, *, /), a Sum and an Avg raise QueryBuildError on every engine, naming the cast to write (#1483). The query does not say which number it computes on, and the engines answer differently: SQLite's json_extract returns the integer the document holds, so it would divide 121 / 2 as integers and return 60, and PostgreSQL extracts the key as text, which its +, / and sum reject when the query runs. The same holds for a value that carries the key into the arithmetic: the functions whose value is their operand's (Floor, Ceil, Abs, Max, Min, Coalesce, Greatest, Least, Lag and its default) and a Case branch, so Floor("metadata__wins") / 2 raises too. It also holds wherever the arithmetic appears: a projection, a filter value ("constructorid__@gt" => F("metadata__wins") / 2), an update() value, an ordering, a CTE body or a Subquery.

A Max or a Min over an uncast key raises QueryBuildError on every engine too (#1491). It computes nothing, but it orders the value, and the query does not say whether as numbers or as text: PostgreSQL extracts the key as text, so over {"wins": 9} and {"wins": 10} its Max would be "9" ('9' > '10'), where SQLite's json_extract gives the number 10. Name the order you mean:

q = M.Constructor.objects
q.values("most" => Max(Cast("metadata__wins", "numeric")))    # 10 on every engine
q.values("last" => Max(Cast("metadata__principal", "text")))  # the greatest text, in the engine's collation

An ordering over an uncast key raises QueryBuildError on every engine for the same reason (#1497). That covers an order_by term, an order_by on an alias, CTE column or Subquery that holds the key, a window's order_by, and Greatest/Least over a key. PostgreSQL and MySQL would order the key's text, putting 9 above 10 in descending order, where SQLite orders the numbers. order_by takes a path or a projected name, so project the cast and order by its name:

q = M.Constructor.objects
q.values("name", "wins" => Cast("metadata__wins", "numeric"))
q.order_by("-wins")                                                   # 10 before 9 on every engine

q = M.Constructor.objects
q.values("name", "most" => Greatest(Cast("metadata__wins", "numeric"), "constructorid"))

NullIf over an uncast key, in either operand, raises QueryBuildError on every engine as well (#1510). It compares its operands, and the query does not say whether as numbers or as text: PostgreSQL would compare the key's text with a number and fail when the query runs, where SQLite compares the number the document holds. Cast the key:

q = M.Constructor.objects
q.values("name", "wins" => NullIf(Cast("metadata__wins", "numeric"), 0))     # NULL for no wins

A function over the key with nothing computed on it builds (Floor("metadata__wins")), and so does a comparison ("metadata__wins__@gte" => 100, above).

A whole document (field => document)

A filter on the column itself compares the whole stored document, and get_or_create and update_or_create find their row the same way:

M.Constructor.objects.filter("metadata" => """{"principal": "Toto Wolff", "wins": 121}""")

PostgreSQL compares jsonb values, so key order and spacing do not matter, and MySQL compares JSON values too (PormG binds the document as CAST(? AS JSON) there). SQLite compares the stored text, so it matches a document written from the same value.

A projection alias over the column and an F comparison with it (== or !=) compare the same way:

q = M.Constructor.objects
q.values("name", "meta" => F("metadata"))
q.filter("meta" => """{"principal": "Toto Wolff", "wins": 121}""")

M.Constructor.objects.filter(F("metadata") == """{"principal": "Toto Wolff", "wins": 121}""")

Containment and key-existence operators (PostgreSQL only)

These map to the PostgreSQL JSONB operators and apply to a JSON column (not a nested path). On SQLite and MySQL they raise a BackendCapabilityError:

# Rows whose metadata contains this sub-document (deep match)
M.Constructor.objects.filter("metadata__@jcontains" => Dict("drivers" => [Dict("name" => "Russell")]))

# Rows that have BOTH keys
M.Constructor.objects.filter("metadata__@has_keys" => ["principal", "wins"])

@jcontains accepts a Dict, Vector, NamedTuple, or a raw JSON string (validated at build time). @has_any_keys / @has_keys take a vector of keys. All values are sent as bound parameters. A column expression on the right ("metadata__@has_key" => F("name")) raises a FilterError on every backend.

Array Lookups (PostgreSQL only)

An ArrayField takes Django's array lookups. Like the JSON operators they are PostgreSQL only, and on SQLite and MySQL they raise a BackendCapabilityError:

# Two-stop strategies or longer that started on the soft tyre.
M.Race_strategy.objects.
  filter("pit_laps__@len__@gte" => 2, "tyre_compounds__0" => "SOFT").
  values("team", "pit_laps").
  list()

The containment value is a Vector, even for one element. An index is compared as one element of the element field's type, and a slice as an array. The value rules, the NULL semantics and the refusals are in Querying array fields.

IN and NOT IN

Value In Set

query = M.Result.objects
query.filter("raceid__circuitid__name__@in" => ["Circuit de Monaco", "Silverstone"])

Value Not In Set

query = M.Driver.objects
query.filter("nationality__@nin" => ["British", "German"])

Subquery IN

You can also pass a query object to @in for a server-side IN (SELECT ...):

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

query = M.Result.objects
query.filter("statusid__@in" => engine_statuses)

The subquery must project exactly one column — see Subqueries and CTEs for the full column-count rule and SQL-function projection examples.

A column is not a list

@in and @nin take a list of values or a subquery. A column expression on the right, such as "points__@in" => F("grid"), a Case, or a Joined/CTE column, raises a FilterError when the filter is built. So does a list of them, such as "points__@in" => [F("grid"), F("positionorder")]. To test membership against another table's column, pass a subquery that projects it. To compare against several columns of the same row, combine the comparisons: @in is an OR of equalities and @nin an AND of inequalities.

# Results whose points equal either the grid slot or the finishing position.
M.Result.objects.filter(Qor(F("points") == F("grid"), F("points") == F("positionorder")))

Correlated EXISTS

Use Exists(subquery) with OuterRef("field") when the child query must compare against the current row of the outer query. PormG renders the child query as EXISTS (SELECT 1 ... LIMIT 1) and keeps child filter values parameterized.

using PormG: Exists, OuterRef, Qor

fast_laps = M.Lap_times.objects.filter(
    "raceid" => OuterRef("raceid"),
    "driverid" => OuterRef("driverid"),
    "milliseconds__@lte" => 90_000,
)

fast_pit_stops = M.Pit_stops.objects.filter(
    "raceid" => OuterRef("raceid"),
    "driverid" => OuterRef("driverid"),
    "milliseconds__@lte" => 30_000,
)

query = M.Result.objects
query.filter(Qor(Exists(fast_laps), Exists(fast_pit_stops)))
query.values("resultid", "raceid__name", "driverid__surname")

Generated SQL (PostgreSQL):

SELECT
    "Tb"."resultid" as resultid,
    "T1"."name" as raceid__name,
    "T2"."surname" as driverid__surname
FROM "result" as "Tb"
INNER JOIN "races" as "T1" ON "Tb"."raceid" = "T1"."raceid"
INNER JOIN "drivers" as "T2" ON "Tb"."driverid" = "T2"."driverid"
WHERE (EXISTS (SELECT 1
FROM "lap_times" as "R1"
WHERE "R1"."raceid" = "Tb"."raceid" AND
   "R1"."driverid" = "Tb"."driverid" AND
   "R1"."milliseconds" <= $1
LIMIT 1) OR EXISTS (SELECT 1
FROM "pit_stops" as "R2"
WHERE "R2"."raceid" = "Tb"."raceid" AND
   "R2"."driverid" = "Tb"."driverid" AND
   "R2"."milliseconds" <= $2
LIMIT 1))
-- Parameters: [90000, 30000]

OuterRef("pk") resolves to the outer model's primary key field automatically:

# Find results that have at least one linked test-deletion row
del_sub = M.Just_a_test_deletion.objects.filter(
    "test_result" => OuterRef("pk"),
)
query = M.Result.objects.filter(Exists(del_sub))

Generated SQL (PostgreSQL):

SELECT
    *
FROM "result" as "Tb"
WHERE EXISTS (SELECT 1
FROM "just_a_test_deletion" as "R1"
WHERE "R1"."test_result" = "Tb"."resultid"
LIMIT 1)

Combining EXISTS with outer scalar filters

Additional filters on the outer query AND with the EXISTS predicate in the usual way:

fast_laps = M.Lap_times.objects.filter(
    "raceid"             => OuterRef("raceid"),
    "driverid"           => OuterRef("driverid"),
    "milliseconds__@lte" => 90_000,
)

# All results for driver 1 that also have a fast lap — EXISTS AND scalar
query = M.Result.objects.filter(Exists(fast_laps), "driverid" => 1)

Generated SQL (PostgreSQL):

SELECT
    *
FROM "result" as "Tb"
WHERE EXISTS (SELECT 1
FROM "lap_times" as "R1"
WHERE "R1"."raceid" = "Tb"."raceid" AND
   "R1"."driverid" = "Tb"."driverid" AND
   "R1"."milliseconds" <= $1
LIMIT 1) AND
   "Tb"."driverid" = $2
-- Parameters: [90000, 1]

Reusing a subquery object

The same subquery object can be passed to Exists in multiple outer queries without mutation. Each outer query builds its own independent correlated subquery:

lap_sub = M.Lap_times.objects.filter(
    "raceid"             => OuterRef("raceid"),
    "driverid"           => OuterRef("driverid"),
    "milliseconds__@lte" => 90_000,
)

all_fast     = M.Result.objects.filter(Exists(lap_sub))                       # all results with a fast lap
driver2_fast = M.Result.objects.filter(Exists(lap_sub), "driverid" => 2)      # narrowed to driver 2

Generated SQL for driver2_fast (PostgreSQL):

SELECT
    *
FROM "result" as "Tb"
WHERE EXISTS (SELECT 1
FROM "lap_times" as "R1"
WHERE "R1"."raceid" = "Tb"."raceid" AND
   "R1"."driverid" = "Tb"."driverid" AND
   "R1"."milliseconds" <= $1
LIMIT 1) AND
   "Tb"."driverid" = $2
-- Parameters: [90000, 2]

Filter Values from Web Frameworks

PormG accepts any AbstractString — SubString{String}, LazyString — wherever a String is expected, so values parsed directly from HTTP query strings (e.g. via split, HTTP.URIs, or Genie parameters) can be passed without an explicit String(...) conversion:

# SubString from a query-string parser — no conversion needed
nationality = split("nationality=British", "=")[2]  # SubString{String}
query = M.Driver.objects.filter("nationality" => nationality)

# @in with a split list also works
codes = split("hamilton,vettel,alonso", ",")  # Vector{SubString{String}}
query = M.Driver.objects.filter("driverref__@in" => codes)

This is not limited to filter values. A column name, an alias, a database key or a field keyword accepts the same spellings, so a value can travel from the request straight into the query:

# A column list and a sort key straight out of a query string
columns = split("cols=driverid__surname,points", "=")[2]  # SubString{String}
sort_by = split("sort=-points", "=")[2]                   # SubString{String} — "-points"

query = M.Driver_standings.objects.
    values(split(columns, ",")...).
    order_by(sort_by)

# Field references and SQL functions take them too
query = M.Driver.objects.values(
    "who"  => Upper(split("field=surname", "=")[2]),
    "ref"  => F(split("field=driverref", "=")[2]),
)

The value comes back out of the row the same way it went in — a row is keyed by any string spelling too, so the column name parsed from the request can be reused to read the result:

cols = split("cols=driverid__surname,points", "=")[2]   # SubString{String}

row = M.Driver_standings.objects.
    values(split(cols, ",")...).
    order_by("-points").
    first()

key = split(cols, ",")[1]        # SubString{String} — no String(...) needed
row[key]                         # "Verstappen"
haskey(row, key)                 # true
get(row, key, "absent")          # "Verstappen"

Concretely, a non-String AbstractString is accepted by:

• Reads — filter values and __@in lists; values() field names and aliases; order_by(), including the "-field" descending form; db().
• Column references — F(...), and the right-hand side of any F or Joined comparison.
• Aggregates — Sum, Avg, Count, Max, Min.
• Scalar and conversion functions — Lower, Upper, Length, Abs, Round, Trim, LTrim, RTrim, Floor, Ceil, Sqrt, Exp, Ln, Cast, Extract, ToChar, Concat, Coalesce, Greatest, Least, NullIf, Replace, LPad, RPad, Substr, Power, Mod, together with their output_field and _as keywords.
• Windows — WindowOver(partition_by = …, order_by = …, frame = …), SQLOrder(...; orientation = …, _as = …), and the value functions Lag, Lead, FirstValue, LastValue, NthValue.
• Bulk writes — the filters, columns and match_on arguments.
• Rows — row[key], haskey(row, key) and get(row, key, default) on a PormGRow, alongside the Symbol and dot-access spellings.
• Model fields — verbose_name, db_column, CharField(choices = …), and to / through / how / related_name / pk_field on ForeignKey, OneToOneField and ManyToManyField.
• Field defaults — default = on the seven plain-text fields: CharField, TextField, EmailField, URLField, SlugField, FileField and ImageField. These share one policy: any AbstractString, or an Integer written as its decimal text (CharField(default = 0) stores "0"). Anything else — a Symbol, a Float64, a Bool — is a FieldValidationError. UUIDField and JSONField are not part of that policy — each validates what the value means, so neither is simply stricter nor simply looser. UUIDField accepts any string spelling of a well-formed UUID and refuses everything else, including an Integer: UUIDField(default = "abc") is refused where TextField(default = "abc") is not. JSONField refuses a string that is not valid JSON, and in the other direction accepts any value it can serialize — 3.5, true, [1, 2] and Dict("a" => 1) all store their JSON text, where the seven plain-text fields refuse all four.
• Model names — the positional name in Models.Model("drivers", …), including the no-fields guard that refuses Models.Model(name) on its own.

Widening the accepted type did not widen the accepted shape: values("points__@lte") is still refused as an operator suffix in a projection whichever string type spells it.

The same distinction holds one type family over, for integers. A field constructor that takes a number takes the abstract type, not Int64 — the spelling matters no more here than it does for a SubString (#614):

• Numeric defaults — default = on IDField, ForeignKey, OneToOneField, IntegerField, PositiveSmallIntegerField, PositiveIntegerField and BigIntegerField takes any Integer, or an Integer written as its decimal text, and stores an Int64: IntegerField(default = Int32(5)) stores 5. FloatField and DecimalField take any non-Bool Real — an Integer, a Float32, a Rational, a Decimals.Decimal — and store a Float64.
• Integer width keywords — max_length on CharField, URLField, SlugField and BinaryField, and max_digits / decimal_places on DecimalField, follow the same rule and store an Int: DecimalField(max_digits = "12") stores 12, and text that is not a number ("8 8", "1x") is a FieldValidationError. PasswordField's max_length takes an Integer only. BinaryField also accepts max_length = nothing, and reads a String with no digit in it the same way, as "no limit".

Bool is refused throughout, exactly as it is for the plain-text fields: IntegerField(default = true) is a FieldValidationError, not a stored 1, and so is CharField(max_length = true). A value out of range is refused rather than silently coerced: IntegerField(default = big(2)^70) and CharField(max_length = big(2)^70) raise rather than wrapping, and FloatField(default = big"1e400") raises rather than storing Inf.

A Decimals.Decimal is accepted on FloatField and DecimalField and refused on the integer family — IntegerField(default = Decimal(0, 5, 0)) is a FieldValidationError. The asymmetry is the columns, not an oversight: a float column can hold a scaled value and an integer column cannot, so an integer default has to be written as an integer. Write IntegerField(default = 5), or IntegerField(default = "5") if the value arrives as text.

Null Checks

# Find drivers with no date of birth recorded
query = M.Driver.objects.filter("dob__@isnull" => true)

# Find drivers that DO have a date of birth
query = M.Driver.objects.filter("dob__@isnull" => false)

Multiple Filters (AND Logic)

Multiple pairs inside one filter() call are combined with AND:

query = M.Result.objects
query.filter("statusid__status" => "Finished", "resultid" => 26745)
query.values(
    "resultid",
    "raceid__circuitid__name",
    "driverid__forename",
    "constructorid__name",
    "statusid__status",
    "grid",
    "laps"
)
results = query.list()

Successive .filter() calls also use AND — they are additive:

query = M.Result.objects
query.filter("statusid__status" => "Finished")
query.filter("positionorder" => 1)   # Adds another AND condition

For OR logic, use Qor():

using PormG: Qor

query = M.Result.objects
query.filter(Qor("constructorid" => 1, "constructorid" => 9))

Ordering and NULL Placement

order_by(...) sorts the result. Prefix a field with - for descending; pass several fields to break ties left to right:

# By nationality (A→Z), then surname (A→Z) within each nationality
query = M.Driver.objects
query.values("surname", "nationality")
query.order_by("nationality", "surname")

Where NULLs land

Nullable sort keys are normalized to sort the same way on every engine (PostgreSQL's native default is the opposite of SQLite's and MySQL's, which used to make order_by(...).first() return different rows on each). PormG standardizes on PostgreSQL's convention — NULL sorts as the largest value — on every backend:

# nationality is nullable. Ascending → the rows with a NULL nationality come LAST,
# identically on every engine.
M.Driver.objects.values("surname", "nationality").order_by("nationality").list()

Overriding placement per term

Pass a SQLOrder object with nulls = :first or nulls = :last to force placement independently of the sort direction:

using PormG.QueryBuilder: SQLOrder, SQLField

# Ascending by nationality, but push NULL nationalities to the FRONT
M.Driver.objects.values("surname", "nationality").order_by(
    SQLOrder(SQLField("nationality", "nationality"); orientation = "ASC", nulls = :first)
).list()

orientation must be "ASC" or "DESC" (case-insensitive); any other value — including a user-supplied sort-direction string forwarded as-is — raises a QueryBuildError at construction instead of reaching the SQL.

On SQLite builds older than 3.30.0 (which lack NULLS FIRST/LAST syntax), and on MySQL, PormG emits the portable (field IS NULL) sort prefix, so the placement is identical there too. When the term is a projected name that a table in the query also has as a column, the prefix holds the projection's SQL rather than the name, which those engines would read as the column there.

First, last, earliest, and latest

first() and last() return a single row (or nothing) for the current query. last() returns the row first() would return under the reversed ordering — direction and NULL placement invert together. With no order_by set, last() falls back to primary-key descending, so it is always well-defined:

standings = M.Driver_standings.objects.filter("raceid" => 1)

standings.order_by("points").first()   # fewest points
standings.order_by("points").last()    # most points (ORDER BY points DESC, one row)

M.Driver.objects.last()                # highest driverid (pk-descending fallback)

earliest(fields...) / latest(fields...) order by the field(s) you name (ascending / descending; a leading - flips a term, so latest("dob") == earliest("-dob")) and return the extreme row. Unlike first/last, they raise DoesNotExist on an empty queryset — they are the extreme-row counterpart of get():

M.Driver.objects.earliest("dob")   # oldest driver
M.Driver.objects.latest("dob")     # youngest driver

Counting

count() is a terminal that returns a scalar Int for the current query (filters included). It has four forms:

# Total rows
M.Driver.objects.count()                                 # SELECT COUNT(*)

# Distinct rows — dedupes the model's rows, or the values() projection when there is one
M.Driver.objects.distinct().count()                      # same as:
M.Driver.objects.count(distinct=true)

# Across a to-many filter, each matching driver counts once (110 drivers with a pole, not their 1136 poles)
M.Driver.objects.filter("result__grid" => 1).distinct().count()

# Non-null values of one column
M.Driver.objects.count("nationality")                    # SELECT COUNT("nationality")

# Distinct values of one column (e.g. "how many nationalities are represented?")
M.Driver.objects.count("nationality", distinct=true)     # SELECT COUNT(DISTINCT "nationality")

Filters apply to every form:

# How many distinct nationalities among drivers who have raced for constructor 1?
M.Result.objects.filter("constructorid" => 1).count("driverid__nationality", distinct=true)

Aggregations and Grouping

PormG.Functions provides five aggregate functions: Count, Sum, Avg, Max, and Min.

When aggregate values appear in values(), PormG automatically groups by the non-aggregated columns.

using PormG.Functions: Count, Sum, Max, Min

query = M.Result.objects
query.values(
    "statusid__status",
    "raceid__circuitid__name",
    "driverid__forename",
    "constructorid__name",
    "count_grid" => Count("grid"),
    "max_grid"   => Max("grid"),
    "min_grid"   => Min("grid")
)
query.filter("statusid__status" => "Finished", "driverid__forename" => "Ayrton")
query.order_by("raceid__circuitid__name")
df = query |> DataFrame

How Grouping Works

PormG detects which columns in values() are aggregates and which are plain fields. It generates:

SELECT ..., COUNT("Tb"."grid") as count_grid, ...
FROM "result" as "Tb" ...
GROUP BY 1, 2, 3, 4   -- groups by non-aggregate columns

You do not write GROUP BY manually — PormG handles it.

What a Sum returns

Sum over integers is an integer on every engine, as Django's Sum of an integer field is. SQLite sums integers as integers. MySQL sums any integer to a DECIMAL, so PormG renders (SUM(x) DIV 1) there for every integer sum. PostgreSQL sums a smallint or an integer column to a bigint, but widens the sum of a bigint to numeric, which would keep the half once divided. So when what it sums is a bigint (an IDField, a BigIntegerField, a ForeignKey, an integer literal such as the 1 of a conditional count, or integer arithmetic over one), PormG renders SUM(x)::bigint there (#1147):

using PormG.Functions: Sum, Case, When

query = M.Result.objects
query.filter("raceid" => 18)
query.values(
    "id_sum"  => Sum("resultid"),                                                  # Int64 on every engine
    "half"    => Sum("resultid") / 2,                                              # integer division on every engine
    "podiums" => Sum(Case(When("positionorder__@lte" => 3, then = 1), default = 0)))  # Int64 on every engine
df = query |> DataFrame
# PostgreSQL: SUM("Tb"."resultid")::bigint as "id_sum", (SUM("Tb"."resultid")::bigint / $1::bigint) as "half", …

A sum beyond the bigint range then raises on PostgreSQL (bigint out of range), as it does on SQLite (integer overflow) and on MySQL (a StatementError with SQLSTATE 22003). A sum of floats or decimals is not cast: it stays a double precision or a numeric.

Aggregating Without Grouping

To compute one or more aggregates over the whole (optionally filtered) queryset — with no GROUP BY — use the aggregate(...) terminal. It is Django's .aggregate(): pass "alias" => AggregateFunction(...) pairs and it returns a single-row NamedTuple of scalars with dot-access.

# Highest score, lowest score, and number of results for one constructor
agg = M.Result.objects.filter("constructorid" => 131).aggregate(
    "max_points"    => Max("points"),
    "min_points"    => Min("points"),
    "total_results" => Count("resultid"),
)
agg.max_points      # e.g. 25.0
agg.total_results   # e.g. 412

aggregate() computes over the entire queryset, so it cannot be combined with values() grouping columns — call it on an unprojected (optionally filtered) queryset. For grouped aggregation, use values(...) (see above); PormG also emits no GROUP BY when every projected column is an aggregate, so the equivalent values() form returns the same single summary row when you want a DataFrame:

# Same numbers as a one-row DataFrame, via the values() projection path
query = M.Result.objects
query.filter("constructorid" => 131)
query.values(
    "max_points"    => Max("points"),
    "min_points"    => Min("points"),
    "total_results" => Count("resultid")
)
df = query |> DataFrame   # one-row DataFrame

Generated SQL (PostgreSQL):

SELECT
    MAX("Tb"."points") as "max_points",
    MIN("Tb"."points") as "min_points",
    COUNT("Tb"."resultid") as "total_results"
FROM "result" as "Tb"
WHERE "Tb"."constructorid" = $1
-- Parameters: [131]

The WHERE filter still applies row-by-row before aggregation; it is the absence of plain (non-aggregate) projection columns that removes the GROUP BY. Add any plain column back into values() and PormG groups by it again, exactly as shown in the section above.

These aggregates can carry arithmetic too — e.g. "id_span" => Max("resultid") - Min("resultid"), or subtract a constant like Max("resultid") - 1000. See Aggregate Arithmetic.

Aggregating a Boolean Column

Over a BooleanField, Max answers "is any row true?" and Min answers "are all rows true?". Both read back as a Bool on every engine. PostgreSQL has no max(boolean), so PormG renders its boolean aggregates there, BOOL_OR and BOOL_AND. SQLite and MySQL store a boolean as 0/1 (a TINYINT(1) on MySQL), where MAX/MIN already give the same answer.

Sum and Avg over a boolean raise QueryBuildError on every engine: the query does not say which number a flag counts as. To count the true rows, turn the flag into a number explicitly with a standalone When, one that sets otherwise:

using PormG.Functions: Avg, Max, Min, Sum, When

# A race-entry model with a boolean flag, e.g. is_rookie = Models.BooleanField()
query = M.Race_entry.objects
query.values(
    "raceid",
    "any_rookie" => Max("is_rookie"),                               # BOOL_OR on PostgreSQL
    "all_rookie" => Min("is_rookie"),                               # BOOL_AND on PostgreSQL
    "rookies"    => Sum(When("is_rookie" => true, then = 1, otherwise = 0)),   # how many are true
    "share"      => Avg(When("is_rookie" => true, then = 1, otherwise = 0)),   # fraction that are true
)
df = query |> DataFrame

Sum("is_rookie") is refused rather than answered differently on different engines. A NULL flag counts as not true in rookies and share, while Max/Min skip it.

These boolean values read back as a Bool on every engine: the column itself, a comparison projected as a value ("gained" => F("grid") > F("positionorder")), Cast(x, "boolean"), Coalesce(Max("is_rookie"), false), Lag/Lead/FirstValue over a boolean, a projected Exists(...), a Value(true)/Value(false) literal (alone, under a CTE or inside a Subquery), a Case whose branches are all true/false, and a function over a condition (Max(Q("grid__@gt" => 1)), Coalesce(Q(…), false)). A Coalesce, Greatest or Least over booleans has every operand a boolean: a boolean beside a value of another type, such as a Case that mixes true with a number, raises QueryBuildError on every engine (#1386; see Mixed date, time, text and number operands). SQLite and MySQL store each of these booleans as 0/1, and PormG reads that back as a Bool. Such a Case, and a function over a condition, is a boolean for the rules above too: Max of it renders BOOL_OR on PostgreSQL, and Sum/Avg of it raise QueryBuildError.

Aggregating Across To-Many Relations (Fan-Out Guard)

Joining a to-many relation — a reverse foreign key (one parent → many children), a many-to-many, or a cjoin_on join not proven to-one — repeats each parent row once per related row before aggregation. An aggregate over a parent/base column would therefore be silently multiplied. PormG refuses this at build time rather than return a confidently-wrong number:

# ✗ raises: counts the DRIVER pk, but the driver_standings join repeats each driver row once per standing
query = M.Driver.objects
query.values("nationality", "n" => Count("driverid"))
query.filter("driver_standings__position__@gte" => 1)
query |> DataFrame   # QueryBuildError: PormG fan-out guard (#74): the aggregate n is inflated because …

The legitimate case — aggregating the related table's own column (e.g. counting the related rows) — is exactly the intended fan-out and works normally:

# ✓ counts each driver's standings rows — the fan-out IS the answer
query = M.Driver.objects
query.values("driverid", "standings" => Count("driver_standings__driverid"))
df = query |> DataFrame

The rule. When a to-many join is present, COUNT / SUM / AVG raise if they aggregate a column the join row-multiplies (a base/parent column, or any column when two or more to-many relations are joined). To resolve it, pick one:

1. Aggregate the related table's own column — count/sum the related rows directly, as above.
2. Pass distinct=true if de-duplicated counting is what you want: Count("driverid", distinct=true) renders COUNT(DISTINCT …).
3. Compute the aggregate in a correlated Subquery(...) projected in values() so the base rows are never multiplied — see Scalar correlated subqueries for the full pattern.

Not affected. Ordinary forward (to-one) ForeignKey traversals never trip the guard — only to-many joins (reverse FK / many-to-many / a to-many cjoin_on) multiply rows. Aggregating across a normal FK is always fine:

# ✓ to-one join (Result → Constructor); no fan-out — results per constructor
query = M.Result.objects
query.values("constructorid__name", "n" => Count("resultid"))
query.filter("raceid" => 1)
df = query |> DataFrame

Exemptions. Max and Min are immune to row duplication and are never blocked; an aggregate built with distinct=true is treated as an explicit opt-in.

Who introduced the join does not matter here. In the first example the to-many join comes from a filter. A query that does not aggregate refuses such a filter outright (Filtering or Ordering Across a To-Many Relation); one that aggregates returns one row per group, so the repeat can only show inside an aggregate, and this guard is the one that answers. A cjoin(field = …) link to a column the target does not declare unique counts as a to-many join for it as well.

A cjoin_on join. A cjoin_on self-join or custom join is to-many unless its ON clause pins a primary key or a declared-unique column of the target to one value per base row (see When PormG treats a cjoin_on join as to-many in Custom Joins). A self-join on a shared, non-unique column is the typical case:

# ✗ raises: each driver meets every driver of the same nationality, so COUNT repeats each base row
query = M.Driver.objects
query.cjoin_on("Driver", alias = "d2", on = [Joined("d2", "nationality") == F("nationality")])
query.values("nationality", "n" => Count("driverid"))
query |> DataFrame   # QueryBuildError: PormG fan-out guard (#74): … The cjoin_on alias(es) "d2" count as to-many …

The same three fixes apply, and Count(Joined("d2", "driverid")) is the "related table's own column". A join on a column that is unique in your data but not declared unique is refused too: declare it with unique = true or a UniqueConstraint (a schema change: the migration adds the constraint, and fails if the table holds duplicates).

HAVING Clauses

When you filter on an aggregate alias, PormG automatically promotes the condition to HAVING:

query = M.Result.objects
query.values(
    "raceid__circuitid__name",
    "driverid__forename",
    "constructorid__name",
    "count_grid" => Count("grid")
)
query.filter("statusid__status" => "Finished", "count_grid__@lte" => 3)
df = query |> DataFrame

PormG generates:

SELECT
   "Tb_2"."name" as raceid__circuitid__name,
   "Tb_3"."forename" as driverid__forename,
   "Tb_4"."name" as constructorid__name,
   COUNT("Tb"."grid") as count_grid
FROM "result" as "Tb"
 INNER JOIN "race" AS "Tb_1" ON "Tb"."raceid" = "Tb_1"."raceid"
 INNER JOIN "circuit" AS "Tb_2" ON "Tb_1"."circuitid" = "Tb_2"."circuitid"
 INNER JOIN "driver" AS "Tb_3" ON "Tb"."driverid" = "Tb_3"."driverid"
 INNER JOIN "constructor" AS "Tb_4" ON "Tb"."constructorid" = "Tb_4"."constructorid"
 INNER JOIN "status" AS "Tb_5" ON "Tb"."statusid" = "Tb_5"."statusid"
WHERE "Tb_5"."status" = $1
GROUP BY 1, 2, 3
HAVING COUNT("Tb"."grid") <= 3

Notice how PormG separates:

• WHERE — row-level conditions (status = 'Finished')
• HAVING — aggregate conditions (COUNT(grid) <= 3)

The alias is what routes the condition to HAVING. An aggregate written directly into filter, such as filter(Count("grid") <= 3), raises QueryBuildError instead of rendering in WHERE. See Filter the alias, not the expression.

Aggregate Arithmetic in HAVING

You can filter on computed aggregate expressions too:

query = M.Result.objects
query.values(
    "constructorid__name",
    "avg_perf" => Sum("points") / Count("resultid")
)
query.filter("avg_perf__@gt" => 5)

The projected expression may itself carry values — arithmetic operands, a Case/When arm, a Value(...). Those are bound separately for each clause the expression prints in, so a conditional count reads the same in HAVING as it does in SELECT:

# Seasons with more than eight races after round 10
query = M.Race.objects
query.values("year", "late_season" => Count(Case([When("round__@gt" => 10, then = 1)])))
query.filter("late_season__@gt" => 8)

SELECT "Tb"."year", COUNT(CASE WHEN "Tb"."round" > $1 THEN $2::bigint ELSE NULL END) as "late_season"
FROM "race" as "Tb"
GROUP BY 1
HAVING COUNT(CASE WHEN "Tb"."round" > $3 THEN $4::bigint ELSE NULL END) > $5

Note the operands appear twice and bind twice — $1/$2 for the projection, $3/$4 for the HAVING copy. That is correct: the expression is evaluated in two clauses.

For more complex expressions, see Field Expressions.

A Function Over an Aggregate

A function that wraps an aggregate is still an aggregate. Coalesce, Cast, NullIf, Round, Case/When and the rest group like the bare aggregate they contain, and a filter on their alias goes to HAVING. Coalesce(Sum(...), 0) is the usual way to report an empty sum as 0 rather than NULL:

using PormG.Functions: Coalesce, Sum

# 2009 drivers who never finished a race on the lead lap — `milliseconds` is NULL for everyone else
query = M.Result.objects
query.filter("raceid__year" => 2009)
query.values("driverid__surname", "lead_lap_ms" => Coalesce(Sum("milliseconds"), 0))
query.filter("lead_lap_ms" => 0)

SELECT "Tb_1"."surname" as "driverid__surname",
  COALESCE(SUM("Tb"."milliseconds"), $1::bigint) as "lead_lap_ms"
FROM "result" as "Tb"
 INNER JOIN "driver" AS "Tb_1" ON "Tb"."driverid" = "Tb_1"."driverid"
 INNER JOIN "race" AS "Tb_2" ON "Tb"."raceid" = "Tb_2"."raceid"
WHERE "Tb_2"."year" = $2
GROUP BY 1
HAVING COALESCE(SUM("Tb"."milliseconds"), $3::bigint) = $4

Until #702, most of these wrappers dropped the aggregate. The query printed no GROUP BY, and SQLite returned a single row for the whole table.

A Column Beside an Aggregate in One Expression

An expression can also mix a plain column with an aggregate: F("grid") - Avg("positionorder"), Coalesce("grid", Max("points")), or a Case whose condition reads a column while its branch aggregates. Such an expression is computed once per group, but the column in it has one value per row. It therefore needs that column grouped, which the query may not do. PormG does not pick a grouping for you. It raises QueryBuildError naming the column, unless the query already groups it:

using PormG.Functions: Avg   # `F` comes with `using PormG`

# ✗ raises QueryBuildError: `grid` is one start's slot, but the query groups by driver
query = M.Result.objects
query.filter("raceid__year" => 1990)
query.values("driverid__surname", "places_gained" => F("grid") - Avg("positionorder"))

There are two fixes, and they answer different questions. Aggregate the column, and each driver's average grid slot is compared with their average finish:

query = M.Result.objects
query.filter("raceid__year" => 1990)
query.values("driverid__surname", "places_gained" => Avg("grid") - Avg("positionorder"))

Project the column, and the query groups by it too, giving one row per driver and grid slot:

query = M.Result.objects
query.filter("raceid__year" => 1990)
query.values("driverid__surname", "grid", "places_gained" => F("grid") - Avg("positionorder"))

SELECT "Tb_1"."surname" as "driverid__surname", "Tb"."grid" as "grid",
  ("Tb"."grid" - AVG("Tb"."positionorder")) as "places_gained"
FROM "result" as "Tb"
 INNER JOIN "driver" AS "Tb_1" ON "Tb"."driverid" = "Tb_1"."driverid"
 INNER JOIN "race" AS "Tb_2" ON "Tb"."raceid" = "Tb_2"."raceid"
WHERE "Tb_2"."year" = $1
GROUP BY 1, 2

A column grouped by any route satisfies the check: projected, named in order_by, or read by a window's partition_by. A transform is grouped by its own path or by its column, so on M.Driver, F("dob__@year") is covered by "dob__@year" or by "dob" in values(...). As with a correlated subquery, grouping by the primary key alone is not enough. PostgreSQL would accept that, but PormG does not infer the dependency. Before #798, PostgreSQL rejected these queries with a GroupingError, and SQLite ran them against an arbitrary row of each group.

A window function's argument is held to the same rule, even with no aggregate inside it: Lag("raceid__round") beside Sum("points") raises unless the query groups raceid__round. See A Window Over an Aggregate.

An ordering term is held to the same rule. In an aggregating query, a column named in order_by joins GROUP BY. An aggregate term does not, because it already has one value per group, and a window term contributes only the columns its partition_by and order_by read. Ordering by the alias (order_by("-best")) is the usual spelling; an expression term works too:

using PormG.QueryBuilder: SQLOrder, SQLField

query = M.Result.objects
query.values("constructorid__name", "best" => Max("points"))
query.order_by(SQLOrder(SQLField(Max("points"), "best_first"); orientation = "DESC"))

SELECT "Tb_1"."name" as "constructorid__name", MAX("Tb"."points") as "best"
FROM "result" as "Tb"
 INNER JOIN "constructor" AS "Tb_1" ON "Tb"."constructorid" = "Tb_1"."constructorid"
GROUP BY 1
ORDER BY MAX("Tb"."points") DESC NULLS FIRST

The second argument of SQLField is only a label, and ORDER BY never prints it. An expression term orders by its expression even when its label is the name of a projection: labelled "best", Count("resultid") still sorts by the count, not by the best column. To order by a projection, name it as a string: order_by("-best"). Before #1138, a label that matched a projection's name sorted by that projection and dropped the expression.

A term that mixes a column with an aggregate, such as F("grid") + Max("points"), raises unless the query groups grid. Before #1115, every one of these terms was added to GROUP BY whole, which both engines reject.

A Condition on an Aggregate Alias

A condition can also reach an aggregate by its alias. A When that reads an aggregate alias compares the aggregate itself, so the projection that holds it is an aggregate too. It is left out of GROUP BY, and a filter on its alias goes to HAVING:

using PormG.Functions: Case, Count, When

# Constructors with at least 100 wins
query = M.Result.objects
query.filter("positionorder" => 1)
query.values("constructorid__name", "wins" => Count("resultid"),
             "era" => Case([When("wins__@gte" => 100, then = "dominant")], default = "other"))
query.filter("era" => "dominant")

SELECT "Tb_1"."name" as "constructorid__name", COUNT("Tb"."resultid") as "wins",
  CASE WHEN COUNT("Tb"."resultid") >= $1 THEN $2::text ELSE $3::text END as "era"
FROM "result" as "Tb"
 INNER JOIN "constructor" AS "Tb_1" ON "Tb"."constructorid" = "Tb_1"."constructorid"
WHERE "Tb"."positionorder" = $4
GROUP BY 1
HAVING CASE WHEN COUNT("Tb"."resultid") >= $5 THEN $6::text ELSE $7::text END = $8

It returns Ferrari, McLaren, Mercedes, Red Bull and Williams. era is not in the GROUP BY, and the filter on it tests each constructor's count in HAVING.

The same holds for a Q(...) condition, a standalone When(…, otherwise = …), a wrapper around the Case, and a chain of aliases, where one projection reads an alias that itself reads the aggregate. An alias that projects a row expression, such as F("grid") - F("positionorder"), stays a row expression when a condition reads it, and is grouped. The alias must be declared earlier in values(...) than the condition that reads it.

Until #722, such a projection was put in GROUP BY, which both PostgreSQL and SQLite reject, and a filter on its alias went to WHERE.

A Row-Level Alias Filters in WHERE

Only an aggregate alias goes to HAVING. An alias over a row-level expression, such as arithmetic on columns or a bare F("col"), has one value per row, so a filter on it goes to WHERE, the same as a filter on a column:

# 2010 drives that gained 15 or more places from the grid
query = M.Result.objects
query.filter("raceid__year" => 2010)
query.values("driverid__surname", "raceid__name", "places_gained" => F("grid") - F("positionorder"))
query.filter("places_gained__@gte" => 15)

SELECT "Tb_1"."surname" as "driverid__surname", "Tb_2"."name" as "raceid__name",
  ("Tb"."grid" - "Tb"."positionorder") as "places_gained"
FROM "result" as "Tb"
 INNER JOIN "driver" AS "Tb_1" ON "Tb"."driverid" = "Tb_1"."driverid"
 INNER JOIN "race" AS "Tb_2" ON "Tb"."raceid" = "Tb_2"."raceid"
WHERE "Tb_2"."year" = $1 AND ("Tb"."grid" - "Tb"."positionorder") >= $2

When a query projects both kinds, one filter(...) call splits the same way: the row alias goes to WHERE and the aggregate alias to HAVING.

How an Alias Filter's Value Is Typed

A filter on an alias checks its value against the type of what the alias projects. The rule is the same whether the filter is written top-level or inside Q(...)/Qor(...):

• a column (F("grid"), Max("grid"), Coalesce("code", Value("-"))) has the column's type;
• a text function (Lower, Upper, Trim, Replace, Concat) is text;
• a Case with no output_field has the type its branches agree on, so Case(When(…, then = F("fastestlaptime"))) is a duration;
• a window value function (Lag, Lead, FirstValue, LastValue, NthValue) has its operand's type, and Rank, DenseRank and RowNumber are integers;
• a condition is a boolean: a comparison (F("grid") > 1), Exists(...), and a function over a Q(...)/Qor(...), such as Max(Q("grid__@gt" => 1));
• a Subquery(...) has the type of the one column it projects, so a subquery over a uuid column is a uuid;
• Sum, Count, Avg and arithmetic on numbers are numbers, and Sum/Avg of a duration is a duration;
• date and duration arithmetic has the type its SQL has: F("date") + Day(1) is a date, F("date") + Hour(1) a timestamp, F("date") - F("date") a whole number of days, F("start_at") - F("start_at") and F("fastestlaptime") * 2 durations;
• Cast(...), or a function given output_field = ..., has the type it names when that is a text, number (integer, int8, float8, numeric, …), boolean or date type. The SQL casts the value to that type on every engine (a boolean is ((x) <> 0) on MySQL, which casts to no boolean type), so the filter compares like with like. On PostgreSQL a uuid, inet or cidr type counts too, and a pattern lookup (@startswith, @contains, …) on such an alias reads it as text, as it does on a column of that type, because PostgreSQL has no LIKE for them. SQLite casts those three to text, so there the value is checked as text. For any other type, such as a timestamp on PostgreSQL, Coalesce, Greatest and Least fall back to their operand's type, and Cast and Case bind the value as given. On SQLite the cast itself refuses a timestamp (see Functions and Dates).

A value of the wrong type raises InvalidValueError, which names the alias but never the value. When PormG cannot tell what type an expression returns, for example a Case whose branches have different types, it binds the value as given. An expression on the right, such as F("grid") or Max("grid"), is not a value: it is compared as SQL, so filter("total_points__@gt" => F("raceid")) over "total_points" => Sum("points") renders HAVING SUM("Tb"."points") > "Tb"."raceid".

using PormG.Functions: Lower

# 2010 results for the driver whose surname, lower-cased, is "button"
query = M.Result.objects
query.filter("raceid__year" => 2010)
query.values("raceid__name", "surname_lc" => Lower("driverid__surname"), "points")
query.filter(Q("surname_lc" => "button"))

SELECT "Tb_1"."name" as "raceid__name", LOWER("Tb_2"."surname") as "surname_lc",
  "Tb"."points" as "points"
FROM "result" 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 (LOWER("Tb_2"."surname") = $2)

An alias over a literal, values("v" => Value(5)), is a row-level alias too: a filter on it compares two literals in WHERE, with both bound.

An Alias Named After a Field

An alias can repeat the name of a model field: values("points" => Sum("points")) is fine, and the result column is called points. A filter on that name, though, has two meanings: the points column in WHERE, or the projected sum in HAVING. PormG will not choose, and raises AmbiguousFieldError when the query is built. That applies top-level, inside Q/Qor, and with a lookup suffix:

query = M.Result.objects
query.values("driverid__surname", "points" => Sum("points"))
query.filter("points__@gt" => 100)   # AmbiguousFieldError

Rename the alias. The alias then filters the projection, and the field name filters the column:

# Drivers with more than 100 points from finishes worth 10 or more
query = M.Result.objects
query.values("driverid__surname", "total_points" => Sum("points"))
query.filter("points__@gte" => 10, "total_points__@gt" => 100)

WHERE "Tb"."points" >= $1
GROUP BY 1
HAVING SUM("Tb"."points") > $2

The same applies when the alias projects a different column under a field's name (values("points" => "grid")).

An alias cannot contain __ at all. PormG reads __ as a relation path ("driverid__surname") or a CTE column ("ev__points"), so an alias spelled that way would be read as one of those wherever it is filtered on. values(...) refuses it when it is declared, with QueryBuildError:

query = M.Result.objects
query.values("driverid", "season__points" => Sum("points"))   # QueryBuildError

Use a single underscore, such as "season_points". A path is still fine where it names a column: values("driverid__surname") and values("surname" => "driverid__surname") project the related column, and only the alias is checked. The same rule applies to the aliases passed to aggregate(...).

It also applies to a condition inside another projection, such as a When in a Case or a Q inside one. PormG raises AmbiguousFieldError there as well, whatever order the projections are declared in:

query = M.Result.objects
query.values("raceid",
             "podium_finish" => Case([When("points__@gte" => 15, then = 1)], default = 0),
             "points" => Sum("points"))   # AmbiguousFieldError: the column, or the sum?

Rename the alias, as above, and the condition reads the column. A projection whose own condition names it, such as "points" => Case([When("points__@gte" => 15, then = 1)], default = 0), is not ambiguous: inside the expression that defines it, the name can only mean the column.

A Transform Named Like a Related Path

A transform projection is named after its path with the @ dropped, as Django names it: values("date__@year") returns a column called date__year. When the field under the transform is a foreign key, that name can also be a real path.

A date part over a foreign key reads the key the column holds. On M.Result, raceid holds the race's integer id, so "raceid__@year" raises QueryBuildError when the query is built: an integer has no year. Read the year through the relation instead, with "raceid__date__@year" or "raceid__year". A foreign key whose key is a date is different. Take a calendar of race weekends keyed by date, with a year column of its own, and the sessions held on each weekend:

Weekend::Models.ModelType = Models.Model("weekend", weekendid = Models.IDField(),
                       date = Models.DateField(unique = true), year = Models.IntegerField())
Session::Models.ModelType = Models.Model("session", sessionid = Models.IDField(), name = Models.CharField(),
                       weekend = Models.ForeignKey(Weekend, pk_field = "date"))

session.weekend holds a date, so values("weekend__@year") is valid, and it returns a column named weekend__year. That is also the path to the weekend's year column.

The two spellings are kept apart. The spelling with @ is the transform, and the plain path is the related column, wherever you write them:

query = M.Session.objects
query.values("sessionid", "yr" => "weekend__@year")
query.filter("weekend__year" => 2009)   # the weekend's year: INNER JOIN "weekend" … WHERE "Tb_1"."year" = $1
query.order_by("weekend__@year")        # the transform, ordered by its name: ORDER BY "yr"

When the projection keeps its generated name, the plain path names two things: a column in the result and the related column. PormG will not choose, so a filter, a condition or an order_by on that name raises AmbiguousFieldError:

query = M.Session.objects
query.values("sessionid", "weekend__@year")
query.order_by("weekend__year")   # AmbiguousFieldError: the projection, or the weekend's year?

Write order_by("weekend__@year") for the projection. For the column, give the projection a name of its own, such as values("weekend_year" => "weekend__@year"). After that, "weekend__year" means the column.

A generated name that is not a path can still be ordered by. On M.Race, values("date__@day"); order_by("date__day") orders by the projection, because date__day reaches no related column. Filtering on that name raises AmbiguousFieldError, because a generated name is not a filter key. Filter "date__@day" instead.

A name you choose for a path or transform projection is not a filter key. values("yr" => "weekend__@year"); filter("yr" => 2009) raises UnknownFieldError. Filter "weekend__@year" instead.

A projection that is the column is not ambiguous. values("points"), values("points" => "points") and values("points" => F("points")) filter the column as usual.

Q and Qor on Aggregate Aliases

An aggregate alias inside Q(...) or Qor(...) goes to HAVING too. Qor is the only way to write an OR across aggregates, because separate filter(...) keys always combine with AND:

# Seasons with at least 20 races, or fewer than 10
query = M.Race.objects
query.values("year", "n" => Count("raceid"))
query.filter(Qor("n__@gte" => 20, "n__@lt" => 10))

SELECT "Tb"."year" as "year", COUNT("Tb"."raceid") as "n"
FROM "race" as "Tb"
GROUP BY 1
HAVING (COUNT("Tb"."raceid") >= $1 OR COUNT("Tb"."raceid") < $2)

A Q is an AND, so it can hold both kinds of condition. PormG splits it the same way it splits separate filter(...) keys: column conditions go to WHERE and filter rows before they are grouped, and aggregate conditions go to HAVING and filter the groups:

# Constructors with at least 100 wins
query = M.Result.objects
query.values("constructorid__name", "wins" => Count("resultid"))
query.filter(Q("positionorder" => 1, "wins__@gte" => 100))

WHERE "Tb"."positionorder" = $1
GROUP BY 1
HAVING COUNT("Tb"."resultid") >= $2

A Qor cannot be split this way, because a OR b does not mean "a in WHERE, b in HAVING". A Qor that mixes an aggregate alias with a column condition raises QueryBuildError when the query is built. If the column is one you group by, project it as an aggregate alias as well, so that every term in the Qor filters groups:

# Races with fewer than 20 results, plus race 1 whatever its count
query = M.Result.objects
query.values("raceid", "n" => Count("resultid"), "race" => Max("raceid"))
query.filter(Qor("n__@lt" => 20, "race" => 1))

HAVING (COUNT("Tb"."resultid") < $1 OR MAX("Tb"."raceid") = $2)

Grouping by raceid means Max("raceid") is the same value as the group's raceid. You only need it because an alias is how a value gets into HAVING.

Which Lookups Work on an Aggregate Alias

A filter on a projection alias renders through the same operator ladder as a filter on a column, so the comparison operators, @in / @nin, and the whole pattern family (@contains, @istartswith, @iendswith, …) mean the same thing in HAVING that they mean in WHERE — including the % decoration and the escaping of a % or _ you typed yourself:

# Seasons whose alphabetically-last Grand Prix name begins with "United"
query = M.Race.objects
query.values("year", "last_gp" => Max("name"))
query.filter("last_gp__@istartswith" => "United")

HAVING MAX("Tb"."name") ILIKE $1 ESCAPE '\'    -- bound: "United%"

PormG types the alias from the projection so it can validate the filter value. Max / Min and a bare F(...) take the type of the column they name, including a column reached through a relation or a CTE:

# Constructors whose alphabetically-last driver surname begins with "V"
query = M.Result.objects
query.values("constructorid__name", "last_driver" => Max("driverid__surname"))
query.filter("last_driver__@istartswith" => "V")

HAVING MAX("Tb_2"."surname") ILIKE $1 ESCAPE '\'    -- bound: "V%"

An aggregate over arithmetic (Max(F("points") + 1)) names no single column, so its alias is read as a number.

@range and @nrange work on an alias too. Both bounds are checked against the alias's type:

# Seasons with between 10 and 16 races
query = M.Race.objects
query.values("year", "n" => Count("raceid"))
query.filter("n__@range" => [10, 16])

HAVING COUNT("Tb"."raceid") BETWEEN $1 AND $2    -- bound: 10, 16

@isnull works on a Max, Min, Sum or Avg alias, and is true for a group in which every value is NULL:

# Seasons where no race has a recorded start time
query = M.Race.objects
query.values("year", "latest_start" => Max("time"))
query.filter("latest_start__@isnull" => true)

HAVING MAX("Tb"."time") IS NULL

On a Count alias it raises FilterError when the query is built, because COUNT never returns NULL (an empty group counts 0) and the filter could never match. Compare the count with => 0 instead.

Three further consequences worth knowing:

• The JSONB lookups are WHERE-only. On an alias they raise FilterError when the query is built, naming the lookup and the alias. Filter the underlying field instead.
• An operator that is PostgreSQL-only on a column is PostgreSQL-only on an alias too. @iunaccent_contains, @iunaccent_exact and the @regex family raise BackendCapabilityError on SQLite and MySQL from HAVING exactly as they do from WHERE.
• An operator PormG does not implement is refused when the query is built, naming the operator — it is never passed through to the database as a bare token.

Common Aggregation Patterns

Wins Per Constructor

df = M.Result.objects.
    filter("positionorder" => 1).
    values("constructorid__name", "wins" => Count("resultid")).
    order_by("-wins") |> DataFrame

Total Points Per Driver

df = M.Result.objects.
    values("driverid__surname", "total_pts" => Sum("points")).
    order_by("-total_pts").
    limit(20) |> DataFrame

Best Finish Per Driver at a Specific Circuit

df = M.Result.objects.
    filter("raceid__circuitid__name" => "Circuit de Monaco").
    values(
        "driverid__surname",
        "best_finish" => Min("positionorder"),
        "races" => Count("resultid")
    ).
    order_by("best_finish") |> DataFrame

Next Steps

• Functions and Dates — Use Case, Coalesce, Concat, date extraction, and math functions.
• Q Objects — Build complex OR/AND logic beyond what filter() pairs support.
• Field Expressions — Field-to-field comparisons with F() and aggregate ratios.