To-one and inverse relations

A relation is declared on the column that holds the key. You call relation on the column. The first argument is the target, written as package dot table dot column. Inside the same package, table dot column is enough. The invoice column customer_id points to the id of customer. foreign_key True asks for a physical foreign key. In a package, a relation gets a foreign key even when you omit the argument. Migration creates the constraint with ON UPDATE CASCADE and an index on the column.

A relation can say what happens when the target row is deleted. The argument onDelete is an action that asqueel runs in Python, during its own delete. The argument onDelete_sql is an action written into the foreign key, so PostgreSQL runs it. The two are separate. The DDL of the previous scene has no ON DELETE clause, because the course model declares only onDelete. Lesson 23 explains both families of actions. In this lesson, relations are used to read.

A query reads a related column with a path. The path starts with an at sign and the name of the column that holds the key. Here it is at customer_id. A dot follows, and then a column of the target table. The relation is named after the key column, not after the target table. The compiler turns the path into a LEFT JOIN. Both columns of the customer use the same join, with the alias t1. One join is created per path, and every column on that path reuses it. The result column has a name built from the path. Each character that is not a letter, a digit or an underscore becomes an underscore. So the path becomes underscore customer_id underscore account_name.

A path can cross several relations. Each step is an at sign and a key column of the table reached so far. The last element is a plain column. From an invoice row, the path goes to the invoice, to the customer, to the state and to the region. The compiler writes one LEFT JOIN per step. Paths work in where and order_by too. A path used only in where still adds its joins to the query.

Every relation also exists in the other direction. relation_name gives the name of that inverse side. The invoice_row relation declares rows. So, from an invoice, at rows leads to its rows. Without relation_name, the inverse side gets a name made from the package, the table and the column, for example invc_customer_profile_customer_id. The inverse side reaches many rows. A query cannot project it as a column. at rows alone raises ValueError. at rows dot quantity raises UnsupportedFeatureError. asqueel never multiplies the rows of a query through a join towards many. The inverse side is used through aggregation, which lesson 06 covers.

one_one True states that each target row has at most one row on the other side. A customer has at most one profile. With this flag, the inverse side is a path towards one. From the customer, at profile dot website is a LEFT JOIN, like a forward path. The flag does not create a unique constraint. If the data holds two profiles for one customer, the join returns two rows. Declare a unique column or a constraint when the database must enforce it.

weak_relation declares a relation that only navigates. It never creates a foreign key. It can sit on a physical column or on a virtual column. Here the formula last_invoice_id selects the id of the latest invoice of the customer. Lesson 08 explains formulas. The weak relation makes the formula a key. A query can then read at last_invoice_id dot inv_number. The join compares the result of the formula with the invoice id. relation on a virtual column behaves the same way. It refuses foreign_key True.

An alias column reads a column through a relation path. Lesson 08 covers aliases in full. When the target of the alias carries a relation, the alias inherits it. On the invoice row, the alias customer_id reads the customer_id of the invoice. That column points to the customer. So the alias points to the customer too. The path at customer_id dot account_name starts from the row and reaches the customer through the invoice. The alias gets only the target of the relation. It gets no foreign key, no inverse name and no delete action.

A path names the key column and becomes one LEFT JOIN per path. The inverse side has the name given by relation_name, and towards many it is used only through aggregation. one_one, weak relations and aliases change navigation only. The next lesson covers aggregations over the inverse side.