Performing GROUP and COUNT queries
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.
Getting ready
To go through this recipe we need some sample tables to work with.
1. Create a table named
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`) );
2. Create a table named
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`) );
3. Create a table named
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...