cakephp find all posts that have comments

Question

I created a simple blog that has posts and comments. I want to find all the posts that have at least one comment and also find all the posts with no comments. Is there a cakephp way to do this? I.E. maybe something like

$this->Post->find('all', ???);

I ended up writing my own query, the example below finds all the posts with at least 1 comment

SELECT *
  FROM (
       select posts.*, count(comments.id) as comment_count
        from posts left join comments on posts.id = comments.post_id
        group by posts.id
       ) as T
 WHERE comment_count != 0

but there seems like there would be a better way to do this.

Note: a Post hasMany Comment and Comment belongsTo Post

Answer 1

One solution would be:

$this->Post->find('all');

And your relationships are setup as you say above, the comments will already be returned with this information, you just need to run a count on them and handle the processing from there... something like:

$posts = $this->Post->find('all');
count($posts[0][Comment]);

There will likely be a more elegant way - as I'm new to CakePHP myself, but hopefully this has some value.

Answer 2

$grouped_comments = $this->Comment->find('all', array('group' => 'Comment.post_id'));

This will give you an array of all Comments grouped by post_id so you will have exactly one comment for each post, which is what you want. From there you can do whatever you want with that data.

Let's say you wanted to post a list of all post titles with comments.

echo "<H1>Posts with comments:</H1>";
foreach ($grouped_comments as $comment) {
echo $comment['Post']['Title'] . "<br>";
}

This of course only works if you have your model relationships set up in your comment.php model.

Answer 3

I'm still fairly new to CakePHP, but I think you could use counterCache to track the # of Comments per Post model record in a field in your Posts table, and then use some simple conditions in your Find to get what you are looking for. counterCache automatically updates the cache count field in the Parent model record field when children model records are added/deleted.

counterCache for CakePHP 1.2: http://book.cakephp.org/view/816/counterCache-Cache-your-count