Hello
I have two models, Post
hasMany Comment
. How do I select all Post
that have less than two Comment
?
I tried using a find
with 'fields'=>array('COUNT(Comment.id) as numComments','Post.*')
, (and then doing a numComments < 2
in 'conditions'
). But, I get a Unknown column 'Comment.id' in 'field list'
error.
Thanks!
EDIT: I've gotten CakePHP to generate this query:
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 an error #1111 - Invalid use of group function
on the COUNT
function.
EDIT: Resolved, use the HAVING COUNT instead of WHERE COUNT.