Expressions

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 this list: http://drupal.org/node/773090)

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.

Few more Expression Examples

Most of the following examples are from Drupal 7 Core itself. I have modified some of them to make it simpler to understand.

// a column called changed will be added to result and its value will be 1
$query->addExpression('1', 'changed');

// example using CONCAT
$query->addExpression("CONCAT(:prefix, t.$id_field)", 'item_id', array(':prefix' => $type . '/'));

// placeholder , column name , assign value to placeholder
$query->addExpression(':index_id', 'index_id', array(':index_id' => 'some string or variable'));

// using MAX
$query->addExpression('MAX(i.invoice_id)', 'max_invoice_id');

// using SUM
$select->addExpression('SUM(c.chvotes)', 'votes');

// using CASE //https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#operator_case
$query->addExpression('
CASE ncs.last_comment_uid 
  WHEN 0 THEN ncs.last_comment_name 
  ELSE u2.name END', 'last_comment_name'
);

// CASE with like
$alias = $db_query->addExpression("CASE WHEN t.word LIKE :like_$alias THEN 1 ELSE 0 END", $alias, array(":like_$alias" => $like));

// Using MIN
$query->addExpression('min(l.lid)', 'lid');

// Using SUM
$first->addExpression('SUM(i.score * t.count)', 'calculated_score');

$this->addExpression('SUM(' . implode(' + ', $this->scores) . ')', 'calculated_score', $this->scoresArguments);

$query->addExpression("'" . LANGUAGE_NONE . "'", 'language');

$query->addExpression('0', 'delta');

$query->addExpression('SUBSTRING(c.thread, 1, (LENGTH(c.thread) - 1))', 'torder');

$query->addExpression('0 + :changed', 'changed', array(':changed' => $changed));

// Using Case, Extensive example
$query->addExpression('
CASE 
  WHEN (MAX(i.invoice_id) < 5) THEN :less  
  WHEN (MAX(i.invoice_id) > 5 AND MAX(i.invoice_id) < 10)  THEN :medium  
  ELSE :more END', 'is_it_sri_sri', array(
    ':less' => 'less',
    ':more' => 'more',
    ':medium' => 'medium')
); //https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#operator_case