query(...): syntax and clauses

Section three is about reading. All its examples use the invoicing recipe of the course and this sample data. There are three customers. Cy Draft is a draft row. There are four invoices, two for Ada and two for Bob. The invoice table has one column more than the course recipe: details, with dtype X. An X column holds an XML document. Invoice A1 has a document; the other invoices have NULL. The examples assume a database object built with AsqueelDb from this recipe, with the tables created by migrate.

You read rows through a table object. db dot table returns it. Its query method has three named arguments and then keywords. The first argument is columns, and its default is star. The second is where. The third is sqlparams, a mapping of parameter values. Columns and where are often passed by position, as in this example. Every other keyword has one of two roles. If its name is a query option, such as order_by or limit, it sets that option. Otherwise it is a parameter value. Here minimum is a parameter, and the where clause reads it as colon minimum. Lesson fifteen covers parameters in detail.

A dollar sign names a column of the queried table. It can be a physical column or a virtual column, such as an aliasColumn or a formulaColumn. An at sign starts a relation path. At customer_id dot account_name follows the relation declared on customer_id and reads account_name of the customer. The compiler writes a LEFT JOIN for it. A longer path adds one join per step. For example, on the customer table, at state dot at region_code dot name reaches the region. A path may only follow to-one relations. A path through a to-many relation, such as at invoices on the customer, raises UnsupportedFeatureError. Lesson six shows how to aggregate a to-many relation instead. Look at the WHERE line. It has a second condition on the column underscore underscore del_ts. That condition comes from a row policy. Lesson eighteen explains it.

Every result column has a name. A plain dollar reference keeps the column name. Any other item without AS gets a name built from its text. Each character that is not a letter, a digit or an underscore becomes an underscore. So at customer_id dot account_name becomes underscore customer_id underscore account_name. Dollar total times two becomes a name with three underscores. These names are hard to read. Give every relation path and every expression an explicit AS. The alias is written into the SQL as you typed it. Without quotes, PostgreSQL folds it to lower case. With double quotes, the case is kept.

The default columns value is star. Star expands to the physical columns of the table, in the order of the model. That includes the system columns from sys_fields, such as id and underscore underscore ins_ts. After the physical columns come the virtual columns declared with static equal True. The course recipe declares none, so the star of invoice gives only physical columns. A physical column with dtype X is left out of the star. It is included when you pass bagFields equal True, or when the query locks rows with for_update. A column you name explicitly is always included. Star can be combined with other items, as in star comma dollar customer_name. A star followed by other characters raises UnsupportedFeatureError.

order_by takes an SQL ordering expression with dollar and at references. limit and offset take non-negative integers. A negative value raises ValueError. The query in this example returns A2 and B1. Without order_by, the compiler uses the order_by attribute of the table, if the table declares one. The course recipe declares none. So a query without order_by has no ORDER BY clause, and PostgreSQL returns rows in no guaranteed order. Note one detail. A limit of zero writes no LIMIT clause at all. The query then returns every row, not zero rows. The same holds for an offset of zero.

distinct equal True writes SELECT DISTINCT. distinct accepts only True, False or None; any other value raises ValueError. group_by and having take expressions, resolved like the where clause. Relation paths work there too. In the example, the invoices are grouped by customer name, and having keeps the groups above six hundred. Only Bob Ltd remains, with a total of one thousand five hundred and eighty. A group_by of star writes no GROUP BY clause. A having without group_by is written as you give it; PostgreSQL then treats the whole result as one group. The option addPkeyColumn equal True appends the primary key with the name pkey. It has no effect on a query with distinct or group_by.

Calling query does not run SQL and does not compile. It checks the options and stores them. A misspelled column is found only when the query compiles. Compilation happens on the first access to sqltext, compiled or a fetch terminal. The compiled query is then kept. Every later fetch reuses the same SQL text and executes it again. No rows are cached. Some errors are raised by query itself, before any compilation. An option that is not available, such as storename, raises UnsupportedFeatureError. columns equal None raises the same error. for_update together with distinct or group_by raises UnsupportedFeatureError too.

To recap. Dollar names a column, at follows a to-one relation, and AS names the result. Star expands to physical columns and then to static virtual columns. The query compiles once, on first use, and every terminal executes it again. The next lesson covers parameters and macros.