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.
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...