Rails SQL COUNT N+1 inefficiency

Question

I have a blog. On my index page, I pull in all blog posts. For each blog post, I count the number of comments on that post. This leads to an N+1 problem. My queries look as follows:

SELECT "blog_posts".* FROM "blog_posts" WHERE ("blog_posts"."published" = 't') ORDER BY published_at DESC
SELECT "users".* FROM "users" WHERE ("users"."id" IN (1, 2, 3)) 
SELECT COUNT(*) FROM "blog_comments" WHERE ("blog_comments".blog_post_id = 10)
SELECT COUNT(*) FROM "blog_comments" WHERE ("blog_comments".blog_post_id = 9)
SELECT COUNT(*) FROM "blog_comments" WHERE ("blog_comments".blog_post_id = 8)
SELECT COUNT(*) FROM "blog_comments" WHERE ("blog_comments".blog_post_id = 2)
SELECT COUNT(*) FROM "blog_comments" WHERE ("blog_comments".blog_post_id = 7) 

Is there a way in Rails to include the COUNT in the same way I include the users (SQL line 2)?

Answer 1

You can use dase gem or one of techniques explained in that video.

Example with Dase:

Author.includes_count_of(:articles).each do |author| puts "#{author.name} has #{author.articles_count} articles" end

Answer 2

You can use counter cache: http://guides.rubyonrails.org/association_basics.html#counter_cache

"With this declaration, Rails will keep the cache value up to date, and then return that value in response to the size method."

class BlogPost < ActiveRecord::Base
  has_many :blog_comments
end

class BlogComment < ActiveRecord::Base
  belongs_to :blog_post, :counter_cache => true
end

Blog post would have a column named blog_comments_count.