ActiveRecord: count with groups

Question

I have a model of a post with has_many comments. Every post has a thread id (but there is no model named Thread).

So if I want to count the number of threads in this post I do smth like

> post.comments.count(:thread, :distinct => true) SELECT COUNT(DISTINCT "comments"."thread") FROM "comments" WHERE "comments"."post_id" = 3

And this works fine. But what if I want to count the number of threads with only one comment?

> post.comments.group(:thread).having('COUNT(*) == 1').count SELECT COUNT(*) AS count_all, thread AS thread FROM "comments" WHERE "comments"."post_id" = 3 GROUP BY thread HAVING COUNT(*) == 1 ORDER BY id

So I have a OrderedHash instead of Integer. And I have to do the unnecessary step

> post.comments.group(:thread).having('COUNT(*) == 1').count.count

Is there any better solution?

Brad · Accepted Answer · 2012-09-20 22:10:54Z

This is expected behavior.

post.comments.group(:thread).having('COUNT(*) == 1').count

This is returning a hash where the key is the thread id and the value is the count. I believe this is the case anytime you do a group with an aggregate function in rails. You must do the second count to get the number of results that the first query matches.

I am not sure how this would look in Rails, but here is the SQL that I think you want:

SELECT COUNT(*) FROM SELECT COUNT(*) AS count_all, thread AS thread FROM "comments" WHERE "comments"."post_id" = 3 GROUP BY thread HAVING COUNT(*) == 1 ORDER BY id

Generally SQL expects "=" not "==" for equivalence. Your answer is probably correct for some brands of SQL but it does fail on MySQL without this change.
That is correct. However I was trying to be consistent with the format he was using in the original post.

Collectives™ on Stack Overflow

ActiveRecord: count with groups

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related