CakePHP: How to count the number of hasMany entries in a search?

Question

I have two models, Post hasMany Comment . How to select all Post that have less than two Comment ?

I tried using find with 'fields'=>array('COUNT(Comment.id) as numComments','Post.*') , (And then doing numComments < 2 in 'conditions' ). But I get an Unknown column 'Comment.id' in 'field list' error.

Thanks!

EDIT: I created CakePHP to create this request:

 SELECT `Post`.*, FROM `posts` AS `Post` LEFT JOIN comments AS `Comment` ON (`Post`.`id` = `Comment`.`text_request_id`) WHERE COUNT(`Comment`.`id`) < 2 GROUP BY `Comment`.`post_id` LIMIT 10 

But I get error message #1111 - Invalid use of group function in COUNT function.

EDIT: Allowed to use HAVING COUNT instead of WHERE COUNT.

Answer 1

 class Post extends AppModel { var $name = "Post"; var $hasMany = array('Comment'=>array('counterCache'=>true)); } 

add comment_count fields to posts

an that everything :-)

Answer 2

In raw SQL, the query would look something like this:

 SELECT Post.* FROM Post LEFT JOIN Comment ON Post.id = Comment.post_id GROUP BY Comment.post_id HAVING COUNT(Comment.id) < 2 

Most of them are easy to translate to Cake:

 array( 'having' => array('COUNT(Comment.id) <' => 2), 'group' => array('Comment.post_id') ) 

Cake does not automatically join hasMany tables; this is what you need to do manually. See the documentation for details.

Edit:

You can make a suggestion as follows:

 array( 'group' => 'Comment.post_id HAVING COUNT(Comment.id) < 2' ) 

string restrictions are only for the group and are impossible without the group. Cake 3 will probably contain more SQL syntax like having

Answer 3

If anyone is interested: here is the document on counterCache http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#belongsto and here is more about adding a few counterCache fields http: // book .cakephp.org / 2.0 / en / models / associations-linking-models-together.html # multiple-countercache