This recipe shows how to use CakePHP's built-in find types to perform relatively complex GROUP
and COUNT
queries, including the combination of both.
To go through this recipe we need some sample tables to work with.
users
, using the following SQL statement:CREATE TABLE `users`( `id` INT UNSIGNED AUTO_INCREMENT NOT NULL, `name` VARCHAR(255) NOT NULL, `email` VARCHAR(255) NOT NULL, PRIMARY KEY(`id`) );
blogs
, using the following SQL statement:CREATE TABLE `blogs`( `id` INT UNSIGNED AUTO_INCREMENT NOT NULL, `user_id` INT UNSIGNED NOT NULL, `name` VARCHAR(255) NOT NULL, PRIMARY KEY(`id`), KEY `user_id`(`user_id`), CONSTRAINT `blogs__users` FOREIGN KEY(`user_id`) REFERENCES `users`(`id`) );
posts
, using the following SQL statement:CREATE TABLE `posts`( `id` INT UNSIGNED AUTO_INCREMENT NOT NULL, `blog_id` INT UNSIGNED NOT NULL, `title` VARCHAR(255) NOT NULL, `body` TEXT NOT NULL, `created` DATETIME NOT NULL, `modified` DATETIME NOT NULL, PRIMARY KEY(`id`), KEY `blog_id`(`blog_id`), CONSTRAINT `posts__blogs` FOREIGN KEY(`blog_id`) REFERENCES `blogs`(`id`) );
INSERT INTO `users`(`id`, `name`, `email`) VALUES (1, 'John Doe', '[email protected]'), (2, 'Jane Doe', '[email protected]'), INSERT INTO `blogs`(`user_id`, `name`) VALUES (1, 'John Doe's Blog'), (2, 'Jane Doe's Blog'), INSERT INTO `posts`(`blog_id`, `title`, `body`, `created`, `modified`) VALUES (1, 'John's Post 1', 'Body for John's Post 1', '2010-04-19 14:00:00', '2010-04-19 14:00:00'), (1, 'John's Post 2', 'Body for John's Post 2', '2010-04-19 14:30:00', '2010-04-19 14:30:00'), (1, 'John's Post 3', 'Body for John's Post 3', '2010-04-20 14:00:00', '2010-04-20 14:00:00'), (1, 'John's Post 4', 'Body for John's Post 4', '2010-05-03 14:00:00', '2010-05-03 14:00:00'), (2, 'Jane's Post 1', 'Body for Jane's Post 1', '2010-04-19 15:00:00', '2010-04-19 15:00:00'), (2, 'Jane's Post 2', 'Body for Jane's Post 2', '2010-06-18 15:00:00', '2010-06-18 15:00:00'), (2, 'Jane's Post 3', 'Body for Jane's Post 3', '2010-10-06 15:00:00', '2010-10-06 15:00:00'),
Post
in a file named post.php
and place it in your app/models
folder with the following contents:<?php class Post extends AppModel { public $belongsTo = array('Blog'), } ?>
index()
method of a controller. Create a file named posts_controller.php
and place it in your app/controllers
folder with the following contents:<?php class PostsController extends AppController { public function index() { $this->set(compact('data')); } } ?>
posts
and place it in your app/views
folder. Inside this newly created folder, create a file named index.ctp
, with the following contents:<?php debug($data); ?>
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); } } ?>
Whenever you override a model method, such as beforeFind()
or afterFind()
, make sure you call the parent implementation by using the parent
keyword.
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.
18.189.188.121