Expressions

The Select query builder supports the use of expressions in the field list. Examples of expressions include "twice the age field", "a count of all name fields", and a substring of the title field. Be aware that many expressions may use SQL functions, and not all SQL functions are standardized across all databases. It is up to the module developer to ensure that only cross-database compatible expressions are used. (Refer to the list of functions and operators)

To add an expression to a query, use the addExpression() method.

$count_alias = $query->addExpression('COUNT(uid)', 'uid_count');
$count_alias = $query->addExpression('created - :offset', 'timestamp', array(':offset' => 3600));

The first line above will add "COUNT(uid) AS uid_count" to the query. The second parameter is the alias for the field. In the rare case that alias is already in use, a new one will be generated and the return value of addExpression() will be the alias used. If no alias is specified, a default of "expression" (or expression_2, expression_3, etc.) will be generated.

The optional third parameter is an associative array of placeholder values to use as part of the expression.

Note that some SQL expressions may not function unless accompanied by a GROUP BY clause added with $query->groupBy(). It is up to the developer to ensure that the query that is generated is in fact valid.

MAX, MIN, AVG, etc

Expressions are useful to do aggregate functions, like MAX, MIN, AVG

$select = $this->database->select('node__body', 't');
$select->addExpression('MAX(entity_id)');
$last = $select->execute()->fetchField();

GREATEST, LEAST, etc

Expressions with GREATEST and LEAST work the same on the by core supported databases (MySQL, MariaDB, PostgreSQL and SQLite) for normal values. Only when mixed with NULL values are the results different. MySQL and MariaDB have a different option how to handle NULL values than PostgreSQL. The Microsoft SQL-server only likes to handle 'real' values in combination with GREATEST.