Find all posts by this post

Question

I have a simple rails 3 blogging application in which posts contain many comments and commentary on the post.

I want to create an area that will retrieve all posts with more than 5 comments. What is the best way to do this without a cache counter column.

Answer 1

How is this possible?

Post.select('posts.*, count(comments.id) as comment_count'). joins(:comments). group('posts.id'). having('comment_count > 5') 

Answer 2

In Postgres 9.1, I had to set it up so that postgres did not like the conditions for computed fields or something like that.

 Post.select('posts.*, count(comments.id) as comment_count'). joins(:comments). group('posts.id'). having('count(comments.id) > 5') 

Answer 3

Great answer from noodl ... thanks for that!

I needed to find - stick with the sample classes of the original question - 4 posts that were recently commented ... a small answer to noodl does the trick:

 Post.select('posts.*, max(comments.created_at) as last_commented_at'). joins(:comments). group('posts.id'). order('last_commented_at DESC'). limit(4) 

Thanks!