Easy to Overlook Way to Break Eager Loading in Rails Apps

In theory, configuring eager loading to avoid N+1 issues is straightforward. Chaining includes method is usually enough to ensure that all the relations data is fetched efficiently. However, this rule breaks for some easy-to-overlook edge cases. In this blog post, I’ll describe how to eager load ActiveRecord relations using custom SQL ORDER BY sorting.

How to use eager loading with ORDER BY in SQL?

Based on my experience conducting Rails performance audits, N+1 issues are a primary cause of sub-optimal response times in Ruby on Rails apps. ActiveRecord makes it just too easy to sneak in an N+1 bug in production. It’s easy to miss it in development because you usually work with small datasets.

Let’s analyze a sample scenario of displaying the products of a user:

app/models/user.rb

class User < ApplicationRecord
  has_many :products
end

app/models/product.rb

class Product < ApplicationRecord
  belongs_to :user

  scope :by_rating, -> { order(rating: :desc) }
end

app/controllers/users_controller.rb

class UsersController < ApplicationController
  def index
    @users = User.limit(50).includes(:products)
  end
end

In theory, this implementation should correctly eager-load all the product objects and prevent N+1 queries.

But let’s consider the following view layer implementation:

app/views/users/index.html.erb

<div>
  <% @users.each do |user| %>
    <p> <%= user.id %> products </p>
    <ul>
      <% user.products.by_rating.each do |product| %>
        <li><%= product.name %></li>
      <% end %>
    </ul>
  <% end %>
</div>

The addition of by_rating scope effectively breaks the eager loading. Adding a where call would have the same effect. It is caused by chaining methods that alter the relation SQL and invalidate the previously eager loaded collection. It’s even worse than not using eager loading at all. includes still triggers additional queries, but results are discarded.

A naive approach to avoiding N+1 issues in similar situations could be to work with methods that do not alter the relation SQL. It means that instead of adding by_rating scope, you’d need to use a method that does the sorting in Ruby:

app/views/products/index.html.erb

<% user.products.sort_by { |pr| -pr.rating }.each do |product| %>
  <li><%= product.name %></li>
<% end %>

Similarly, instead of where, you could use the select method. It filters collection in Ruby instead of an SQL WHERE clause.

Using custom relation to prevent N+1 queries

However, there are downsides to using Ruby instead of SQL to transform relation objects. For example, sorting in Ruby can be slower than SQL ORDER BY or even impossible for larger data sizes. And using select to filter the collection means that you’ll discard already initialized ActiveRecord objects, so memory is wasted.

A better way to work with relations that need their SQL customized is to use a dedicated relation method. Let’s see it in action:

class User < ApplicationRecord
  has_many :products
  has_many :products_by_rating, -> { by_rating }, class_name: 'Product'
end

app/controllers/users_controller.rb

class UsersController < ApplicationController
  def index
    @users = User.limit(50).includes(:products_by_rating)
  end
end

app/views/products/index.html.erb

<% user.products_by_rating.each do |product| %>
  <li><%= product.name %></li>
<% end %>

This approach eliminates the additional N+1 queries while preserving custom order without a need for sorting in Ruby.

For non-trivial views, it could be challenging to prevent N+1 issues from emerging accidentally. For example, even custom relations could still trigger N+1 queries if their SQL is further customized. One way to avoid it could be to explicitly call to_a on relations collections before using them. As an effect, ActiveRecord::Relation is transformed into an Array of objects, making it impossible to chain SQL calls.

user.products.to_a.by_rating

# raises => undefined method `by_rating' for Array (NoMethodError)

But it’s arguably not the cleanest solution.

Unfortunatelly, even using the recently added strict_loading on a relation would not detect similar mistakes:

app/models/user.rb

class User < ApplicationRecord
  has_many :products, strict_loading: true
end

User.last(2).map(&:products).map(&:to_a)

# raises => ActiveRecord::StrictLoadingViolationError

User.last(2).map(&:products).map(&:by_rating).map(&:to_a)

# loads the Products but triggers N+1 queries

Summary

You should continuously monitor the number of queries executed by your application. I always recommend using rack-mini-profiler and regularly inspecting SQL queries triggered by each view. This 10-year-old Railscast is still up to date, so the tool did stand the test of time. Getting rid of sneaky N+1 issues could be the lowest-hanging fruit in optimizing your Rails app’s performance.