Find most commented posts

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

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());

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

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.

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.