Asqueel

Course / Orientation / Lesson 08

Lesson 08 · Orientation

Optional: find the best buyer

Optional exercise: group purchases to find a product's best buyer, then follow a weak relation from the calculated ID to the customer.

1:51 · video5 scenesPrerequisites: the in-memory Python session from lesson 7

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

This is an optional extension. Continue with writes and transactions →. Later lessons do not require this exercise.

WATCH THE LESSON1:51 · 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 the database used in lesson seven; its exported files are not needed here.

Download individual CSV files: customers.csv · invoice_rows.csv · invoices.csv · products.csv

Scene 01

What we'll learn in this lesson

Welcome to this optional exercise. We will reuse group by to find the customer who spent the most on each product. Then we will turn that calculated ID into a relationship. You can skip this exercise and continue with writes and transactions; no later lesson needs this formula.

Scene 02

Choose a buyer by total spending

Why do we group before sorting? Imagine Ada buys this product twice, spending six each time, while Grace spends ten once. The largest single purchase belongs to Grace. But Ada's combined twelve makes her the best buyer. Group by combines her purchases before order by and limit choose a winner. We use the grouping from lesson six to choose the winning customer.

Example & screen notes
Best buyer = most spent across all purchases

Illustrative example:
Ada:     6 + 6 = 12
Grace:      10 = 10

ORDER BY one row + LIMIT 1 → Grace (10)
GROUP BY buyer + SUM + ORDER BY + LIMIT 1 → Ada (12)

Group first, then sort and keep one customer

Scene 03

Calculate each product's best buyer

Let's find each product's best buyer. Best means the customer who spent the most on this product across all their invoices. Group its rows by customer, sum each customer's spending, then sort and keep one ID. Sorting individual rows would find the largest single purchase instead. If totals are equal, the lower customer ID wins.

Example & screen notes
>>> 
>>> ######## Find each product's best buyer ########
>>> 
>>> best_customer = product.formulaColumn(
...     "best_customer_id", dtype="I",
...     select=dict(
...         table="shop.invoice_row",
...         columns="@invoice_id.customer_id",
...         where="$product_code = #THIS.code",
...         group_by="@invoice_id.customer_id",
...         order_by="SUM($calculated_total) DESC, "
...                  "@invoice_id.customer_id",
...         limit=1,
...     ),
... )

Scene 04

Follow a relation from the calculated ID

Let's connect that ID to the customer. A weak relation starts from the calculated column. It creates no stored foreign key. Reload the model and refresh the table handles. Let's read the buyer's name and city. The path starts from our formula, just like a stored ID. Linus is the best buyer of biscuits and black tea; Grace is the best buyer of bottles. With no sales, the ID and customer fields are null. True confirms that no migration is needed.

Example & screen notes
>>> 
>>> ######## Connect that ID to the customer ########
>>> 
>>> _ = best_customer.weak_relation("shop.customer.id")
>>> db.reloadModel(model)
>>> products = db.table("shop.product")
>>> rows = db.table("shop.invoice_row")

>>> 
>>> ######## Read the buyer's name and city ########
>>> 
>>> products.query(
...     columns="$code, $best_customer_id, "
...             "@best_customer_id.name, @best_customer_id.city",
...     order_by="$code", limit=3,
... ).fetch()
[[code=BIS,best_customer_id=3,_best_customer_id_name=Linus,_best_customer_id_city=Helsinki], [code=BOT,best_customer_id=2,_best_customer_id_name=Grace,_best_customer_id_city=London], [code=BTEA,best_customer_id=3,_best_customer_id_name=Linus,_best_customer_id_city=Helsinki]]
>>> db.commit()
>>> db.migration_plan().empty
True

Scene 05

Thank you — next steps

Thanks for following along! We combined purchases, selected one winning customer, and followed a weak relation from a calculated ID. This optional extension adds no stored columns. Continue with writes and transactions; that lesson also works without this exercise.