How do I get Rails to eager load counts?

Question

This is related to a question a year and change ago.

I put up an example of the question that should work out of the box, provided you have sqlite3 available: https://github.com/cairo140/rails-eager-loading-counts-demo

Installation instructions (for the main branch)

git clone git://github.com/cairo140/rails-eager-loading-counts-demo.git
cd rails-eager-loading-counts-demo
rails s

I have a fuller write-up in the repository, but my general question is this.

How can I make Rails eager load counts in a way that minimizes db queries across the board?

The n+1 problem emerges whenever you use #count on an association, despite having included that association via #includes(:associated) in the ActiveRelation. A workaround is to use #length, but this works well only when the object it's being called on has already been loaded up, not to mention that I suspect it duplicates something that the Rails internals have done already. Also, an issue with using #length is that it results in an unfortunate over-loading when the association was not loaded to begin with and the count is all you need.

From the readme:

We can dodge this issue by running #length on the posts array (see appendix), which is already loaded, but it would be nice to have count readily available as well. Not only is it more consistent; it provides a path of access that doesn't necessarily require posts to be loaded. For instance, if you have a partial that displays the count no matter what, but half the time, the partial is called with posts loaded and half the time without, you are faced with the following scenario:

• Using #count
  • n COUNT style queries when posts are already loaded
  • n COUNT style queries when posts are not already loaded
• Using #length
  • Zero additional queries when posts are already loaded
  • n * style queries when posts are not already loaded

Between these two choices, there is no dominant option. But it would be nice to revise #count to defer to #length or access the length that is some other way stored behind the scenes so that we can have the following scenario:

• Using revised #count
  • Zero additional queries when posts are already loaded
  • n COUNT style queries when posts are not already loaded

So what's the correct approach here? Is there something I've overlooked (very, very likely)?

Answer 1

As @apneadiving suggested, counter_cache works well because the counter column gets automatically updated when records are added or removed. So when you load the parent object, the count is included in the object without needing to access the other table.

However, if for whatever reason you don't like that approach, you could do this:

Post.find(:all,
          :select => "posts.*, count(comments.id) `comments_count`",
          :joins  => "left join comments on comments.post_id = posts.id")

Answer 2

An alternative approach to the one of Zubin:

Post.select('posts.*, count(comments.id) `comments_count`').joins(:comments).group('posts.id')