Find most commented posts

Question

I have a blog, and I would like to find out the best way to find the most commented posts. I have a straight setup with posts have many comments, and comments belong to posts.

I am trying to find a 1 line "right way" to do this, but can't find it. I would like to get like a top 10 commented posts.

Thanks

Answer 1

If CakePHP will allow you to use straight SQL queries:

SELECT
  POST.POST_ID
  COUNT(*)
FROM
  POST_TABLE POST
  INNER JOIN COMMENT_TABLE COMMENT ON COMMENT.POST_ID = POST.POST_ID
GROUP BY POST.POST_ID
ORDER BY COUNT(*)

should do the trick, and will probably be quite efficient.

Answer 2

Do you have this in a database? If so, maybe you could use a calculated field in the post table to keep track of the number of comments for each post.

Then just use

SELECT TOP 10 FROM Post ORDER BY CommentNumber DESC

Answer 3

I would hybridize the two approaches, mostly b/c I don't know your constraints or your primary concerns. Either Adams or moranu's solutions will work, although differently.

If you have a big DB, and performance is an issue, you should take the first approach. It will require you to increment that number each time a new comment is added. Alternatively, if your site doesn't need that top-10 thing to be updated frequently, you could just cache your "top 10 commented posts" feed.

My suggestion: if it's too difficult to increment the CommentNumber field (legacy code, etc.) you could run an hourly / daily script to update that number for you, by executing the second query. I would probably suggest that you pick and choose from these options as they fit your requirements.

Answer 4

CakePHP has counterCache functionality for this exact purpose.

Ensure your posts table contains a 'comment_count' field.

Then add the counterCache key to the Comment model's belongsTo Post settings.

// app/models/comment.php
var $belongsTo = array(
  'Post' => array(
    'counterCache' => true
  )
);

CakePHP will now automatically increment / decrement the Post.comment_count field everytime a comment belonging to that post is added or deleted.

To get the top 10 most commented posts:

// app/models/post.php
function getTopCommentedPosts($limit = 10) {
  return $this->find('all', array(
    'order' => 'Post.comment_count DESC',
    'limit' => $limit
  ));
}

Then call this method from any controller action that requires this data. If from a controller other than the PostsController use:

$this->set('posts', ClassRegistry::init('Post')->getTopCommentedPosts());