Grouping

Grouping

To group by a given field, use the groupBy() method.

$query->groupBy('uid');

The above code will instruct the query to group by the uid field. Note that the field name here should be the alias created by the addField() or addExpression() methods, so in most cases you will want to use the return value from those methods here to ensure the correct alias is used.

To obtain the count of rows that have been grouped by a field such as the uid, you could run the following:

$query->addExpression('count(uid)', 'uid_node_count');

To group by multiple fields, simply call groupBy() multiple times in the order desired.

Having

It's possible to add a condition on aggregated values.

$query->having('COUNT(uid) >= :matches', array(':matches' => $limit));

This example would find instances where the count of uids is greater than or equal to the $limit. Note that the first parameter to having is not filtered before sending it to the database, so user supplied values should be passed in via the second parameter.

Examples of groupBy and having

The following code counts the number of nodes per uid:

$query = db_select('node', 'n')
->fields('n', array('uid'));
$query->addExpression('count(uid)', 'uid_node_count');
$query->groupBy("n.uid");
$query->execute();

The next block of code takes the previous example of counting nodes per uid and limits the results to uids that have at least 2 records.

$query = db_select('node', 'n')
->fields('n',array('uid'));
$query->addExpression('count(uid)', 'uid_node_count');
$query->groupBy("n.uid");
$query->having('COUNT(uid) >= :matches', array(':matches' => 2));
$results = $query->execute();