Giuseppe Giuseppe - 1 year ago 110
SQL Question

Counting associated records in Rails

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


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

SELECT COUNT( AS count_comments_id, AS posts_id

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


SELECT COUNT( AS count_comments_id, AS posts_id
FROM `posts` INNER JOIN `comments` ON `comments`.`post_id` = `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( AS count_comments_id, AS posts_id
FROM `posts` LEFT OUTER JOIN `comments` ON `comments`.`post_id` = `posts`.`id`

Answer Source

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:"posts.*, COUNT( as comment_count").joins("LEFT OUTER JOIN comments ON (comments.post_id =").group("")

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.