Rails 3 ActiveRecord Query, return all records of a model and include a count of associated records in a single query

Question

I have the following models:

class Keyword < ActiveRecord::Base
  has_many :tags
  has_many :studies, :through => :tags
end

class Tag < ActiveRecord::Base
  belongs_to :keyword
  belongs_to :study
end

class Study < ActiveRecord::Base
  has_many :tags
  has_many :keywords, :through => :tags
end

I want an array containing all of the keywords, and for each keyword, and count of the studies tagged with that keyword, so that I can say:

<ul>
<% @keywords.each do |keyword| %>
  <li><%= "(#{keyword.number_of_studies}) #{keyword.title}") %></li>
<% end %>
</ul>

I'm sure this is possible, the question is: how?

Answer 1

You could use the counter cache. This keeps an integer value on the parent object with the number of children. ActiveRecord keeps track.

class Post < AR::Base
  has_many :comments
end

class Comment < AR::Base
  belongs_to :post, :counter_cache => true
end

@post.comments_count

If you really must use one query without the counter cache:

@post = Post.find(first, :select => 'posts.*, count(comments.id) as comments_count',:joins => 'left outer join comments on comments.post_id = posts.id', :group => 'posts.id')
@post.comments_count #=> From join query.

As you can see, this gets ugly fast. It's better to stick with the counter cache, or do two separate queries.

@post = Post.find(:first) # SELECT FROM posts
@post.comments.count      # SELECT COUNT(1) FROM comments WHERE post_id = ?