ActiveRecord Grouping/Count Query Help Needed

I am using Rails 3.

Suppose I have the following archetypical models: Blog, Post, Comment, Category. Blog has_many posts, Post has_many comments and belongs_to category.

I want to know how many posts are in each category but only counting those that have comments.

If I do the following:

blog.posts.group("category_id").count

--This gives me an OrderedHash which is close to what I need but of course the problem is the count values include posts that have no comments.

If I do:

blog.posts.group("category_id").joins("comments").count

--this gives me the number of comments that are in posts of that category (it does the same thing if I put the jo开发者_如何学Goin before the grouping).

Thanks for any help.

Giving 'having' a try. Just hacked this together quickly....

Post.select("posts.id, posts.category_id, count(comments.id)").join(:comments).group('posts.category_id').having('count(comments.id) > 0')

Which would translate to something like...

select posts.id, posts.category_id, count(comments.id) from posts join comments on posts.id = comments.post_id group by posts.category_id having count(comments.id) > 0

Might have to iterate on this a small bit...

A simple option which will help to keep your query simple and readable would be to set up a counter_cache in your post-comment association so you can just add where("comments_count > 0") in your find.