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

19

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

answered Sep 20, 2012 at 22:10

Brad

5,4681 gold badge36 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fitter Man Over a year ago

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.

Brad Over a year ago

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

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related