`feat` OR-composition across a left-joined reverse child table (PRD from Pinch)

[0x054]

Requested by: Pinch (M6 CP0 scratch-verification, ADR-0003 block-on-ferro). Blocks: pinch#26 (split lines — the line-aware category_id list filter). Sibling of #307 (reverse-relation membership predicates) — plausibly one mechanism.

Summary

A root-model query cannot filter on "a root column matches, OR a column on a reverse (BackRef) child row matches, keeping child-less roots". The child edge is a reverse relation, so neither where() traversal nor left_join() can name it (forward-FK-only relation specs), and the OR's root branch must retain roots that have no child rows at all.

Motivating Pinch queries

Pinch M6 splits a transaction into SplitLine rows (child FK → transaction, CASCADE; nullable category FK). While split, the parent's own category is vacated — exactly one layer holds categories. The list's category_id filter must then be line-aware, or splitting makes transactions less findable:

-- category filter, subtree-inclusive id set in $2
SELECT t.*
FROM transaction t
WHERE t.ledger_id = $1
  AND (
    t.category_id = ANY($2)
    OR EXISTS (
      SELECT 1 FROM split_line l
      WHERE l.transaction_id = t.id AND l.category_id = ANY($2)
    )
  )
ORDER BY t.date DESC, t.id DESC   -- composes with the keyset cursor
LIMIT 50;

(Spelled with EXISTS because the equivalent LEFT JOIN split_line … WHERE t.category_id = ANY($2) OR l.category_id = ANY($2) needs DISTINCT-on-root to avoid a multi-line split matching twice — either rendering is fine; root-set semantics are the requirement.)

Minimal repro (ferro 0.16.2, Postgres 18)

import asyncio, uuid
from typing import Annotated, Optional
import ferro
from ferro import BackRef, Field, ForeignKey, Model, Relation, engines, execute

DSN = "postgres://postgres:password@localhost:5432/postgres"

class Cat(Model):
    id: uuid.UUID = Field(default_factory=uuid.uuid7, primary_key=True)
    name: str
    txns: Relation[list["Txn"]] = BackRef()
    lines: Relation[list["Line"]] = BackRef()

class Txn(Model):
    id: uuid.UUID = Field(default_factory=uuid.uuid7, primary_key=True)
    amount_minor: int
    category: Annotated[Optional[Cat], ForeignKey(related_name="txns", on_delete="SET NULL")] = None
    lines: Relation[list["Line"]] = BackRef()

class Line(Model):
    id: uuid.UUID = Field(default_factory=uuid.uuid7, primary_key=True)
    txn: Annotated[Txn, ForeignKey(related_name="lines", on_delete="CASCADE")]
    category: Annotated[Optional[Cat], ForeignKey(related_name="lines", on_delete="SET NULL")] = None
    amount_minor: int

async def main() -> None:
    schema = f"ferro_repro_{uuid.uuid4().hex[:8]}"
    await ferro.connect(DSN)
    async with engines.session():
        await execute(f'CREATE SCHEMA "{schema}"')
    ferro.reset_engine()
    await ferro.connect(f"{DSN}?ferro_search_path={schema}", auto_migrate=True)
    async with engines.session():
        groceries = await Cat.create(name="Groceries")
        split = await Txn.create(amount_minor=-7000)          # category vacated
        await Line.create(txn=split, category=groceries, amount_minor=-3000)
        await Line.create(txn=split, category=groceries, amount_minor=-4000)
        plain = await Txn.create(amount_minor=-1000, category=groceries)
        ids = [groceries.id]

        try:
            await Txn.select().left_join(lambda t: t.lines).where(
                lambda t: (t.category_id.in_(ids)) | (t.lines.category_id.in_(ids))
            ).all()
        except Exception as e:
            print(f"left_join + OR  -> {type(e).__name__}: {e}")

        try:
            await Txn.where(
                lambda t: (t.category_id.in_(ids)) | (t.lines.category_id.in_(ids))
            ).all()
        except Exception as e:
            print(f"where traversal -> {type(e).__name__}: {e}")

        await execute(f'DROP SCHEMA "{schema}" CASCADE')

asyncio.run(main())

Output:

left_join + OR  -> AttributeError: Txn has no queryable column 'lines'. Valid columns: amount_minor, category_id, id. Valid relations: category.
where traversal -> AttributeError: Txn has no queryable column 'lines'. Valid columns: amount_minor, category_id, id. Valid relations: category.

Root cause is shared with #307: build_relation_specs registers forward FKs only, so the reverse edge is invisible to traversal and to the explicit join chainers.

Suggested shape (illustrative only — ferro idioms win)

Reverse-relation traversal in predicates with semi-join (EXISTS) rendering per branch covers this and #307 in one mechanism:

txns = await Txn.where(
    lambda t: (t.category_id.in_(ids)) | (t.lines.category_id.in_(ids))
).all()
# expected: {split, plain} — one row each, child-less roots survive via the other branch

• The reverse branch renders as a correlated EXISTS, so a transaction with three matching lines is still one result row and child-less transactions survive the OR through the other branch — no LEFT/DISTINCT bookkeeping for the caller.
• Must compose with root-column predicates under &/|, with order_by() on root columns, and with limit() — the consumer is a keyset-paginated list query.

If instead ferro grows reverse edges on the existing left_join() + whole-path LEFT machinery, the docs' pinned "a join never multiplies root rows" property needs an answer for to-many edges (implicit DISTINCT on the root PK, or rejection pointing at the EXISTS form).

Impact on Pinch when fixed

M6 CP1's line-aware category_id filter implements directly, and M8's "reporting operates on lines" queries build on the same reverse traversal. Until then the slice is blocked (ADR-0003) — no id-materialization workaround will be merged.