Grouping rows by a certain field is as simple as specifying the group setting when issuing a find operation. For example, the following statement, while not exactly practical by itself, shows how to use the setting:

$data = $this->Post->find('all', array(
'group' => array('Blog.id')
));

If we also want to obtain the number of rows for each grouped set, which in our case means the number of posts per blog, we would do:

$data = $this->Post->find('all', array(
'fields' => array('COUNT(Post.id) AS total', 'Blog.*'),
'group' => array('Blog.id')
));

The preceding query will return the following data structure:

array(
array(
0 => array(
'total' => 4
),
'Blog' => array(
'id' => 1,
'user_id' => 1,
'name' => 'John Doe's Blog'
)
),
array(
0 => array(
'total' => 3
),
'Blog' => array(
'id' => 2,
'user_id' => 2,
'name' => 'Jane Doe's Blog'
)
)
)

Let us now make sure that every time we have a calculated field (which come in the index 0 of each resulting row), they become part of the resulting model, for easier readability. To do so, we override the afterFind() method. If you don't have one already, create a file named app_model.php in your app/ folder. Make sure your AppModel class includes the following contents:

<?php
class AppModel extends Model {
public function afterFind($results, $primary = false) {
if (!empty($results)) {
foreach($results as $i => $row) {
if (!empty($row[0])) {
foreach($row[0] as $field => $value) {
if (!empty($row[$this->alias][$field])) {
$field = 'total_' . $field;
}
$results[$i][$this->alias][$field] = $value;
}
unset($results[$i][0]);
}
}
}
return parent::afterFind($results, $primary);
}
}
?>

As a result, the previous query, which uses GROUP and COUNT, will now look like a much more readable result set:

array(
array(
'Blog' => array(
'id' => 1,
'user_id' => 1,
'name' => 'John Doe's Blog'
),
'Post' => array(
'total' => 4
)
),
array(
'Blog' => array(
'id' => 2,
'user_id' => 2,
'name' => 'Jane Doe's Blog'
),
'Post' => array(
'total' => 3
)
)
)

If we want to subdivide the post counts for each blog according to the month they were created on, we would have to add another level of grouping:

$data = $this->Post->find('all', array(
'fields' => array(
'CONCAT(YEAR(Post.created), '-', MONTH(Post.created)) AS period',
'COUNT(Post.id) AS total',
'Blog.*'
),
'group' => array('Blog.id', 'period')
));

Considering our afterFind implementation, the preceding query would produce the following results:

array(
array(
'Blog' => array(
'id' => 1,
'user_id' => 1,
'name' => 'John Doe's Blog'
),
'Post' => array(
'period' => '2010-4',
'total' => 4
)
),
array(
'Blog' => array(
'id' => 1,
'user_id' => 1,
'name' => 'John Doe's Blog'
),
'Post' => array(
'period' => '2010-5',
'total' => 1
)
),
array(
'Blog' => array(
'id' => 2,
'user_id' => 2,
'name' => 'Jane Doe's Blog'
),
'Post' => array(
'period' => '2010-10',
'total' => 1
)
),
array(
'Blog' => array(
'id' => 2,
'user_id' => 2,
'name' => 'Jane Doe's Blog'
),
'Post' => array(
'period' => '2010-4',
'total' => 1
)
)
array(
'Blog' => array(
'id' => 2,
'user_id' => 2,
'name' => 'Jane Doe's Blog'
),
'Post' => array(
'period' => '2010-6',
'total' => 1
)
)
)

We use the group find setting to specify what fields will be used for grouping the resulting rows. That setting is given as an array, where each element is a field to group in. When we specify more than one field, such as the last example in the recipe, grouping of rows occurs in the given order of grouping fields.

Calculated fields, that is, expressions that result in a value (such as the COUNT(*) AS total expression used throughout the recipe) are placed in the index 0 of each resulting row, because they are not real fields defined in the model. Because of that, we overrode the afterFind() method, executed after a result for a find operation is obtained, and with some basic logic we make sure those calculated fields get included in the resulting row within a much more readable index: the model name.

The last example in the recipe shows not only how to group on more than one field, but how to properly use some SQL methods (such as MONTH and YEAR) with an alias, so we can easily return the value of that expression and also use it to group or optionally order the rows.