Derived columns: aliasColumn, formulaColumn

A derived column is declared in the model and computed by each query. It never becomes a column of the database. Migration ignores it. A query reads it with a dollar sign and its name, like a physical column. asqueel has two declarations for this lesson. aliasColumn reads a column through a relation path. formulaColumn computes an SQL expression, a scalar sub-select, or an EXISTS test.

An alias column gives a name to a relation path. state_name reads the name of the state of the customer. region_name crosses two relations. In the SQL, the alias becomes the same LEFT JOIN as the path. The result column carries the alias name, not the path name. The alias takes the dtype and the attributes of its target column. state_name has dtype A, like the name column of the state. When the target carries a relation, the alias inherits it, as lesson 05 showed.

sql_formula is an SQL expression. Inside it, a dollar name is a column of the same table. An at path reaches a related table and adds its join. The compiler replaces the references and puts the expression between brackets. full_address joins two text columns. value_category is a CASE expression. The formula has no type that asqueel can infer. Declare it with dtype. Without dtype, a formula column has dtype A.

select describes a sub-select as a dictionary. table is the table of the sub-select. columns is what it returns. where is its filter. Inside the dictionary, a dollar name belongs to the sub-select table. hash THIS dot id belongs to the row that owns the formula, here the customer. That is how the sub-select is correlated with the outer row. The sub-select must return exactly one column. asqueel adds no LIMIT. If it returns more than one row, PostgreSQL raises an error. The dictionary also accepts order_by and limit. The latest invoice in lesson 05 uses them.

exists has the same dictionary, without columns. It becomes an EXISTS test. The result is true when the sub-select finds at least one row. Declare dtype B. asqueel does not infer a boolean from the SQL. A formula column takes exactly one of sql_formula, select or exists. Two of them together raise a ValueError.

To use a sub-select inside a larger expression, give it a name. An argument select underscore name holds the dictionary. The formula refers to it as hash and the name. Here hash top_note is the text of the note with the lowest priority. COALESCE returns No notes when the invoice has none. exists underscore name works the same way and becomes an EXISTS test. Every named sub-select must appear in the formula. An unused one raises a ValueError.

A formula can have its own parameters. Each argument var underscore name gives a value. The formula refers to it as colon and the name, without var. The value is a bound parameter, not SQL text. The compiler gives it a name that belongs to this formula, here f2 underscore high_label. So two formulas can use the same parameter name without a conflict. In a formula column, var values are Python values. In a virtual relation, lesson 07, they are SQL expressions of the source row.

A formula can refer to other formulas and aliases of the same table. The compiler expands each reference into its own expression. line_gross uses line_total and line_vat, and line_vat uses line_total. The final SQL contains only physical columns. avg_invoice_value divides two sub-select formulas. Each sub-select appears again in the expression. A formula that refers back to itself, directly or through others, raises a ValueError.

A query with columns star returns the physical columns of the table. It also returns the virtual columns declared with static True. Other virtual columns are left out of star. Here the alias invoice_date and the formula product_code are static. They follow the physical columns in the result. The formula product_code also shows an at path inside sql_formula. Lesson 10 shows which physical columns star leaves out.

An alias names a path, and a formula is an expression, a sub-select or an EXISTS test. hash THIS correlates sub-selects, named sub-selects compose formulas, and var arguments are bound values. Declare dtype on every formula, and mark with static the virtual columns that star must return. The next lesson covers formulas supplied by table methods.