{ "nbformat": 4, "nbformat_minor": 5, "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Filter and sort through relationships\n", "\n", "Use relationship paths in WHERE and ORDER BY, including two-hop filters and descending dates. Select only the fields you need.\n", "\n", "[Watch lesson 3](https://asqueel.org/learn/03-the-recipe/)\n", "\n", "**Run the cells from top to bottom in a fresh Python kernel.** Each notebook includes its own starting state. SQLite runs in memory; restarting the kernel discards the database. To repeat the exercise, restart the kernel and run all cells. Colab execution requires a Google account.\n", "\n", "\u00a9 2026 Softwell S.r.l. \u00b7 Apache-2.0" ], "id": "l3-c0-2b7e082b0b86" }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Before the examples: `_ = ...` is optional\n", "\n", "It assigns a return value we do not need, preventing its automatic display in the Python console or notebook. The call still runs normally. This is a Python convention, not an Asqueel requirement. When you need the result, use a meaningful variable name." ], "id": "l3-c1-5261ce0eb0bf" }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Prepare the notebook\n", "\n", "The video starts in a terminal. Here, install the same official package in the notebook kernel. No virtual environment or separate Python console is needed in Colab." ], "id": "l3-c2-a53efa980848" }, { "cell_type": "code", "metadata": {}, "source": [ "%pip install -q \"asqueel[migration]==0.6.0\"" ], "id": "l3-c3-5283bc5543e7", "execution_count": null, "outputs": [] }, { "cell_type": "code", "metadata": {}, "source": [ "%config InteractiveShell.ast_node_interactivity = \"all\"" ], "id": "l3-c4-7a5eb4f63b61", "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Recreate the starting point\n", "\n", "The previous lessons are rebuilt below. The four fictional CSV files are included in this notebook and written into a new temporary folder. No downloads or existing database are needed." ], "id": "l3-c5-26bce07fb451" }, { "cell_type": "code", "metadata": { "jupyter": { "source_hidden": true }, "cellView": "form" }, "source": [ "#@title Prepare the sample CSV files\n", "from pathlib import Path\n", "import os\n", "import tempfile\n", "_lesson_folder = tempfile.TemporaryDirectory(prefix=\"asqueel-notebook-\")\n", "os.chdir(_lesson_folder.name)\n", "_csv_files = {'customers.csv': 'id,name,city\\n1,Ada,Florence\\n2,Grace,London\\n3,Linus,Helsinki\\n4,Guido,Amsterdam\\n5,Elena,Florence\\n6,Alan,Manchester\\n7,Margaret,New York\\n8,Barbara,Boston\\n', 'invoice_rows.csv': 'id,invoice_id,product_code,quantity,unit_price,total\\n1,101,TEA,2,3.50,7.00\\n2,101,COF,1,6.90,6.90\\n3,101,MUG,4,5.25,21.00\\n4,102,BTEA,2,3.20,6.40\\n5,102,HTEA,3,4.10,12.30\\n6,102,HON,4,7.50,30.00\\n7,103,SUG,3,2.30,6.90\\n8,103,CHO,4,3.90,15.60\\n9,103,BIS,1,2.80,2.80\\n10,104,JAM,4,4.60,18.40\\n11,104,OAT,1,2.40,2.40\\n12,104,JUI,2,2.70,5.40\\n13,105,WAT,1,0.90,0.90\\n14,105,LEM,2,1.80,3.60\\n15,105,KET,3,32.90,98.70\\n16,106,POT,2,18.50,37.00\\n17,106,PLT,3,6.40,19.20\\n18,106,BWL,4,7.80,31.20\\n19,107,SPO,3,9.50,28.50\\n20,107,TRY,4,14.90,59.60\\n21,107,JAR,1,8.20,8.20\\n22,108,NAP,4,12.00,48.00\\n23,108,TWL,1,5.60,5.60\\n24,108,FLT,2,2.10,4.20\\n25,109,TIN,1,6.70,6.70\\n26,109,MAT,2,11.40,22.80\\n27,109,CAF,3,29.90,89.70\\n28,110,BOT,2,13.50,27.00\\n29,110,STR,3,4.80,14.40\\n30,110,SCL,4,24.90,99.60\\n31,111,TEA,3,3.50,10.50\\n32,111,COF,4,6.90,27.60\\n33,111,MUG,1,5.25,5.25\\n34,112,BTEA,4,3.20,12.80\\n35,112,HTEA,1,4.10,4.10\\n36,112,HON,2,7.50,15.00\\n', 'invoices.csv': 'id,number,customer_id,invoice_date\\n101,INV-001,1,2026-09-28\\n102,INV-002,2,2026-09-29\\n103,INV-003,3,2026-09-30\\n104,INV-004,4,2026-10-01\\n105,INV-005,5,2026-10-02\\n106,INV-006,6,2026-10-03\\n107,INV-007,7,2026-10-04\\n108,INV-008,8,2026-10-05\\n109,INV-009,1,2026-10-06\\n110,INV-010,2,2026-10-07\\n111,INV-011,5,2026-10-08\\n112,INV-012,3,2026-10-09\\n', 'products.csv': 'code,name,category,stock\\nBTEA,Black tea,Drinks,30\\nHTEA,Herbal tea,Drinks,18\\nHON,Honey,Food,22\\nSUG,Cane sugar,Food,40\\nCHO,Dark chocolate,Food,35\\nBIS,Biscuits,Food,28\\nJAM,Strawberry jam,Food,16\\nOAT,Oat drink,Drinks,32\\nJUI,Apple juice,Drinks,26\\nWAT,Mineral water,Drinks,60\\nLEM,Lemonade,Drinks,30\\nKET,Kettle,Kitchen,9\\nPOT,Teapot,Kitchen,14\\nPLT,Plate,Kitchen,36\\nBWL,Bowl,Kitchen,24\\nSPO,Spoon set,Kitchen,20\\nTRY,Serving tray,Kitchen,11\\nJAR,Storage jar,Kitchen,25\\nNAP,Cotton napkins,Kitchen,18\\nTWL,Tea towel,Kitchen,30\\nFLT,Coffee filters,Kitchen,45\\nTIN,Tea tin,Kitchen,15\\nMAT,Table mats,Kitchen,16\\nCAF,Coffee maker,Kitchen,7\\nBOT,Water bottle,Kitchen,21\\nSTR,Tea strainer,Kitchen,23\\nSCL,Kitchen scale,Kitchen,8\\n'}\n", "for _name, _text in _csv_files.items():\n", " _ = Path(_name).write_text(_text, encoding=\"utf-8\")\n" ], "id": "l3-c6-89efb228754e", "execution_count": null, "outputs": [] }, { "cell_type": "code", "metadata": { "jupyter": { "source_hidden": true }, "cellView": "form" }, "source": [ "#@title Recreate the previous lessons\n", "\n", "######## Import our two tools ########\n", "\n", "from asqueel import AsqueelDb, SqlDatabaseConfig\n", "\n", "\n", "######## Choose SQLite in memory ########\n", "\n", "model = SqlDatabaseConfig()\n", "root = model.source.db(implementation=\"sqlite\")\n", "_ = root.connection(name=\":memory:\", implementation=\"sqlite\")\n", "\n", "\n", "######## Describe a product table ########\n", "\n", "shop = root.packages().package(\"shop\", sqlschema=\"shop\")\n", "product = shop.table(\"product\", pkey=\"code\")\n", "_ = product.column(\"code\", size=\":10\")\n", "_ = product.column(\"name\", size=\":60\")\n", "\n", "\n", "######## Create that table ########\n", "\n", "db = AsqueelDb(model)\n", "print(db.migrate().commands)\n", "\n", "\n", "######## Add our first product ########\n", "\n", "products = db.table(\"shop.product\")\n", "products.insert({\"code\": \"TEA\", \"name\": \"Tea\"})\n", "db.commit()\n", "\n", "\n", "######## Read our product ########\n", "\n", "products.query(columns=\"$code, $name\").fetch()\n", "\n", "\n", "######## Change its name ########\n", "\n", "products.update({\"name\": \"Green tea\"}, pkey=\"TEA\")\n", "db.commit()\n", "\n", "\n", "######## Check the update ########\n", "\n", "products.query(columns=\"$code, $name\").fetch()\n", "db.commit()\n", "\n", "\n", "######## Track category and stock ########\n", "\n", "_ = product.column(\"category\", size=\":30\")\n", "_ = product.column(\"stock\", dtype=\"I\")\n", "\n", "\n", "######## Load the updated model ########\n", "\n", "db.reloadModel(model)\n", "\n", "\n", "######## Add those database columns ########\n", "\n", "print(db.migrate().commands)\n", "\n", "\n", "######## Check our original row ########\n", "\n", "products = db.table(\"shop.product\")\n", "products.query(columns=\"$name, $stock\").fetch()\n", "db.commit()\n", "\n", "\n", "######## Give tea its stock ########\n", "\n", "products.update({\"category\": \"Drinks\", \"stock\": 8}, pkey=\"TEA\")\n", "db.commit()\n", "\n", "\n", "######## Read the new values ########\n", "\n", "products.query(columns=\"$name, $category, $stock\").fetch()\n", "db.commit()\n", "\n", "\n", "######## Add products from CSV ########\n", "\n", "from io import StringIO\n", "data = StringIO(\n", " \"code,name,category,stock\\n\"\n", " \"COF,Coffee,Drinks,24\\n\"\n", " \"MUG,Mug,Kitchen,12\\n\"\n", ")\n", "\n", "\n", "######## Import those rows ########\n", "\n", "products.sql_insert(data, format=\"csv\")\n", "db.commit()\n", "\n", "\n", "######## List just the drinks ########\n", "\n", "products.query(\n", " columns=\"$name, $stock\",\n", " where=\"$category = :category\", category=\"Drinks\",\n", " order_by=\"$stock DESC\",\n", ").fetch()\n", "db.commit()\n", "\n", "# Continue with lesson two.\n", "\n", "######## Check our starting point ########\n", "\n", "products.query(columns=\"$code, $name\").fetch()\n", "db.commit()\n", "\n", "\n", "######## Describe our customers ########\n", "\n", "customer = shop.table(\"customer\", pkey=\"id\")\n", "_ = customer.column(\"id\", dtype=\"I\")\n", "_ = customer.column(\"name\", size=\":60\")\n", "_ = customer.column(\"city\", size=\":60\")\n", "\n", "\n", "######## Create the customer table ########\n", "\n", "db.reloadModel(model)\n", "print(db.migrate().commands)\n", "\n", "\n", "######## Describe a dated invoice ########\n", "\n", "invoice = shop.table(\"invoice\", pkey=\"id\")\n", "_ = invoice.column(\"id\", dtype=\"I\")\n", "_ = invoice.column(\"number\", size=\":20\")\n", "_ = invoice.column(\"invoice_date\", dtype=\"D\")\n", "\n", "\n", "######## Connect invoices to customers ########\n", "\n", "customer_id = invoice.column(\"customer_id\", dtype=\"I\")\n", "_ = customer_id.relation(\n", " \"shop.customer.id\", foreign_key=True, relation_name=\"invoices\",\n", ")\n", "\n", "\n", "######## Describe an invoice row ########\n", "\n", "invoice_row = shop.table(\"invoice_row\", pkey=\"id\")\n", "_ = invoice_row.column(\"id\", dtype=\"I\")\n", "_ = invoice_row.column(\"quantity\", dtype=\"I\")\n", "_ = invoice_row.column(\"unit_price\", dtype=\"N\", size=\"12,2\")\n", "_ = invoice_row.column(\"total\", dtype=\"N\", size=\"12,2\")\n", "\n", "\n", "######## Connect the row at both ends ########\n", "\n", "invoice_id = invoice_row.column(\"invoice_id\", dtype=\"I\")\n", "_ = invoice_id.relation(\n", " \"shop.invoice.id\", foreign_key=True, relation_name=\"rows\",\n", ")\n", "product_code = invoice_row.column(\"product_code\", size=\":10\")\n", "_ = product_code.relation(\n", " \"shop.product.code\", foreign_key=True, relation_name=\"rows\",\n", ")\n", "\n", "\n", "######## Apply the new model ########\n", "\n", "db.reloadModel(model)\n", "print(db.migrate().commands)\n", "\n", "\n", "######## Get fresh table handles ########\n", "\n", "products = db.table(\"shop.product\")\n", "customers = db.table(\"shop.customer\")\n", "invoices = db.table(\"shop.invoice\")\n", "rows = db.table(\"shop.invoice_row\")\n", "\n", "\n", "######## Inspect the row file ########\n", "\n", "from pathlib import Path\n", "print(\"\\n\".join(Path(\"invoice_rows.csv\").read_text().splitlines()[:4]))\n", "\n", "\n", "######## Expand the product catalogue ########\n", "\n", "with open(\"products.csv\", newline=\"\", encoding=\"utf-8\") as source:\n", " products.sql_insert(source)\n", "\n", "db.commit()\n", "\n", "\n", "######## Count the catalogue ########\n", "\n", "products.query().count()\n", "db.commit()\n", "\n", "\n", "######## Import customers and their cities ########\n", "\n", "with open(\"customers.csv\", newline=\"\", encoding=\"utf-8\") as source:\n", " customers.sql_insert(source)\n", "\n", "db.commit()\n", "customers.query(columns=\"$name, $city\", limit=3).fetch()\n", "db.commit()\n", "\n", "\n", "######## Import the invoices ########\n", "\n", "with open(\"invoices.csv\", newline=\"\", encoding=\"utf-8\") as source:\n", " invoices.sql_insert(source)\n", "\n", "db.commit()\n", "\n", "\n", "######## Import the invoice rows ########\n", "\n", "with open(\"invoice_rows.csv\", newline=\"\", encoding=\"utf-8\") as source:\n", " rows.sql_insert(source)\n", "\n", "db.commit()\n", "\n", "\n", "######## Check how much we imported ########\n", "\n", "invoices.query().count()\n", "rows.query().count()\n", "db.commit()\n", "\n", "\n", "######## Read invoices with their customers ########\n", "\n", "invoices.query(\n", " columns=\"$number, $invoice_date, \"\n", " \"@customer_id.name, @customer_id.city\",\n", " limit=3,\n", ").fetch()\n", "db.commit()\n", "\n", "\n", "######## Inspect the first invoice ########\n", "\n", "rows.query(\n", " columns=\"@product_code.name, \"\n", " \"$quantity, $unit_price, $total\",\n", " where=\"$invoice_id = :invoice\", invoice=101,\n", ").fetch()\n", "db.commit()\n", "\n", "\n", "######## Read across both connections ########\n", "\n", "rows.query(\n", " columns=\"@invoice_id.number, \"\n", " \"@invoice_id.@customer_id.city, \"\n", " \"@product_code.name, $total\",\n", " limit=3,\n", ").fetch()\n", "db.commit()\n" ], "id": "l3-c7-3a8d2ea69357", "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 1 \u2014 What we'll learn in this lesson" ], "id": "l3-c8-0faae73bfca2" }, { "cell_type": "markdown", "metadata": {}, "source": [ "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." ], "id": "l3-c9-889391f05c53" }, { "cell_type": "markdown", "metadata": {}, "source": [ "```text\n", "What we'll learn in this lesson\n", "Filter invoices by customer city\n", "Sort through relationship paths\n", "Put the newest invoices first\n", "Combine filters with parameters\n", "\n", "where \u2192 which records\n", "order_by \u2192 in what order\n", "```" ], "id": "l3-c10-6e69b5999875" }, { "cell_type": "markdown", "metadata": {}, "source": [ "The setup above has recreated the previous lessons in this notebook's kernel. Continue with the cells below." ], "id": "l3-c11-7ea3c414bc8c" }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 2 \u2014 Filter by the customer city" ], "id": "l3-c12-f1e26d1f75ab" }, { "cell_type": "markdown", "metadata": {}, "source": [ "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." ], "id": "l3-c13-e71921a694ea" }, { "cell_type": "code", "metadata": {}, "source": [ "######## Find invoices for Florence ########\n", "\n", "invoices.query(\n", " columns=\"$number\",\n", " where=\"@customer_id.city = :city\", city=\"Florence\",\n", ").fetch()" ], "id": "l3-c14-1af4efeb82be", "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 3 \u2014 Sort by the customer city" ], "id": "l3-c15-384f889027c3" }, { "cell_type": "markdown", "metadata": {}, "source": [ "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." ], "id": "l3-c16-fb8794ebecce" }, { "cell_type": "code", "metadata": {}, "source": [ "######## Sort invoices by city ########\n", "\n", "invoices.query(\n", " columns=\"$number, @customer_id.city\",\n", " order_by=\"@customer_id.city\", limit=4,\n", ").fetch()" ], "id": "l3-c17-ab04d38af534", "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 4 \u2014 Put the newest invoices first" ], "id": "l3-c18-b32214615bb1" }, { "cell_type": "markdown", "metadata": {}, "source": [ "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." ], "id": "l3-c19-29af63a6a265" }, { "cell_type": "code", "metadata": {}, "source": [ "######## Show the newest rows first ########\n", "\n", "rows.query(\n", " columns=\"@invoice_id.invoice_date, $total\",\n", " order_by=\"@invoice_id.invoice_date DESC\", limit=3,\n", ").fetch()" ], "id": "l3-c20-4b0a6f557d66", "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 5 \u2014 Filter through two relationships" ], "id": "l3-c21-877324831a45" }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now start from an invoice row. To reach the customer city, go through the invoice and then the customer." ], "id": "l3-c22-c86f24983072" }, { "cell_type": "markdown", "metadata": {}, "source": [ "```text\n", "invoice_row \u2192 invoice \u2192 customer\n", "@invoice_id.@customer_id.city\n", "```" ], "id": "l3-c23-223f9c8832d5" }, { "cell_type": "markdown", "metadata": {}, "source": [ "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." ], "id": "l3-c24-363321037a29" }, { "cell_type": "code", "metadata": {}, "source": [ "######## Find rows for Florence ########\n", "\n", "rows.query(\n", " columns=\"@product_code.name, $quantity\",\n", " where=\"@invoice_id.@customer_id.city = :city\",\n", " city=\"Florence\", limit=3,\n", ").fetch()" ], "id": "l3-c25-4f5a9e7300d0", "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 6 \u2014 Combine filters and ordering" ], "id": "l3-c26-a39b77d6ffac" }, { "cell_type": "markdown", "metadata": {}, "source": [ "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." ], "id": "l3-c27-3880c9ca3fe8" }, { "cell_type": "code", "metadata": {}, "source": [ "######## Combine the conditions ########\n", "\n", "from datetime import date\n", "rows.query(\n", " columns=\"@product_code.name, $total\",\n", " where=\"@invoice_id.@customer_id.city = :city \"\n", " \"AND @invoice_id.invoice_date >= :since\",\n", " city=\"Florence\", since=date(2026, 10, 1),\n", " order_by=\"@invoice_id.invoice_date DESC\", limit=4,\n", ").fetch()" ], "id": "l3-c28-65755b600e4e", "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Let's close this read transaction. We only read the data; the imported records have not changed." ], "id": "l3-c29-9499af60c589" }, { "cell_type": "code", "metadata": {}, "source": [ "######## Close this read transaction ########\n", "\n", "db.commit()" ], "id": "l3-c30-74f9d1215df0", "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 7 \u2014 Thank you \u2014 the same SQL, fewer details" ], "id": "l3-c31-f194fcbf5831" }, { "cell_type": "markdown", "metadata": {}, "source": [ "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." ], "id": "l3-c32-020f8e1b71f6" }, { "cell_type": "markdown", "metadata": {}, "source": [ "```text\n", "Thank you!\n", "The same paths in columns, where and order_by\n", "Write only what the query needs\n", "```" ], "id": "l3-c33-95ea08a2a752" }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Check your result\n", "\n", "Run this final cell to check the exercise." ], "id": "l3-c34-062cfb81c9cc" }, { "cell_type": "code", "metadata": {}, "source": [ "assert invoices.query(where=\"@customer_id.city = :city\", city=\"Florence\").count() == 4\n", "assert rows.query(where=\"@invoice_id.@customer_id.city = :city\", city=\"Florence\").count() == 12\n", "assert rows.query(where=\"@invoice_id.@customer_id.city = :city AND @invoice_id.invoice_date >= :since\", city=\"Florence\", since=date(2026, 10, 1)).count() == 9\n", "db.commit()\n", "print(\"Lesson checks passed.\")\n" ], "id": "l3-c35-b71955f80b68", "execution_count": null, "outputs": [] } ], "metadata": { "kernelspec": { "name": "python3", "display_name": "Python 3", "language": "python" }, "language_info": { "name": "python" }, "asqueel": { "version": "0.6.0", "lesson": 3 } } }