Custom Join Documentation
Overview
PormG provides a way to create custom join conditions using the chainable .cjoin() method on query handlers.
The .cjoin() method is the recommended approach as it's more flexible and allows you to specify join conditions when building queries, rather than at model definition time.
.cjoin() Method
The .cjoin() method allows you to add custom conditions to JOIN clauses at query time. This is particularly useful for:
- Legacy databases where foreign key constraints don't exist
- Joining on non-ID fields (e.g., joining on codes, names, or other unique identifiers)
- Adding extra conditions to the ON clause beyond simple equality
- Complex multi-field joins (e.g., multi-tenant systems where you need to match both tenant_id and another field)
Key Features
- Runtime flexibility - Add join conditions when building queries
- ON clause conditions - Restrict the joined table during the JOIN itself
- Full Q/Qor/operator suffix support - Use the same filter syntax as .filter() for conditions that belong to the joined model
- Automatic field prefixing - Plain joined-model field names in join filters are automatically prefixed with the join path
- F expressions - Field-to-field comparisons in joins
- Multi-tenant support - Perfect for tenant isolation at join level
- Nested joins - Apply conditions to any level of join
Installation
The .cjoin() method is built into the query builder handler, meaning no special function imports are required once you have loaded PormG:
using PormG, LibPQ # load SQLite or MySQL instead for a SQLite or MySQL app
# Q, Qor, and F are automatically exported by PormGBasic Usage with .cjoin()
Setup: Create Test Data
First, let's set up some test data. The New_join_position model has a result field (IntegerField) that we'll join to the Result model:
# Clear and populate test data
delete(M.New_join_position.objects, allow_delete_all = true)
M.New_join_position.objects.create("result" => 1, "description" => "test 1")
M.New_join_position.objects.create("result" => 2, "description" => "test 2")
M.New_join_position.objects.create("result" => 3, "description" => "test 3")Simple Join
Create a custom join from an IntegerField to another model:
df = M.New_join_position.objects.
cjoin("result" => "Result").
values("result__statusid__status", "description", "result") |> DataFrameOutput:
3x3 DataFrame
Row | resultstatusidstatus description result
| String? String? Int32?
—–+————————————————
1 | Finished test 1 1
2 | Finished test 2 2
3 | Finished test 3 3
The `.cjoin()` call creates a LEFT JOIN from `new_join_position.result` to `Result.resultid`, allowing you to traverse the relationship chain `result__statusid__status`.
### Join with Filter Conditions (LEFT JOIN)
Add conditions to the ON clause using fields from the joined model. When a row doesn't match the ON condition, the joined fields will be `missing`:
julia df = M.Newjoinposition.objects. cjoin("result" => "Result", filters=["resultid" => 1]). values("resultstatusidstatus", "description", "result") |> DataFrame
# Output:
# 3x3 DataFrame
# Row | result__statusid__status description result
# | Union{Missing, String} String? Int32?
# -----+--------------------------------------------------
# 1 | Finished test 1 1
# 2 | missing test 2 2
# 3 | missing test 3 3
Notice that only the row whose joined `Result.resultid` is `1` has the status because the filter is applied in the ON clause, not WHERE. This returns all 3 rows, but only one row matches the join condition.
### Join with Filter Conditions (INNER JOIN)
Use `join_type="INNER"` to only return rows that match the join condition:
julia df = M.Newjoinposition.objects. cjoin("result" => "Result", filters=["resultid" => 1], join_type="INNER"). values("resultstatusidstatus", "description", "result") |> DataFrame
# Output:
# 1x3 DataFrame
# Row | result__statusid__status description result
# | String? String? Int32?
# -----+------------------------------------------------
# 1 | Finished test 1 1Only the row joined to Result.resultid = 1 is returned because the INNER JOIN excludes non-matching rows.
Join with Q/Qor Filters (AND/OR Logic)
Use Q() for AND logic and Qor() for OR logic in join conditions. Plain field names are automatically prefixed with the join path. A column of a further relation — the result's status — goes on that relation's own join, with on():
# "positionorder" is auto-prefixed onto the result join; the status predicate rides on the
# result__statusid join, which on() builds since values() already reaches it
df = M.New_join_position.objects.
cjoin("result" => "Result", filters=[
Q("grid__@gte" => 1, Qor("positionorder" => 1, "positionorder" => 2))
]).
on("result__statusid", "status" => "Finished").
values("result__statusid__status", "description") |> DataFrameJoin with Driver Model (Q/Qor Example)
Here's a complete example joining Result to Driver with complex filter logic:
# Add custom join with recursive Q/Qor filters + WHERE clause filter
# Plain field names ("nationality", "forename") are automatically prefixed
df = M.Result.objects.
cjoin("driverid" => "Driver", filters=[
Q("nationality" => "Brazilian", Qor("forename" => "Ayrton", "forename" => "Nelson"))
]).
filter("points" => 10).
values("driverid__surname", "points") |> DataFrameGenerated SQL (simplified):
SELECT ... FROM result AS Tb
LEFT JOIN driver AS Tb1 ON Tb.driverid = Tb1.driverid
AND (Tb1.nationality = ? AND (Tb1.forename = ? OR Tb_1.forename = ?))
WHERE Tb.points = ?
Notice that the cjoin filter parameters (`"Brazilian", "Ayrton", "Nelson"`) appear in the ON clause, while the regular filter parameter (`10` for points) appears in the WHERE clause.
## Understanding `.cjoin()` Behavior
### Contract of `.cjoin(filters=...)`
`.cjoin()` exists to modify the JOIN itself. Its `filters` are ON-clause predicates and should target fields on the joined model.
Use `.cjoin(..., filters=...)` when you want SQL like:
sql LEFT JOIN driver ON result.driverid = driver.driverid AND driver.nationality = ?
Use `.filter(...)` when you want to filter the main query rows in `WHERE`:
sql WHERE result.points > ?
This distinction matters:
- `.cjoin(filters=...)` is for joined-model predicates that belong in `ON`
- `.filter(...)` is for base-query predicates that belong in `WHERE`
- Passing base-table fields to `.cjoin(filters=...)` is not a good API contract and should be treated as unsupported usage
- A base-table value on the **right** side is supported: a right-side `F` names the base row, exactly as in `on()` (see [Which row each side of a condition names](#Which-row-each-side-of-a-condition-names))
### Dedicated `on()` API for Existing Join Paths
When the join path already exists through the model graph, you do not need to redefine it with `.cjoin()` just to add ON-clause predicates. Use the chainable `query.on()` method instead:
julia df = M.Result.objects. on("driverid", "nationality" => "Brazilian", "code" => "SEN"). values("resultid", "driverid__surname", "points") |> DataFrame
This keeps all base `Result` rows in the query tree while only attaching `Driver` rows that satisfy the ON predicates.
You can also override the join type directly from `query.on()`:
julia query = M.Result.objects. on("driverid", "nationality" => "Brazilian", join_type="INNER"). values("resultid", "driverid__surname")
This changes the join keyword to `INNER JOIN` while keeping the predicate in the `ON` clause instead of moving it to `WHERE`.
### Reverse Join Example with `on()`
Reverse joins are the main use case for the dedicated API because they let you preserve base rows while limiting which related rows attach.
julia df = M.Result.objects. on("testdeletion", "name@in" => ["reverse-join-a", "reverse-join-b"]). filter("resultid@in" => [1, 2, 3]). values("resultid", "testdeletion__name") |> DataFrame
The reverse join is `LEFT` here because that is what PormG derives for it, not because `on()` chose it — `on()` only adds the predicate. All three `Result` rows remain and only the matching reverse rows are attached. If you want only the matched base rows, pass `join_type="INNER"` on the same `on()` call.
!!! tip
**Chained Reverse Paths**: `on()` takes a chained reverse path too. Name the hop whose row the
predicate is about — `query.on("test_deletion__just_a_nested_roll_back", "description" =>
"nested-value")` puts the predicate in the nested table's `ON` clause, and builds that join if
nothing else in the query reaches it. Writing the column past the hop instead,
`on("test_deletion", "just_a_nested_roll_back__description" => …)`, raises `FilterError`
([#973](https://github.com/PingoLee/PormG.jl/issues/973)); see
[Which row each side of a condition names](#Which-row-each-side-of-a-condition-names).
### Contract of `on()`
- `query.on("path", ...)` targets a join path, including reverse joins such as `"test_deletion"` and nested paths such as `"raceid__circuitid"`
- **`on()` builds its join when nothing else in the query reaches the path** — no `values()`,
`filter()` or `order_by()` through it. Before [#977](https://github.com/PingoLee/PormG.jl/issues/977)
such an `on()` was dropped from the statement with no error, `join_type="INNER"` included, so a
predicate meant to restrict rows restricted nothing. The join it builds is the one traversal would
build, with the type PormG derives for it: a reverse path repeats a base row once per matching child,
as `values("test_deletion__name")` would, and where that join is `INNER` (a `NOT NULL` ForeignKey) a
base row with no matching child is dropped. A query that aggregates over such a to-many join now
meets the [#74](https://github.com/PingoLee/PormG.jl/issues/74) fan-out guard
- a path that crosses a `ManyToManyField` raises `QueryBuildError`: that join goes through a link
table PormG does not attach an `ON` predicate or a `join_type` to, and it used to drop them
silently. Put the predicate in `.filter(...)` instead
- multiple predicates are combined with `AND` unless you use `Qor(...)`
- repeated `on()` calls for the same path merge additional predicates into the same `ON` clause
- **`on()` does not change the join type unless you pass `join_type=`.** Without it the join keeps
the type PormG derives from the relation itself — the field's own `how`, else `LEFT` for a
nullable ForeignKey and `INNER` for a `NOT NULL` one, and `LEFT` for a reverse relation a
filter's `Qor` arm or `__@isnull => true` reads ([#1367](https://github.com/PingoLee/PormG.jl/issues/1367)).
A `join_type=` you pass is kept even then. Before
[#474](https://github.com/PingoLee/PormG.jl/issues/474) an `on()` with no `join_type` forced
`LEFT`, so adding a predicate to a `NOT NULL` relation silently widened the result set
- an explicit `join_type` on any `on()` call for a path stays in effect for later `on()` calls on
that same path
- `.filter(...)` keeps its existing `WHERE` semantics and is not silently rewritten into `ON`
### Which row each side of a condition names
In an `on()` or `cjoin(filters = …)` condition:
- **The key, and every column on the left side, names the joined row.** PormG prefixes it with the
join path, so `"number"` in `on("driverid", …)` is the driver's `number`. That holds for a key
inside `Q(...)`/`Qor(...)`, for a key with a transform (`"dob__@year"`), and for every column inside
a left-side expression or function (`Abs(F("number")) > 0`), whatever `values()` selects.
- **A right-side `F` names the base row**, the query's own model, in every spelling: a bare pair,
`Q(...)`, `Qor(...)`, `OP(...)` or `F(...) == F(...)`, and at any hop depth. On a deep hop, a table
earlier on the same path is reached through its path from the base model: in
`on("driverid__result", …)`, `F("driverid__number")` is the driver's `number`.
- **A left side stays on its hop.** A key whose relation part goes past the join path —
`on("driverid", "result__grid" => 1)`, or `cjoin("raceid" => "Race", filters =
["circuitid__country" => "Italy"])` — raises `FilterError`, at the call (or, for a path through a
`cjoin(field = …)` link, when the query is built). It names a row of a further
relation, and PormG used to add that relation's join silently, with the predicate in its `ON`
clause; for a reverse relation that join repeated base rows. Write the predicate on the hop that
owns the column, `on("raceid__circuitid", "country" => "Italy")` (`on()` builds that join when
nothing else reaches it), or put it in `.filter(...)` to restrict rows. Before
[#973](https://github.com/PingoLee/PormG.jl/issues/973) the deep key was accepted.
- **A right side stays on the path.** A right-side column — an `F`, a column string, a subquery's
`OuterRef`, an `Exists(...)`'s `OuterRef` — may name the base row, the joined row or a table earlier
on the same path. One that reaches any other relation raises `FilterError` when the query is built
([#962](https://github.com/PingoLee/PormG.jl/issues/962)): that relation's join is not in scope at
this `ON` clause, so put the predicate in `.filter(...)`.
That is what lets a join condition compare the two tables. For example, these are the results where
the car number is the driver's permanent number:
julia df = M.Result.objects. on("driverid", "number" => F("number"), join_type = "INNER"). values("resultid", "number", "driveridcode", "driveridnumber") |> DataFrame
INNER JOIN "driver" AS "Tb1" ON "Tb"."driverid" = "Tb1"."driverid"
AND "Tb_1"."number" = "Tb"."number"
`Q("number" => F("number"))` and `F("number") == F("number")` render the same predicate. Before
[#958](https://github.com/PingoLee/PormG.jl/issues/958), every spelling except the bare pair also
prefixed the right side. That rendered `"Tb_1"."number" = "Tb_1"."number"`, which is true on every
row, so the condition was silently dropped. Before
[#961](https://github.com/PingoLee/PormG.jl/issues/961), the left side had the mirror-image gap: a key
inside `Q(...)` named the base row when `values()` also selected a base column of the same name, or
when the key carried a transform, and so did a function on the left of an `F` comparison.
A right side may not reach any **other** relation. On `on("constructorid", …)`, the right side
`F("driverid__nationality")` names the driver, a relation beside the constructor rather than on its
path. That raises `FilterError` when the query is built, wherever the column sits on a right side:
in an `F`, inside a function or a `Case` branch, as a subquery's `OuterRef`, or on the right of a
comparison nested in the left side, such as a `When` condition. SQL can only hold such a
predicate in the constructor's `ON` clause if the driver's join comes first. PormG would have to move
it onto another join, and which one would depend on the order of `values()`. Compare the two
relations in `.filter(...)` instead:
julia df = M.Result.objects. filter(F("constructoridnationality") == F("driveridnationality")). values("resultid", "constructoridname", "driveridsurname") |> DataFrame
### A `.cjoin()` always joins
A `.cjoin()` (or an `on()`) emits its join whether or not `values()` reaches the path
([#977](https://github.com/PingoLee/PormG.jl/issues/977)). Its `filters` are `ON` conditions of that
join, so under the default `LEFT` join they decide which *joined* row matches, never which base rows
you get:
julia df = M.Newjoinposition.objects. cjoin("result" => "Result", filters = ["resultid" => 1], warn = false). values("description", "result", "resultstatusidstatus") |> DataFrame
3 rows: resultstatusidstatus is "Finished" for result 1 and missing for the other two
sql SELECT "Tb"."description" as "description", "Tb"."result" as "result", "Tb2"."status" as "resultstatusidstatus" FROM "newjoinposition" as "Tb" LEFT JOIN "result" AS "Tb1" ON "Tb"."result" = "Tb1"."resultid" AND "Tb1"."resultid" = 1 LEFT JOIN "status" AS "Tb2" ON "Tb1"."statusid" = "Tb_2"."statusid"
Pass `join_type = "INNER"` to keep only the base rows whose joined row matches (one row here). To
restrict the base table itself, use `.filter(...)`:
julia query = M.Newjoinposition.objects. cjoin("result" => "Result", filters = ["resultid" => 1], warn = false). filter("description" => "test 1"). values("resultstatusidstatus", "description", "result")
### Generated SQL
You can inspect the generated SQL using `show_query`:
julia query = M.Newjoinposition.objects. cjoin("result" => "Result", filters=["resultid" => 1]). values("resultstatusidstatus", "description", "result")
@info query |> show_query
Output:
SELECT
"Tb_2"."status" as resultstatusidstatus,
"Tb"."description" as description,
"Tb"."result" as result
FROM "newjoinposition" as "Tb"
LEFT JOIN "result" AS "Tb1" ON "Tb"."result" = "Tb1"."resultid"
AND "Tb_1"."resultid" = $1
LEFT JOIN "status" AS "Tb2" ON "Tb1"."statusid" = "Tb_2"."statusid"
## Full-Control ON Clauses with `.cjoin_on()` (#45)
`.cjoin()` and `.on()` always emit a base equi-anchor (`main.field = target.pk`) and AND-append
filters that must target the **single joined model**. When you need the *entire* ON clause to be
your own — an arbitrary boolean (top-level `OR`), field-to-field comparisons across **both** sides
(a self-join), or SQL functions in the ON — use **`.cjoin_on()`**.
julia query.cjoinon(M.Model; alias="b2", on=[ ... ], jointype="INNER")
- **`M.Model`** — the model to join, as the model object (may be the query's **own** model, for a
self-join). The model's **name** is accepted too — `cjoin_on("Model"; …)` — and is resolved in
the query's models module; prefer the object, so a typo is an `UndefVarError` from Julia at the
call rather than a `QueryBuildError` at run time. A model registered on a **different connection**
from the one the query runs on (a `.db("key")` override counts) is refused when the query is built,
with a `QueryBuildError`: the join would name a table that lives in another database
([#488](https://github.com/PingoLee/PormG.jl/issues/488)).
- **`alias`** — the SQL alias for the joined copy. Reference its columns as `Joined(alias, "column")`.
- **`on`** — the expressions that form the **entire** ON clause. No equi-anchor is added. An
aggregate or a window function is refused here — see
[Aggregates and Window Functions in an ON Clause](#Aggregates-and-Window-Functions-in-an-ON-Clause).
- **`join_type`** — defaults to `"INNER"`.
### Reference convention
Inside `on`, the two sides of the join are named by how you write the reference:
| You write | Resolves to |
|-----------|-------------|
| `F("col")` (bare) | the **base/main** table (the query's own alias) |
| `Joined("b2", "col")` | the **joined copy** declared by this `cjoin_on` (its `alias`) |
This is unambiguous even in a self-join, where both sides share every column name.
[`Joined(alias, path)`](@ref Joined) is a reference **object**, not a string, and it works in every
clause rather than only inside `on`: project it in `values(...)`, compare it in `filter(...)`, sort
by it in `order_by(...)`, and name **another** `cjoin_on`'s alias from inside one `on` list.
!!! warning "The dotted-string spelling was removed"
`F("alias.col")` is gone (#481). It could not carry an operator suffix, the bare string form
could not be projected (`values("b2.sku")` raised), and a typo in the alias reported an unknown
*field* called `"typo.col"` instead of an unknown alias. Rewrite `F("b2.sku")` as
`Joined("b2", "sku")`; see [Upgrading](../upgrading.md).
### Self-join example
Find, for each lap, other laps in the same race — or the same driver+lap — or the same calendar
year of the timestamp:
julia query = M.Lap.objects query.cjoinon(M.Lap; alias="b2", jointype="INNER", on=[ Qor( Joined("b2", "raceid") == F("raceid"), Q(Joined("b2", "driverid") == F("driverid"), Joined("b2", "lap") == F("lap")), Joined("b2", "dt@year") == F("dt@year"), # year() on both sides — dialect-aware ), ]) query.values("id")
**Generated SQL (SQLite** — PostgreSQL renders `$n` placeholders and `EXTRACT(YEAR FROM …)::integer`, MySQL backtick-quoted names and `EXTRACT(YEAR FROM …)`**):**
sql SELECT "Tb"."id" as "id" FROM "laps" as "Tb" INNER JOIN "laps" AS "b2" ON ( ("b2"."raceid" = "Tb"."raceid") OR (("b2"."driverid" = "Tb"."driverid") AND ("b2"."lap" = "Tb"."lap")) OR (CAST(strftime('%Y', "b2"."dt") AS INTEGER) = CAST(strftime('%Y', "Tb"."dt") AS INTEGER)) )
No `main.field = target.pk` anchor is emitted — the `on` expression **is** the ON clause. Bound
parameters from `on` route to the JOIN clause (ahead of any WHERE parameters).
!!! note "What a `Joined(...)` reference can do"
Since #481 the joined copy is referenced by a typed handle, and three limitations this note used
to list are gone:
- **Alias-qualified operator pairs work.** `filter(Joined("b2", "col__@gte") => 3)` compares the
joined side against a literal; every operator the ordinary pair path accepts works here, since
it *is* that path.
- **A third table can be referenced.** One `cjoin_on`'s `on` may name another's alias —
`Joined("d2", "surname") == Joined("d1", "surname")` — in either declaration order: PormG
emits the alias it names first. See the warning below.
- **A joined column projects in every form.** `values("who" => Joined("d", "surname"))` and the
bare `values(Joined("d", "surname"))` both work; the bare dotted string never did.
An `on` predicate may also name a relation path, `F("raceid__circuitid__country")`: PormG joins
that path *before* the `cjoin_on`, and the predicate stays in this `ON` clause, where you wrote
it ([#982](https://github.com/PingoLee/PormG.jl/issues/982)).
!!! warning "A `cjoin` path or an `on()` path is not the same case"
Only a `cjoin_on` **alias** is free of the collision. A `cjoin` **path** and an `on()`
**path** name a relation *on the model*, so a CTE sharing that name collides with the
relation exactly as it would with any field, and the shared `__` path raises
`AmbiguousFieldError` ([#492](https://github.com/PingoLee/PormG.jl/issues/492)):
```julia
driver_totals = M.Result.objects.values("driverid", "n" => Count("resultid"))
q = M.Result.objects
q.with("driverid" => driver_totals, join_field = "driverid" => "driverid")
q.cjoin("driverid" => "Driver") # a `cjoin` PATH — a relation on Result
q.values("points", "driverid__surname") # → AmbiguousFieldError
```
Here the projection wants the ForeignKey's `surname`, so the fix is to **rename the CTE** —
while the name is taken there is no string that selects the model side. Had you wanted the
CTE's own column instead, that one does have a spelling: `CTE("driverid", "n")`.
**A CTE cannot be referenced from an `ON` clause at all.** A `CTE(name, path)` inside `on`
raises `FilterError`, and so does the equivalent `"<cte>__<column>"` string
([#492](https://github.com/PingoLee/PormG.jl/issues/492)): an `ON` clause targets the joined
*model*, and a CTE is joined by its own `.with(...)` declaration. Put the predicate in
`.filter(...)` instead (#444).
**A `cjoin_on` alias may share a name with a CTE the query joins.** An alias lives in its own
namespace ([#484](https://github.com/PingoLee/PormG.jl/issues/484)) and is reached *only*
through `Joined(alias, column)` — never as a `__` path — so a `"<cte>__<column>"` string is
unambiguously the CTE, and both relations are emitted and addressable:
```julia
# "best" names both the CTE and the cjoin_on alias here.
best = M.Result.objects.values("driverid", "best" => Min("positionorder"))
df = M.Result.objects.
with("best" => best, join_field = "driverid" => "driverid", join_type = "INNER").
cjoin_on("Driver", alias = "best", on = [Joined("best", "driverid") == F("driverid")]).
values("points", "career_best" => CTE("best", "best")). # or "best__best"
filter("raceid" => 18).
order_by("-points").
limit(5) |> DataFrame
```
```sql
-- Both are emitted. The CTE is joined under a GENERATED alias, so only one relation in the
-- statement is actually named "best".
WITH "best" AS (
SELECT "Tb"."driverid" as "driverid", MIN("Tb"."positionorder") as "best"
FROM "result" as "Tb" GROUP BY 1
)
SELECT "R1"."points" as "points", "R1_1"."best" as "career_best"
FROM "result" as "R1"
INNER JOIN "best" AS "R1_1" ON "R1"."driverid" = "R1_1"."driverid"
INNER JOIN "driver" AS "best" ON ("best"."driverid" = "R1"."driverid")
WHERE "R1"."raceid" = ?
ORDER BY "points" DESC NULLS FIRST
LIMIT ?
```
Until [#474](https://github.com/PingoLee/PormG.jl/issues/474) this was refused, because join
resolution looked a CTE hop up in the join-config map under the CTE's own name — so a collision
handed the CTE the other entry's join type and predicates and dropped your join. That lookup is
gone, so a join KEY no longer collides.
**A `cjoin_on` alias may also equal a relation name on the base model**, or an `on()` / `cjoin()`
join path. Both joins are emitted — the relation's under a generated alias, the joined copy's
under the alias you declared — and each stays addressable: `Joined(alias, column)` reaches the
copy, `"relation__column"` reaches the relation. `on(...)` and `cjoin(...)` decorate the
*relation's* join, never the copy's, in either declaration order.
```julia
# "driverid" names both the ForeignKey on Result and the cjoin_on alias.
df = M.Result.objects.
cjoin_on("Driver", alias = "driverid", join_type = "LEFT",
on = [Joined("driverid", "driverid") == F("driverid")]).
filter("raceid" => 841).
values("points", "fk" => "driverid__surname",
"copy" => Joined("driverid", "surname")) |> DataFrame
```
```sql
-- Two joins, each with its own alias, its own ON clause and its own join type: the ForeignKey's
-- keeps what PormG derives from the relation (INNER — `driverid` is NOT NULL), the joined copy
-- keeps the LEFT it declared. Neither adopts the other's.
SELECT "Tb"."points" as "points", "Tb_1"."surname" as "fk", "driverid"."surname" as "copy"
FROM "result" as "Tb"
INNER JOIN "driver" AS "Tb_1" ON "Tb"."driverid" = "Tb_1"."driverid"
LEFT JOIN "driver" AS "driverid" ON ("driverid"."driverid" = "Tb"."driverid")
WHERE "Tb"."raceid" = ?
```
Before [#484](https://github.com/PingoLee/PormG.jl/issues/484) this shape was silently wrong
whenever the relation was *also* traversed: the alias's predicates were AND-appended to the
ForeignKey's `ON`, its join type adopted, its own join never emitted — leaving the statement
referencing a range variable it never declared (PostgreSQL: *missing FROM-clause entry*). A
`cjoin_on` alias and a join path are now two namespaces, so the overlap is simply two relations
that happen to share a name.
One name a CTE may never take is a **physical table name**: `.with("driver" => ...)` raises
`QueryBuildError` at the call when `driver` is the `db_table` of any registered model, or a
many-to-many join table. SQL resolves an unqualified table reference to a same-named CTE for the whole statement,
so the `driver` join PormG generates for `driverid__surname` would silently read the CTE
([#479](https://github.com/PingoLee/PormG.jl/issues/479)). Pick a name that is not a table.
`cjoin_on` works in reads and in `update()`/`delete()`, which scope rows via a subquery. An
`update()` can also **set a column from the joined copy**, `update("x" => Joined("d", "col"))`
([Setting a column from a `cjoin_on` copy](../write/update.md#Setting-a-column-from-a-cjoin_on-copy)).
!!! note "When PormG treats a `cjoin_on` join as to-many"
An `ON` clause can say anything, so PormG decides from the conditions alone whether the join can
match **more than one** target row per base row
([#174](https://github.com/PingoLee/PormG.jl/issues/174)). The join is **to-one** only when
top-level conditions (the `on` list, or a `Q(...)` in it, never a `Qor(...)` branch) use `==` or
`=>` to pin a **key** of the target to **one value per base row**:
- the key is the target's primary key, a `unique = true` column, or every column of a
`UniqueConstraint` declared without `condition`/`expressions`;
- the value is a literal, a base column or a forward path off it (`F("driverid")`,
`F("raceid__circuitid")`), an `OuterRef`, or a column of another alias that is itself to-one.
Anything else counts as **to-many**: a range (`>=`), a `Qor`, arithmetic, or a column the
model never declared unique, even when your data happens to be one-to-one. Two features read this:
- the [fan-out guard](filters_and_aggregates.md#Aggregating-Across-To-Many-Relations-(Fan-Out-Guard))
raises `QueryBuildError` for `Count`/`Sum`/`Avg` over a base column next to a to-many
`cjoin_on`, as it does for a reverse foreign key;
- an `update()` that sets a column from a joined copy refuses a to-many alias.
If the column really is unique, declare it (`unique = true` or a `UniqueConstraint`) and the
join becomes to-one. On a managed model that is a schema change: the next migration adds the
constraint, and fails if the table holds duplicates. The declaration is taken on its word, so a
key declared on an unmanaged model or a view is trusted without a constraint behind it.
!!! warning "Give the join a predicate that names its own alias"
An `ON` clause that never names its own alias is **refused**
([#448](https://github.com/PingoLee/PormG.jl/issues/448)). `on = ["points__@gt" => 10]` puts no
condition on the join at all, so every joined row would pair with every matched base row:
```julia
query = M.Result.objects
query.values("points")
query.cjoin_on("Driver", alias = "d", on = ["raceid__circuitid__country" => "Italy"])
# ERROR: The ON clause built for d never references d, …
```
Add a predicate that names it (`Joined("d", "driverid") == F("driverid")`), or, if none of the
conditions was ever about this join, move them to `.filter(...)` and drop the `cjoin_on`.
**An `ON` clause may name relation paths and other aliases, in any declaration order.** PormG
binds every `cjoin_on` before it renders anything
([#982](https://github.com/PingoLee/PormG.jl/issues/982)):
- **A path it names is joined first**, and the predicate stays in the `cjoin_on`'s own `ON`
clause. Under `join_type = "LEFT"` the predicate decides which rows of the joined copy match,
and drops no base row itself. The path's own join is the one any reference to it builds, so a
nullable foreign key on the way is a `LEFT JOIN` and a required one an `INNER JOIN`.
- **The path must be to-one** — forward foreign keys only. A path that crosses a reverse relation
(`driverid__result__grid`), a `ManyToManyField` or a `ForeignKey` whose `pk_field` names a column
the target does not declare unique raises `FilterError` ([#992](https://github.com/PingoLee/PormG.jl/issues/992),
[#1002](https://github.com/PingoLee/PormG.jl/issues/1002)): joined onto the base row, it would
repeat that row once per related row, whatever the `cjoin_on`'s `join_type`. A reverse
`OneToOneField` is refused too: it repeats nothing, but its `INNER JOIN` drops a base row with no
match, which a `LEFT` `cjoin_on` was written to keep. `distinct()` does not lift this refusal,
since it cannot restore a dropped row; projecting the same path does, because the query then asks
for that join itself — and so does a `cjoin(field = …)` link, which declares its own join even to a
non-unique column. To match on whether a related row exists, correlate a subquery instead:
```julia
# Each result, with d2 matched only for drivers who have started a race from pole.
M.Result.objects.
cjoin_on("Driver", alias = "d2", join_type = "LEFT",
on = [Joined("d2", "driverid") == F("driverid"),
Exists(M.Result.objects.filter("driverid" => OuterRef("driverid"), "grid" => 1))]).
values("resultid", "pole_sitter" => Joined("d2", "surname"))
```
- **An alias it names is emitted first.** Aliases that name no other alias keep the order you
declared them in.
```julia
# d1 names d2, which is declared after it: PormG emits d2 first.
df = M.Result.objects.
cjoin_on("Driver", alias = "d1", on = [Joined("d1", "surname") == Joined("d2", "surname")]).
cjoin_on("Driver", alias = "d2", on = [Joined("d2", "driverid") == F("driverid")]).
filter("raceid" => 841).
values("points", "namesake" => Joined("d1", "forename")) |> DataFrame
```
```sql
SELECT "Tb"."points" as "points", "d1"."forename" as "namesake"
FROM "result" as "Tb"
INNER JOIN "driver" AS "d2" ON ("d2"."driverid" = "Tb"."driverid")
INNER JOIN "driver" AS "d1" ON ("d1"."surname" = "d2"."surname")
WHERE "Tb"."raceid" = ?
```
Two aliases whose `ON` clauses name each other have no order that emits both first, and raise
`QueryBuildError` naming the two.
Until #982 a predicate naming a later join was *moved* onto that join, out of the `ON` clause
you wrote it in. When every predicate moved, the `cjoin_on` was left with no `ON` clause and
PormG raised ([#435](https://github.com/PingoLee/PormG.jl/issues/435)). And until
[#449](https://github.com/PingoLee/PormG.jl/issues/449) the emission order came from hashing the
alias *strings*.
If you genuinely want a cross product, declare the table as an **unkeyed** CTE and **reference
it** — declaring it alone emits no join at all:
```julia
q.with("all_drivers" => M.Driver.objects.values("driverid", "surname")) # no join_field
q.values("points", "who" => CTE("all_drivers", "surname")) # the reference is
# what joins it
q.limit(100) # illustrative: this is drivers × results, so bound it
```
That renders a real `CROSS JOIN` and warns that it is Cartesian (#44), so the intent is visible
in the query and in the log rather than hidden in an `ON` clause.
**`join_type = "CROSS"` is not the way to ask for this.** It raises `QueryBuildError` on
`cjoin`, `cjoin_on`, `on()` and `.with()` alike. Every one of those renders
`<join_type> JOIN <table> AS <alias> ON <clause>`, so a `CROSS` there could only ever build
`CROSS JOIN … ON …`, which PostgreSQL and SQLite both reject and MySQL reads as an inner join — and on `cjoin_on`, where at
least one `ON` predicate is required (#448), the alternative would have been to discard the
predicates you wrote. The unkeyed-and-referenced `.with(...)` above is the supported spelling
([#474](https://github.com/PingoLee/PormG.jl/issues/474)).
## Use Cases
### 1. Legacy Databases Without Foreign Keys
When your database doesn't have proper foreign key constraints:
julia
Join on a code field instead of ID
query = M.Order.objects. cjoin("productcode" => "Product"). # Joins on productcode = Product.code values("product_code__name", "quantity")
### 2. Multi-Tenant Systems
Add tenant isolation at the join level:
julia query = M.Invoice.objects. cjoin("customerid" => "Customer", filters=["tenantid" => currenttenantid]). values("customer_id__name", "amount")
### 3. Conditional Joins with Complex Logic
Join only when certain conditions are met using Q/Qor:
julia query = M.Result.objects. cjoin("driverid" => "Driver", filters=[Q("nationality" => "British", Qor("code" => "HAM", "code" => "BUT"))], join_type="INNER"). values("driveridforename", "driveridsurname", "points")
This creates: `ON ... AND (driver.nationality = ? AND (driver.code = ? OR driver.code = ?))`
### 4. Driver Filtering (Real-World Example)
Find results for drivers of a specific nationality with specific names:
julia
Add both a custom join condition and a regular filter
query = M.Result.objects. cjoin("driverid" => "Driver", filters=[ Q("nationality" => "Brazilian", Qor("forename" => "Ayrton", "forename" => "Nelson")) ]). filter("points@gt" => 0). # Regular WHERE clause filter values("driveridforename", "driverid__surname", "points")
df = query |> DataFrame
Result: Only Brazilian drivers named Ayrton or Nelson with points > 0
## API Reference
julia query.cjoin(mainjoin; filters=[], field=nothing, jointype="LEFT")
### Arguments
| Argument | Type | Description |
|----------|------|-------------|
| `main_join` | `Pair{String, String}` | Field name => Target model name (e.g., `"result" => "Result"`) |
| `filters` | `Vector` | Optional conditions for the ON clause. Supports `Pair`, `Q()`, `Qor()`, operator suffixes, or F expressions. Plain field names in filters are automatically prefixed with the join path. |
| `field` | `PormGField` | Optional custom field definition |
| `join_type` | `String` | Join type: `"LEFT"`, `"INNER"`, `"RIGHT"`, `"FULL"` (default: `"LEFT"`). `"CROSS"` raises `QueryBuildError`; see the cross-product note in the `cjoin_on` section above. `"RIGHT"` and `"FULL"` are capability rows (`right_joins`, `full_joins`): MySQL has no `FULL JOIN`, and SQLite has neither before 3.39.0 ([Backends](../backends.md)) |
### Filter Types in `.cjoin()`
- **Pair filters**: `"field" => value` - Plain field names are automatically prefixed (e.g., `"nationality"` → `"driverid__nationality"`)
- **Q filters**: `Q("field1" => val1, "field2" => val2)` - AND logic; plain field names are prefixed recursively
- **Qor filters**: `Qor("field1" => val1, "field2" => val2)` - OR logic; plain field names are prefixed recursively
- **Operator suffix filters**: Complex operator-based filters using the suffix operator system (e.g., `"nationality__@ne" => "British"`)
- **F expressions**: Field-to-field comparisons (e.g., `F("field1") == F("field2")`). In `on()` / `cjoin(filters = …)` the left `F` names the joined row and the right one the base row; see [Which row each side of a condition names](#Which-row-each-side-of-a-condition-names)
## Important Notes
### Parameter Placement
When using `.cjoin()` with filters:
- **Join filter parameters** (from `filters=`) are placed in the **ON clause** of the JOIN and should reference joined-model fields
- **Regular filter parameters** (from `.filter()`) are placed in the **WHERE clause**
This is important for query efficiency and correctness:
julia
This parameter goes to WHERE clause, cjoin filters go to ON clause
query = M.Result.objects. filter("points" => 10). cjoin("driverid" => "Driver", filters=["nationality" => "Brazilian"])
Result SQL:
... ON driver.driverid = result.driverid AND driver.nationality = ?
WHERE result.points = ?
### Aggregates and Window Functions in an ON Clause
A join condition cannot contain an aggregate (`Count`, `Sum`, `Max`, …) or a window function
(`Rank`, `Lag`, …), on either side of the comparison: SQL joins rows before it groups them and
evaluates windows after the join. PostgreSQL and SQLite reject such a query, and PormG refuses it on every engine when the
query is built, with a `QueryBuildError`. That holds for all three spellings: `.cjoin_on(...; on=...)`,
`.cjoin(...; filters=...)` and `.on(...)`
([#917](https://github.com/PingoLee/PormG.jl/issues/917)).
julia
Refused: an aggregate in the ON clause.
M.Result.objects. cjoin_on(M.Result; alias="r2", on=[Joined("r2", "raceid") == F("raceid"), Count("resultid") > 1])
Compute the value one query level down instead, in a CTE, then join on the column it becomes. For
example, every result from a race with more than 30 entries:
julia df = M.Result.objects. with("racesize" => M.Result.objects.values("raceid", "n" => Count("resultid")), joinfield="raceid" => "raceid"). filter("racesizen@gt" => 30). values("resultid", "raceid", CTE("racesize", "n")) |> DataFrame
An `@in` subquery in a join condition may aggregate, and so may an `Exists(...)` in a `cjoin_on`
condition (the other two spellings do not accept `Exists`): its aggregate belongs to the inner query.
### Field Name Normalization
When you provide plain field names in `.cjoin()` filters, they are automatically prefixed with the join field to resolve them against the joined model:
julia
"nationality" is automatically prefixed to "driverid__nationality"
query = M.Result.objects.cjoin("driverid" => "Driver", filters=["nationality" => "Brazilian"])
This works with Q and Qor too:
query = M.Result.objects.cjoin("driverid" => "Driver", filters=[ Q("nationality" => "Brazilian", Qor("forename" => "Ayrton", "forename" => "Nelson")) ])
All three fields (nationality, forename, forename) are auto-prefixed
If a field belongs to the base model instead, keep it in `.filter(...)` rather than `.cjoin(...)`:
julia query = M.Newjoinposition.objects. cjoin("result" => "Result", filters=["resultid" => 1]). filter("description" => "test 1")
### ForeignKey Target Validation
When a join field already has a defined `ForeignKey` on the model, `.cjoin()` validates that the target model matches:
julia
This works: Result.driverid FK points to Driver
query = M.Result.objects. cjoin("driverid" => "Driver", filters=["nationality" => "Brazilian"]). values("driverid__surname")
This raises an error: driverid points to Driver, not Constructor
query = M.Result.objects. cjoin("driverid" => "Constructor", filters=["name" => "Ferrari"])
QueryBuildError: Field 'driverid' is already a ForeignKey pointing to 'Driver',
but .cjoin() attempted to join with 'Constructor'...
Use query.cjoin("driverid" => "Driver", filters=[...]) instead.
**Why this validation exists:** If a ForeignKey target is mismatched, field prefixing would resolve against the wrong model, silently breaking your filters. By enforcing target model matching, `.cjoin()` ensures ON-condition filters always apply to the correct joined model.
### Auto-Discovery Warning (No Explicit FK Link)
If the source field in `main_join` is **not** a ForeignKey field and you do not pass `field=...`, `.cjoin()` auto-discovers the target model primary key and logs a **warning by default** to ensure you are joining intentionally.
Example:
julia
"result" is an IntegerField, not a ForeignKey
query = M.Newjoinposition.objects.cjoin("result" => "Result")
cjoin warns and auto-links:
main.result -> Result.resultid (or the model PK)
#### If your intended link is not the target model PK
Pass an explicit field mapping:
julia query = M.Newjoinposition.objects.cjoin("result" => "Result", field=Models.ForeignKey("Result", pkfield="yourtarget_field"))
This keeps behavior explicit and avoids accidental joins to the wrong target column.
#### If you intentionally want auto-discovery without the warning
You can suppress the warning using the `warn` parameter:
julia
Suppress the auto-discovery warning
query = M.Newjoinposition.objects.cjoin("result" => "Result", warn=false) ```
This is useful when you're confident about the join link and don't want the informational message in production logs.