Filter and sort through relationships

Scene 01

Use the same paths in queries

Welcome back! We can already read fields through relationships. Now let us use those same paths to choose and sort records. Continue in the same console. We will use the customers and invoices we imported last time. If Python was closed, the download includes a script to rebuild that temporary dataset.

Example & screen notes

Relationship paths
columns → what to read
where → which records
order_by → in what order

Same Python session, same sample data
Closed Python? Use the lesson bundle to restart

Scene 02

Filter by the customer city

Let's find invoices for Florence. Use the customer city in where. Colon city is a parameter: its value is passed separately. We only ask to return the invoice number.

Example & screen notes

>>> invoices.query(
...     columns="$number",
...     where="@customer_id.city = :city", city="Florence",
... ).fetch()
[[number=INV-001], [number=INV-009], [number=INV-005], [number=INV-011]]

Scene 03

Sort by the customer city

Let's sort invoices by city. Use the same path in order by. Without a direction, the order is ascending. Limit keeps the displayed result short.

Example & screen notes

>>> invoices.query(
...     columns="$number, @customer_id.city",
...     order_by="@customer_id.city", limit=4,
... ).fetch()
[[number=INV-004,_customer_id_city=Amsterdam], [number=INV-008,_customer_id_city=Boston], [number=INV-001,_customer_id_city=Florence], [number=INV-005,_customer_id_city=Florence]]

Scene 04

Put the newest invoices first

Let's show the newest rows first. From an invoice row, follow its invoice to the date. Add DESC for descending order. These three rows belong to the latest invoice.

Example & screen notes

>>> rows.query(
...     columns="@invoice_id.invoice_date, $total",
...     order_by="@invoice_id.invoice_date DESC", limit=3,
... ).fetch()
[[_invoice_id_invoice_date=2026-10-09,total=12.8], [_invoice_id_invoice_date=2026-10-09,total=4.1], [_invoice_id_invoice_date=2026-10-09,total=15]]

Scene 05

Filter through two relationships

Now start from an invoice row. To reach the customer city, go through the invoice and then the customer. Let's find rows for Florence. The filter follows two relationships. The returned columns follow the product relation instead. A field used in a filter need not be selected.

Example & screen notes

invoice_row → invoice → customer
@invoice_id.@customer_id.city

>>> rows.query(
...     columns="@product_code.name, $quantity",
...     where="@invoice_id.@customer_id.city = :city",
...     city="Florence", limit=3,
... ).fetch()
[[_product_code_name=Green tea,quantity=2], [_product_code_name=Coffee,quantity=1], [_product_code_name=Mug,quantity=4]]

Scene 06

Combine filters and ordering

Let's combine the conditions. Keep Florence rows from October first, newest invoices first. City and date are parameters. Neither filtered field needs to appear in the result. The same paths work in columns, where, and order by. Let's close this read transaction. We only read the data; the imported records have not changed.

Example & screen notes

>>> rows.query(
...     columns="@product_code.name, $total",
...     where="@invoice_id.@customer_id.city = :city "
...           "AND @invoice_id.invoice_date >= :since",
...     city="Florence", since=date(2026, 10, 1),
...     order_by="@invoice_id.invoice_date DESC", limit=4,
... ).fetch()
[[_product_code_name=Green tea,total=10.5], [_product_code_name=Coffee,total=27.6], [_product_code_name=Mug,total=5.25], [_product_code_name=Tea tin,total=6.7]]

>>> db.commit()

Scene 07

Thank you — the same SQL, fewer details

Thanks for following along! Use relationship fields wherever the query needs them. Select only what you want to read, and add just the conditions and ordering you need.

Example & screen notes

Thank you!
The same paths in columns, where and order_by
Write only what the query needs