Asqueel

Course / Orientation / Lesson 03

Lesson 03 · Orientation

Filter and sort through relationships

Use relationship paths in WHERE and ORDER BY, including two-hop filters and descending dates. Select only the fields you need.

1:49 · video7 scenesPrerequisites: the in-memory Python session and imported CSV data from lesson 2

Asqueel 0.6.0 · Python console demonstration · API compatibility

Course contents
Orientation
  1. 01 Welcome: your first table
  2. 02 Customers, invoices and relationship paths
  3. 03 Filter and sort through relationships
  4. 04 Aliases and formula columns
  5. 05 Aggregate related records
  6. 06 Group by and HAVING
  7. 07 Fetch and export
  8. 08 Optional: find the best buyer
  9. 09 Writes and transactions
  10. 10 Python triggers
The model
  1. 11 virtualRelation
  2. 12 Derived columns: aliasColumn, formulaColumn
  3. 13 Advanced formulas
  4. 14 subQueryColumn and bagItemColumn
  5. 15 Layered recipes
  6. 16 Extending the grammar
  7. 17 The model hosts external metadata
Reading data
  1. 18 query(...): syntax and clauses
  2. 19 Parameters and macros
  3. 20 Fetch terminals
  4. 21 The environment
  5. 22 Row policies
  6. 23 Locking on read: for_update
Writing data
  1. 24 insert, update, delete
  2. 25 The order of the write cycle
  3. 26 Field triggers
  4. 27 Relation actions: Python and SQL
  5. 28 Writes without triggers: sql_*, table.lock
  6. 29 sys_fields, hierarchical, auto_counter
  7. 30 recordToUpdate and batchUpdate
Transactions & operations
  1. 31 Transactions and connections
  2. 32 Deferred callbacks
  3. 33 Direct SQL
  4. 34 Import and migrations
  5. 35 The command line
  6. 36 Integration in a host application
  7. 37 The complete example
Other Python ORMs
  1. 38 asqueel, SQLAlchemy, Django and Peewee
WATCH THE LESSON1:49 · English
Download MP4 ↓English captions ↓

Follow the chapters, then try the examples yourself. The complete transcript and code are below.

Try it yourself

Run this lesson in Google Colab, with the starting data included. A Google account is required to execute code. You can also download the notebook for local Jupyter.

Try it yourself ↗Download notebook ↓

Download this example ↓ · Includes four CSV files and console commands. Put the CSV files in your working folder. If Python was closed, use start-here.py to recreate lessons one and two.

Scene 01

What we'll learn in this lesson

Welcome back! Here's what we'll learn in this lesson. Filter and sort using relationship paths. We will select invoices by customer city, order by date, and combine conditions with parameters. 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.

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
>>> 
>>> ######## Find invoices for Florence ########
>>> 
>>> 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
>>> 
>>> ######## Sort invoices by city ########
>>> 
>>> 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
>>> 
>>> ######## Show the newest rows first ########
>>> 
>>> 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

>>> 
>>> ######## Find rows for Florence ########
>>> 
>>> 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
>>> 
>>> ######## Combine the conditions ########
>>> 
>>> from datetime import date
>>> 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]]

>>> 
>>> ######## Close this read transaction ########
>>> 
>>> 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.