Counting associated records in Rails

Question

I am trying to get the number of comments from each of the posts in the database. The following, however:

Post.includes(:comments).group("posts.id").count("comments.id")

raises the mysql error "Unknown column comments.id", since the generated sql seems to completely ignore the includes():

SELECT COUNT(comments.id) AS count_comments_id, posts.id AS posts_id
FROM `posts` GROUP BY posts.id

Interestingly, replacing includes() with joins() will produce working sql:

Post.joins(:comments).group("posts.id").count("comments.id")

SELECT COUNT(comments.id) AS count_comments_id, posts.id AS posts_id
FROM `posts` INNER JOIN `comments` ON `comments`.`post_id` = `posts`.`id`
GROUP BY posts.id

but the above query excludes all posts with 0 comments, which is not what I want. What I do need is to produce the following SQL (but without writing SQL, he he he)

SELECT COUNT(comments.id) AS count_comments_id, posts.id AS posts_id
FROM `posts` LEFT OUTER JOIN `comments` ON `comments`.`post_id` = `posts`.`id`
GROUP BY posts.id

score 20 · Accepted Answer · edited May 23 '17 at 12:22

20

The includes method will not do a join in all cases but rather batch-fetch the association for performance reasons (see Rails :include vs. :joins).

What you need to do is a joins and you where almost on the correct path but got the group clause a bit wrong:

Post.select("posts.*, COUNT(comments.id) as comment_count").joins("LEFT OUTER JOIN comments ON (comments.post_id = posts.id)").group("posts.id")

Note that this solution has the benefit or actually returning Post objects (using .count() returns a Hash on my Rails 3.2) so you can loop through actual post objects in your view and also access the property comment_count.

edited May 23 '17 at 12:22

Community

1
1

answered Jun 18 '12 at 08:02

Tigraine

23,358
11
65
110

Right, but the INNER JOIN excludes all posts without comments. What I need is the complete list of posts, with the calculated field (i.e. comment_count) set to 0 when appropriate – Giuseppe Jun 19 '12 at 06:14
Please see my own answer to see what I mean. Adding the explicit LEFT JOIN to your solution does not seem to work. All results with 0 comments end up aggregated into one, not sure why. – Giuseppe Jun 19 '12 at 06:41
Because it's grouping by comments.id. Since all comments that don't have comments have comments.id null they get added up. Just change the group clause to `posts.id` and it should be fine.. I updated the answer – Tigraine Jun 19 '12 at 07:36
Syntax needs fixing (missing parenthesis at posts.id): `Post.select("posts.*, COUNT(comments.id) as comment_count").joins("LEFT OUTER JOIN comments ON (comments.post_id = posts.id)").group("posts.id")` – Mythical Fish Jul 19 '16 at 08:58
Thanks @Vertaxe fixed it. – Tigraine Jul 26 '16 at 09:05

score 10 · Answer 2 · answered Nov 30 '17 at 05:44

10

This problem is actually way simpler nowadays:

Comment.joins(:post).group(:post_id).count(:post_id)

This will give you a map of {<post_id> => <count of comments>}

answered Nov 30 '17 at 05:44

Peter P.

3,221
2
25
31

1

Actually, no. Your code generates an `INNER JOIN`, which by definition excludes posts without associated comments. Thus, posts with zero comments are not retrieved. – Giuseppe Apr 24 '18 at 07:29
1

true. Since an inner join query is more efficient than outer join (by definition), i back fill the 0 records in ruby which is O(n) and quite fast. You can debate that null filling records in ruby might be slower than the db though. – Peter P. Apr 25 '18 at 18:34
The counts can be sorted with: Comment.joins(:post).group(:post_id).count(:post_id).sort_by {|_key, value| value} – a2f0 Apr 24 '19 at 03:25

Harish Shetty · Answer 3 · 2012-06-19T15:34:38.827

2

Try this:

Post.select("COUNT(comments.id) AS count_comments_id, posts.id AS posts_id").
  includes(:comments).group("posts.id")

edited Jun 19 '12 at 15:34

answered Jun 18 '12 at 06:12

Harish Shetty

64,083
21
152
198

You have a typo: includes(:comments) – Giuseppe Jun 19 '12 at 05:57
This simply returns an array of post models and the select clause is ignored (there is no COUNT in the generated query) – Giuseppe Jun 19 '12 at 06:04

score 0 · Answer 4 · answered Jun 19 '12 at 06:36

If all you need is the hash of post counts: {post_id1 => cnt1, post_id2 => cnt2, ...} then forcing a left join on the association will work:

  Post.joins("LEFT OUTER JOIN comments on posts.id=comments.post_id").
  group("posts.id").count("comments.id")

score 0 · Answer 5 · answered Dec 08 '21 at 08:43

For rails 5 or above, we have a activerecord method left_outer_joins & we don't need to write the join logic manually.

Post.left_outer_joins(:comments)
    .distinct
    .select('posts.*, COUNT(comments.*) AS comments_count')
    .group('posts.id')

Which produces:

SELECT DISTINCT posts.*, COUNT(comments.*) AS comments_count FROM posts 
LEFT OUTER JOIN comments ON comments.post_id = posts.id GROUP BY posts.id

Counting associated records in Rails

5 Answers5