ActiveRecord: counting with groups

I have a has_many comment post model. Each post has a thread identifier (but there is no model called Thread).

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

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

And it works great. But what if I want to count the number of threads with just 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 OrderedHash instead of Integer. And I have to take an unnecessary step

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

Is there a better solution?

+6
source share
1 answer

This is the expected behavior.

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

This returns a hash where the key is the stream identifier and the value is the counter. I believe that this is so when you make a group with an aggregate function in rails. You must make a second counter to get the number of results that match the first query.

I'm not sure how this will look in Rails, but here is the SQL that I think you need:

 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 
+12
source

Source: https://habr.com/ru/post/909623/


All Articles