Values and Joins

This page covers column selection, relation traversal with __, join types, reverse joins, wildcard selection, aliases, and common patterns.

How Joins Work in PormG

PormG uses the double-underscore __ notation (inspired by Django) to traverse relationships. When you reference a field like driverid__surname, PormG:

1. Resolves driverid as a ForeignKey relationship on the model.
2. Creates the appropriate SQL JOIN automatically.
3. Selects the surname column from the joined Driver table.

You never write raw JOIN statements — PormG builds them from your model definitions.

Basic Field Selection

Select specific columns from the main table:

query = M.Result.objects
query.filter("statusid__status" => "Engine")
query.values("resultid", "statusid")
df = query |> DataFrame

This generates a simple SELECT ... FROM ... WHERE without any joins, since resultid and statusid are local columns on the Result table.

Joined Field Selection

Select columns from related tables using __:

query = M.Result.objects
query.filter("statusid__status" => "Engine")
query.values(
    "resultid",
    "driverid__forename",
    "constructorid__name",
    "statusid__status",
    "grid",
    "laps"
)
df = query |> DataFrame

PormG generates:

SELECT 
    "Tb"."resultid" as "resultid",
    "Tb_1"."forename" as "driverid__forename",
    "Tb_2"."name" as "constructorid__name",
    "Tb_3"."status" as "statusid__status",
    "Tb"."grid" as "grid",
    "Tb"."laps" as "laps"
FROM "result" as "Tb"
    INNER JOIN "driver" AS "Tb_1" ON "Tb"."driverid" = "Tb_1"."driverid"
    INNER JOIN "constructor" AS "Tb_2" ON "Tb"."constructorid" = "Tb_2"."constructorid"
    INNER JOIN "status" AS "Tb_3" ON "Tb"."statusid" = "Tb_3"."statusid"
WHERE "Tb_3"."status" = $1

# ✗ raises: driverid was not projected, and PormG won't lazily load it
row = M.Result.objects.values("resultid", "points").first()
driver_name = row.driverid.forename

# ✓ project the related column, then read it
row = M.Result.objects.values("resultid", "driverid__forename").first()
driver_name = row[:driverid__forename]

# ✓ or project the raw foreign-key value
row = M.Result.objects.values("resultid", "driverid").first()
driver_id = row[:driverid]

Multi-Level Joins

Chain __ segments to traverse multiple relationships:

query = M.Result.objects
query.filter("raceid__circuitid__country" => "Monaco")
query.values(
    "driverid__forename",
    "raceid__circuitid__name",
    "raceid__year"
)
df = query |> DataFrame

This traverses Result → Race → Circuit and Result → Driver, producing three joins:

SELECT 
    "Tb_1"."forename" as driverid__forename,
    "Tb_3"."name" as raceid__circuitid__name,
    "Tb_2"."year" as raceid__year
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"
INNER JOIN "circuit" AS "Tb_3" ON "Tb_2"."circuitid" = "Tb_3"."circuitid"
WHERE "Tb_3"."country" = $1

PormG deduplicates joins — if the same relationship is used in both filter() and values(), it creates only one JOIN.

Reverse Joins

When Model B has a ForeignKey pointing to Model A, you can traverse the relationship backwards from A to B:

# Constructor → Result (reverse: Result has FK to Constructor)
query = M.Constructor.objects
query.values("constructorid", "name", "result__points")
query.filter("result__positionorder" => 1) # Filter for winning constructors
df = query |> DataFrame

A path always ends at a column. values("result") or filter("result" => 1) names the relation itself, so it raises an UnknownFieldError that says result is a reverse relation and asks for a column after it, such as result__resultid. The same holds for a ManyToManyField, at any hop.

Naming Reverse Relations

By default, the name used to traverse backwards is the lowercase name of the source model (e.g., result for Result) — as long as that model declares exactly one relation to the target.

When a model declares two or more relations to the same target, one name cannot address them all, so PormG suffixes every member of that group with its own field name: <model>_<field>. No member keeps the bare model name, and PormG logs each derived name at @info when the models are registered.

# Incident points at Driver twice, so neither reverse accessor is the bare `incident`:
Incident::Models.ModelType = Models.Model("incident",
  id = Models.IDField(),
  causing_driver_id  = Models.ForeignKey(Driver, pk_field="id"),
  affected_driver_id = Models.ForeignKey(Driver, pk_field="id"),
)

query = M.Driver.objects
query.values("surname", "incident_causing_driver_id__lap")

The count spans relation kinds: a ManyToManyField to the same target counts toward the group too, which is why a model carrying both a self-ForeignKey and a self-ManyToManyField gets two distinct accessors instead of one collision.

Whenever you would rather read a name than derive it — and always when the group is larger than a pair — define a related_name on the field. An explicit name always wins:

# Model definition snippet
# "test_deletion" becomes the reverse traversal key from Result
test_result = Models.ForeignKey(Result, pk_field="resultid", related_name="test_deletion")

# Querying using the related_name
query = M.Result.objects
query.values("resultid", "test_deletion__name")

# ✗ both refused at the field, with a FieldValidationError
Models.ForeignKey(Driver, pk_field="id", related_name="incident__driver")
Models.ForeignKey(Driver, pk_field="id", related_name="incidents_")

# ✓ — an underscore inside the name is fine, and so is a leading one
Models.ForeignKey(Driver, pk_field="id", related_name="incident_driver")
Models.ForeignKey(Driver, pk_field="id", related_name="_incidents")

Chained Multi-Hop Reverse Joins

PormG supports traversing multiple relationships in reverse, seamlessly chaining backward hops. Continuing from the previous example, if a Just_a_nested_roll_back model has a FK to Just_a_test_deletion:

# Result ← test_deletion (related_name) ← just_a_nested_roll_back (lowercase model name)
query = M.Result.objects
query.filter("test_deletion__just_a_nested_roll_back__description" => "nested-value")
query.values("resultid", "test_deletion__just_a_nested_roll_back__id")

Forward and reverse hops mix in any order. A reverse hop can follow a forward one, and another reverse hop or a ManyToManyField can follow that. Every reverse hop is to-many, so a filter across one is refused unless the path is projected; the next section explains why.

Filtering or Ordering Across a To-Many Relation

A reverse relation or a ManyToManyField is to-many: one constructor has many results. A join across it repeats each base row once per related row. When you ask for those rows by projecting the path, the repeat is the answer, and this query returns one row per winning result:

# ✓ one row per (constructor, winning result): values() asks for the result rows
query = M.Constructor.objects
query.values("constructorid", "name", "result__points")
query.filter("result__positionorder" => 1)

A filter or an ordering on its own does not ask for them. Django renders it as the same join and returns each constructor once per win, which .distinct() then removes. PormG refuses it instead, so a silently repeated row never reaches list() or count() (#1002):

# ✗ raises FilterError: the filter's join would repeat each constructor once per winning result
query = M.Constructor.objects
query.filter("result__positionorder" => 1)
query.values("constructorid", "name")
query.list()   # FilterError: PormG cardinality check (#1002): a filter crosses the reverse relation 'result', …

Pick the spelling that says what you mean:

# ✓ each constructor once, when it has a winning result: a correlated EXISTS, no join
query = M.Constructor.objects
query.filter(Exists(M.Result.objects.filter("constructorid" => OuterRef("constructorid"), "positionorder" => 1)))
query.values("constructorid", "name")

# ✓ the same rows, by collapsing the repeats
query = M.Constructor.objects
query.filter("result__positionorder" => 1)
query.values("constructorid", "name")
query.distinct()

order_by("result__points") is refused the same way, and raises QueryBuildError. To order constructors by something about their results, compute one value per constructor in a correlated Subquery and order by that.

What is not refused. The rule is about rows the caller can see repeated:

• The same join, declared. A projection (values) or an on() / cjoin path that reaches the same join asks for its rows, as in the first example. Another path that reaches the same table through a different join does not.
• distinct(), and count(distinct = true).
• Questions a repeat cannot change: exists(), Exists(...) and an __@in subquery, and the rows update() and delete() act on. With an offset() (or a limit(), for __@in) the rows are counted, so the repeat matters again and the filter is refused.
• An aggregating query. Its rows are groups, so a repeat shows only inside an aggregate, and Count / Sum / Avg meet the fan-out guard instead. Max and Min are exact. An order_by across a to-many path that nothing else in the query declares is still refused there: the ordering joins GROUP BY, so it would split each group into one per related row, which distinct() cannot undo. Declared (projected, or named by on()), the path is a grouping key you asked for.
• A reverse OneToOneField, or a reverse ForeignKey declared unique = true: each parent has at most one child, so nothing repeats.

A forward relation can be to-many too: a ForeignKey whose pk_field, or a cjoin(field = …) link whose target column, is not declared unique on the target (its primary key, a unique = true field, or every field of a UniqueConstraint). A filter across such a ForeignKey is refused like a reverse one. A cjoin declares its own join, so a filter through it stands, but an aggregate over it meets the fan-out guard.

Wildcard Selection with *

Use "*" to select all columns from the main table, then add specific joined fields:

query = M.Result.objects
query.filter("driverid__nationality" => "Brazilian")
query.values("*", "driverid__surname", "driverid__forename")
df = query |> DataFrame

This selects every column from Result plus surname and forename from the joined Driver table.

Join Types

PormG selects join types based on your model's field definitions:

The join direction is always inferred from the relation path. For custom join behavior, see Custom Joins.

Filters that must see a parent with no related row. An __@isnull => true filter through a reverse or many-to-many relation asks for the parents with no row there, and a Qor arm across one can be matched by a parent through another arm. Both need the parent with no row, which an INNER JOIN drops before the WHERE clause runs, so PormG joins that relation LEFT (#1367). On the F1 data, where pit stops are recorded from 2011 on:

# Races with no recorded pit stop: every 2010 race
query = M.Race.objects
query.filter("year__@in" => [2010, 2011], "pit_stops__stop__@isnull" => true)
query.values("raceid").distinct()
# LEFT JOIN "pit_stops" AS "Tb_1" … WHERE … AND "Tb_1"."stop" IS NULL   → 19 races

A plain filter across the relation, and __@isnull => false, keep their INNER JOIN: they match only parents that have the row. A join type stated with .on(path, join_type = …) is kept as stated, on a hop after the promoted one too, where "INNER" drops the parent again. The same filters in delete() and update() reach the same parents.

Aliases in values()

Rename output columns using the "alias" => "field" pair syntax:

Simple Column Alias

query = M.Result.objects
query.filter("statusid__status" => "Finished", "resultid" => 26745)
query.values(
    "resultid",
    "circuit" => "raceid__circuitid__name"
)
df = query |> DataFrame
# DataFrame columns: :resultid, :circuit

Alias with Date Transforms

query = M.Result.objects
query.filter("statusid__status" => "Finished", "resultid" => 26745)
query.values(
    "resultid",
    "circuit" => "raceid__circuitid__name",
    "quarter" => "raceid__date__@quarter"
)
df = query |> DataFrame
# DataFrame columns: :resultid, :circuit, :quarter

Alias with Aggregates and F-Expressions

query = M.Result.objects
query.values(
    "driver" => "driverid__surname",
    "total_pts" => Sum("points"),
    "bonus" => F("points") * 0.1
)

Mixed-Case and Unicode Aliases

All aliases are quoted in the generated SQL (AS "alias", with backticks on MySQL), so the exact name you provide is preserved — including uppercase letters and Unicode characters:

df = M.Driver.objects.filter("driverref" => "hamilton").
    values("Surname" => "surname", "localização" => "nationality") |> DataFrame
# DataFrame columns: :Surname, Symbol("localização")
# PostgreSQL would fold these to lowercase without quoting

Ordering by an Alias

An alias declared in values() can be used as the sort key, whatever is on the right of the pair — a field path, a local column, an aggregate, or a window function:

query = M.Result.objects
query.values("resultid", "driver" => "driverid__surname")
query.order_by("driver")          # sorts on the projected alias

SELECT "Tb"."resultid" as "resultid", "Tb_1"."surname" as "driver"
FROM "result" as "Tb"
 INNER JOIN "driver" AS "Tb_1" ON "Tb"."driverid" = "Tb_1"."driverid"
ORDER BY "driver" ASC NULLS LAST

Ordering by the underlying path instead is equally valid and produces the same rows — it just renders the qualified column rather than the alias:

query.order_by("driverid__surname")     # ORDER BY "Tb_1"."surname" ASC NULLS LAST

query = M.Result.objects
query.values("resultid", "grid" => "points")
query.order_by("-grid")        # sorts by POINTS (the projected value), not by the grid column

Common Patterns

Filter by Related Fields

# All results for British drivers
query = M.Result.objects
query.filter("driverid__nationality" => "British")

Select Across Multiple Relations

# Full race result detail
query = M.Result.objects
query.values(
    "positionorder",
    "driverid__forename",
    "driverid__surname",
    "constructorid__name",
    "raceid__name",
    "raceid__circuitid__country"
)

Combine Multiple Joins in One Filter

Multiple filter pairs involving different joins are ANDed together:

# British drivers at Monaco
query = M.Result.objects
query.filter(
    "driverid__nationality" => "British",
    "raceid__circuitid__name__@icontains" => "monaco"
)

Aggregate Across Joins

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

Join Path in Filters Only (No Explicit values())

When you filter on a joined field but don't call values(), PormG still creates the join for the WHERE clause but selects * from the main table:

# Returns all Result columns, filtered by driver nationality
df = M.Result.objects.filter("driverid__nationality" => "British") |> DataFrame

Next Steps

• Filters and Aggregates — Learn about lookup operators (@gt, @in, @contains, …) and grouping.
• Custom Joins — Use .cjoin() for non-FK joins and .on() for ON-clause predicates.