Scene 01
One call, several columns
The inverse side of a relation reaches many rows. A query uses it by calling aggr on the path. Inside aggr you list aggregate functions. Here count counts the rows of each invoice, and sum adds their line_total. line_total is a formula of the row table. Each aggregate becomes one column of the result. Each column is a correlated sub-select. The sub-select reads the rows of the current invoice only. The outer query keeps one row per invoice. Invoice A-003 has no rows. Its count is zero and its sum is None.
Example & screen notes
Slide title "@rows.aggr(...)". Code:
q = db.table('invc.invoice').query(
columns='$inv_number, @rows.aggr(count() AS n, sum($line_total) AS amount)',
order_by='$inv_number')
SQL:
SELECT "t0"."inv_number" AS "inv_number",
( SELECT COUNT(*) AS "COUNT___"
FROM "invc"."invc_invoice_row" AS "t0_t0"
WHERE ("t0_t0"."invoice_id"="t0"."id") ) AS "n",
( SELECT SUM(( "t0_t0"."quantity" * "t0_t0"."unit_price" )) AS "SUM__line_total_"
FROM "invc"."invc_invoice_row" AS "t0_t0"
WHERE ("t0_t0"."invoice_id"="t0"."id") ) AS "amount"
FROM "invc"."invc_invoice" AS "t0"
WHERE ("t0"."__del_ts" IS NULL)
ORDER BY "t0"."inv_number"
Rows from q.fetch():
| inv_number | n | amount |
|---|---|---|
| A-001 | 2 | 760.00 |
| A-002 | 1 | 20.00 |
| A-003 | 0 | None |
Scene 02
The functions
aggr accepts eight functions. count takes no arguments. sum, avg, min and max take one or more expressions. join concatenates values into one string. to_json collects rows into a JSON array. to_xml collects rows into XML in the Bag format. to_xml works on PostgreSQL only. On SQLite it raises UnsupportedFeatureError when the query is compiled. Any other function name raises UnsupportedFeatureError too. The arguments are expressions on the related table. They can use columns, formulas, alias columns and to-one paths of that table.
Example & screen notes
Slide title "Functions inside aggr". Table:
| Function | Result |
|---|---|
count() |
number of related rows; no arguments |
sum(x), avg(x), min(x), max(x) |
one column per argument |
join(x, sep[, placeholder]) |
the values as one string |
to_json(...) |
the rows as a JSON array of objects |
to_xml(...) |
the rows as Bag XML; PostgreSQL only |
Bottom line: "Any other name raises UnsupportedFeatureError."
Scene 03
Automatic names
Without AS, a result gets an automatic name. The name joins the relation, the argument and the function, with underscores. count has no argument, so its name is underscore rows underscore count. For join, the separator and the placeholder are not part of the name. A call to to_json without arguments is named underscore rows underscore json. When two results get the same name, the last one wins. No error is raised. In the last row of the table, the query returns only the sum, under the name n.
Example & screen notes
Slide title "Names without AS". Code:
'$inv_number, @rows.aggr(count(), sum($quantity), max($unit_price))'
Result columns:
inv_number, _rows_count, _rows_quantity_sum, _rows_unit_price_max
Table:
Call on @rows |
Name |
|---|---|
count() |
_rows_count |
sum($quantity) |
_rows_quantity_sum |
join($product_name, ', ') |
_rows_product_name_join |
to_json() |
_rows_json |
count() AS n, sum($quantity) AS n |
one column n: the last one |
Scene 04
Several arguments and expressions
sum, avg, min and max accept several arguments. Each argument produces its own column. They are never added together. The alias goes on each argument, inside the brackets. An AS after the closing bracket is accepted only when there is one argument. An argument can be an expression. The second query sums quantity times unit price row by row. It does not multiply two sums. Its column gross equals the amount of the first query.
Example & screen notes
Slide title "One aggregate per argument". Code:
'$inv_number, @rows.aggr(sum($quantity AS pieces, $line_total AS amount))'
'$inv_number, @rows.aggr(sum($quantity * $unit_price) AS gross)'
Rows of the first query:
| inv_number | pieces | amount |
|---|---|---|
| A-001 | 4 | 760.00 |
| A-002 | 1 | 20.00 |
| A-003 | None | None |
Scene 05
join: separator and placeholder
join takes an expression, a separator and an optional placeholder. The separator and the placeholder are SQL string literals, so they use single quotes. Double quotes would name a column. Each related row gives one value. A value that is NULL or an empty string is replaced by the placeholder. Without a placeholder it becomes an empty string. Its position and its separators stay. Customer Bolt has one invoice, and its total is NULL. The first join returns an empty string. The second returns n slash a. When there are no related rows at all, join returns an empty string, whatever the placeholder.
Example & screen notes
Slide title "join(x, sep, placeholder)". Code:
q = db.table('invc.customer').query(
columns="$account_name, @invoices.aggr(join($total, '|'), "
"join($total, '|', 'n/a') AS totals)",
order_by='$account_name')
Rows:
| account_name | _invoices_total_join | totals |
|---|---|---|
| Acme | 760.00|20.00 | 760.00|20.00 |
| Bolt | (empty string) | n/a |
Scene 06
Order of collected rows
join, to_json and to_xml depend on the order of the rows. By default they use the order_by declared on the related table, if it has one. To choose another order, call order_by on the path before aggr. The argument is a quoted ordering, in the same syntax as a query. It can use columns that the aggregate does not return. All aggregates of the same call share that order. It does not change the order of the outer query. Inside to_json, an alias names the field of the JSON object. The AS after to_json names the result column.
Example & screen notes
Slide title ".order_by(...).aggr(...)". Code:
"$inv_number, @rows.order_by('$unit_price DESC').aggr("
"join(@product_id.code, '/', '-') AS codes, "
"to_json($product_name AS product, $quantity) AS lines)"
Rows:
| inv_number | codes | lines |
|---|---|---|
| A-001 | DSK/CHR | [{'product': 'Desk', 'quantity': 2}, {'product': 'Chair', 'quantity': 2}] |
| A-002 | BK | [{'product': 'Book', 'quantity': 1}] |
| A-003 | (empty string) | [] |
Scene 07
to_xml
to_xml builds XML in the database. The root element is GenRoBag. Each related row becomes an element named r underscore and a position, starting from zero. Inside, each field is an element named after the column. A field holding NULL carries an attribute that marks it, so NULL stays distinct from an empty string. With no rows the value is an empty GenRoBag element. The fetch returns the text. asqueel does not convert it into objects.
Example & screen notes
Slide title "to_xml: PostgreSQL". Code:
"$inv_number, @notes.order_by('$priority').aggr("
"to_xml($note_type, $note_text) AS notes_xml)"
Value for invoice A-001:
<GenRoBag><r_0><note_type>WARN</note_type><note_text>Fragile</note_text></r_0>
<r_1><note_type>INFO</note_type><note_text>Deliver at 9</note_text></r_1></GenRoBag>
Value for an invoice without notes: <GenRoBag/>
Scene 08
Limits
A path can reach the many relation through to-one steps first. From an invoice row, the path goes to the invoice, to the customer, and then aggregates the invoices of that customer. The many step must be the last one. A path that crosses two many relations raises. aggr is a projection. It must stand alone as a column of the query. It cannot be part of a larger expression, and it cannot appear in where. The second table repeats the results on an empty set. count gives zero, the numeric aggregates give None, join gives an empty string, to_json an empty array and to_xml an empty Bag.
Example & screen notes
Slide title "Where aggr is accepted". Table:
| Expression | Result |
|---|---|
@invoice_id.@customer_id.@invoices.aggr(count() AS n) on invoice_row |
accepted: to-one steps, then the many step |
@invoices.@rows.aggr(count()) on customer |
UnsupportedFeatureError: a path through the many relation @invoices |
@invoices.aggr(sum($total)) + 1 |
UnsupportedFeatureError: aggr must be a standalone projection |
where='@invoices.aggr(count()) > 0' |
UnsupportedFeatureError: aggr is currently supported only in projections |
Second table, results with no related rows:
count() |
sum/avg/min/max |
join |
to_json |
to_xml |
|---|---|---|---|---|
| 0 | None | '' |
[] |
<GenRoBag/> |
Scene 09
Recap
aggr turns related rows into columns without multiplying the outer rows. Each result has an explicit or an automatic name. order_by sets the order of collected rows, and aggr is accepted only as a projection. The next lesson covers virtual relations, which filter the related rows before aggregation.
Example & screen notes
Three bullets:
@relation.aggr(...): one correlated sub-select per result column, one outer row per record- Names:
ASon each argument, otherwise_<relation>_<argument>_<function> .order_by(...)ordersjoin,to_json,to_xml;aggris projection only