Scene 01
A filtered inverse relation
A virtual relation is a named set of related rows. It is declared on the table where queries start. It has two forms. The first form starts from an existing relation and adds a condition. The second form names any table and gives the whole link as a condition. A virtual relation reaches many rows. A query uses it only through aggr, as in lesson 06. The examples of this lesson are additions to the course model.
Example & screen notes
Slide title "virtualRelation". Three bullets:
- A named set of related rows, declared on the source table
- Form
relation='@...': an existing relation plus a condition - Form
table='pkg.table': any table plus a condition
Bottom line: "Used only through .aggr(...)."
Scene 02
Form relation= with a condition
The virtual relation large_invoices starts from the inverse relation invoices of the customer. Its condition keeps the invoices with a total of at least one hundred. In the condition, dollar total is a column of the invoice, the target table. The sub-select keeps the key correlation of the base relation. It adds the condition in AND, between brackets. Acme has two invoices, and one of them reaches one hundred. Bolt has none.
Example & screen notes
Slide title "relation='@invoices'". Code:
customer = pkg.table('customer', pkey='id')
customer.virtualRelation('large_invoices', relation='@invoices',
condition='$total >= :var_threshold', var_threshold='100')
db.table('invc.customer').query(
columns='$account_name, @large_invoices.aggr(count() AS n, sum($total) AS amount)',
order_by='$account_name')
SQL of the first column:
( SELECT COUNT(*) AS "COUNT___"
FROM "invc"."invc_invoice" AS "t0_t0"
WHERE (("t0_t0"."customer_id"="t0"."id") AND ("t0_t0"."total" >= (100))) ) AS "n",
Rows:
| account_name | n | amount |
|---|---|---|
| Acme | 1 | 760.00 |
| Bolt | 0 | None |
Scene 03
var_* are expressions of the source row
A condition can refer to two tables. A dollar column or an at path belongs to the target table. A name that starts with colon var underscore belongs to the source row. Its value is the argument with the same name. That value is an SQL expression on the source table. It is not a Python value. The compiler resolves the expression and writes it into the condition, between brackets. In the example on the slide, var_total is the total of the invoice. var_customer_type_code follows the customer of the invoice to its type. The next two scenes use both.
Example & screen notes
Slide title "Two sides of a condition". Table:
| In the condition | Belongs to |
|---|---|
$column, @relation.column |
the target table |
:var_<name> |
the source row, through the var_<name> argument |
Code:
invoice.virtualRelation('discount_tiers', table='invc.discount_tier',
condition='$customer_type_code = :var_customer_type_code '
'AND $min_amount <= :var_total AND $max_amount > :var_total',
var_customer_type_code='@customer_id.customer_type_code', var_total='$total')
Scene 04
Derivation
relation can name another virtual relation. recent_large starts from large_invoices and adds a date condition. The compiler follows the chain down to the ordinary relation. It keeps the key correlation and adds every condition in AND, each between brackets. There is still one sub-select per result column. Each level keeps its own var arguments. Two levels can use the same var name without a conflict. A chain that loops back on itself raises an error.
Example & screen notes
Slide title "A virtual relation on a virtual relation". Code:
customer.virtualRelation('recent_large', relation='@large_invoices',
condition="$date >= :var_since", var_since="DATE '2026-09-01'")
'$account_name, @recent_large.aggr(count() AS n, join($inv_number, \',\') AS numbers)'
Condition in the SQL:
WHERE (("t0_t0"."customer_id"="t0"."id") AND ("t0_t0"."total" >= (100))
AND ("t0_t0"."date" >= (DATE '2026-09-01')))
Rows:
| account_name | n | numbers |
|---|---|---|
| Acme | 1 | A-001 |
| Bolt | 0 | (empty string) |
Scene 05
Form table= with a condition
The second form names a target table with table. There is no relation between invoice and discount_tier. The condition is the whole link. asqueel adds no correlation of its own. Here a tier matches when it has the customer type of the invoice and its range contains the invoice total. The var_customer_type_code expression is a path. So the compiler adds a LEFT JOIN to the customer in the outer query, as alias t1. Invoice A-001 is retail and totals seven hundred sixty, so it falls in the tier with rate five. With the table form, condition is mandatory. A missing or empty condition raises ValueError when the model is built.
Example & screen notes
Slide title "table= : no key relation needed". Code, the table added to the course model, from examples/test_invoice_pg/invc.py:
def discount_tier(pkg):
tbl = pkg.table('discount_tier', pkey='id', name_long='Discount tier')
tbl.options().sys_fields()
tbl.column('customer_type_code', size=':5').relation(
'customer_type.code', relation_name='discount_tiers', onDelete='cascade')
tbl.column('min_amount', dtype='N')
tbl.column('max_amount', dtype='N')
tbl.column('discount_rate', dtype='perc')
Query:
'$inv_number, $total, @discount_tiers.aggr(max($discount_rate) AS rate, count() AS n)'
Condition in the SQL:
WHERE (("t0_t0"."customer_type_code" = ("t1"."customer_type_code")
AND "t0_t0"."min_amount" <= ("t0"."total") AND "t0_t0"."max_amount" > ("t0"."total")))
Rows (tiers: RET 0-500 rate 0, RET 500-5000 rate 5, WHO 0-100000 rate 8):
| inv_number | total | rate | n |
|---|---|---|---|
| A-001 | 760.00 | 5.00 | 1 |
| A-002 | 20.00 | 0.00 | 1 |
| A-003 | None | None | 0 |
Scene 06
What a virtual relation is not
A virtual relation exists only for aggregation. A query cannot project it alone. It cannot read one of its columns with a dot. An alias column cannot use it as a path. These uses raise ValueError when the query is compiled. A virtual relation is also not a forward relation to one row. If you need one value from it, use an aggregate such as max or min, as the discount example does.
Example & screen notes
Slide title "Accepted and refused". Table:
| Expression | Result |
|---|---|
@large_invoices.aggr(count() AS n) |
accepted |
@large_invoices |
ValueError: Unknown column invc.customer.@large_invoices |
@large_invoices.total |
ValueError: Relation @large_invoices not found |
aliasColumn('discount_tier_id', '@discount_tiers.id') |
ValueError: Relation @discount_tiers not found at compile |
Scene 07
Recap
A virtual relation either filters an existing relation or links any table through a mandatory condition. In the condition, columns belong to the target and var arguments belong to the source row. Virtual relations can be chained, and they are used only through aggr. The next lesson covers derived columns.
Example & screen notes
Three bullets:
relation='@...'adds a condition to a relation;table=gives the whole link as a condition$and@in the condition are the target;:var_*are SQL expressions of the source row- Virtual relations chain in AND and are used only through
.aggr(...)