How to Properly Eager Load Associations

  • December 18, 2019
  • Pete Whiting
  • 3 min read

by Mark Lodato

TL;DR

  • Ensure any eager loading is correctly preventing any unwanted queries
  • Be especially careful when working with has_many through associations.

Eager Loading in the Dark

Assume we have the following setup:

class User < ApplicationRecord
  has_many :pets
end

class Pet < ApplicationRecord
  belongs_to :user
end

A common mistake to make in ActiveRecord is an N+1 query:

User.all.map do |user|
  user.pets
end

This code will make one query to get all the users and then query the pets table once per
user (and, if we have N users, that's N+1 queries)! The solution
for this is eager loading with the includes statement:

User.all.includes(:pets).map do |user|  user.pets
end

This code is going to execute one query to get all the users and one query to get all the
pets for the returned users. Not bad going from N+1 to 2 queries with one statement!

There is a nuance here, though. Let's say we have this setup:

class User < ApplicationRecord
  has_many :pets
end

class Pet < ApplicationRecord
  belongs_to :user
  has_many :toysend

class Toy < ApplicationRecord  belongs_to :petend

And we have this query which is appropriately eager loaded:

User
  .includes(pets: :toys)
  .map { |user| user.pets.map(&:toys) }

# User Load (0.4ms)  SELECT "users".* FROM "users"
# Pet Load (0.4ms)  SELECT "pets".* FROM "pets" WHERE "pets"."user_id" IN ($1, $2)  [["user_id", 1], ["user_id", 2]]
# Toy Load (0.3ms)  SELECT "toys".* FROM "toys" WHERE "toys"."pet_id" IN ($1, $2, $3, $4)  [["pet_id", 1], ["pet_id", 2], ["pet_id", 3], ["pet_id", 4]]

Then a good citizen notices that we dig from users to toys often and makes this change:

class User < ApplicationRecord
  has_many :pets
  has_many :toys, through :petsend

And updates the query accordingly:

User
  .includes(pets: :toys)
  .map { |user| user.toys }
# User Load (0.4ms)  SELECT "users".* FROM "users"
# Pet Load (0.3ms)  SELECT "pets".* FROM "pets" WHERE "pets"."user_id" IN ($1, $2)  [["user_id", 1], ["user_id", 2]]
# Toy Load (0.3ms)  SELECT "toys".* FROM "toys" WHERE "toys"."pet_id" IN ($1, $2, $3, $4)  [["pet_id", 1], ["pet_id", 2], ["pet_id", 3], ["pet_id", 4]]
# Toy Load (0.3ms)  SELECT "toys".* FROM "toys" INNER JOIN "pets" ON "toys"."pet_id" = "pets"."id" WHERE "pets"."user_id" = $1  [["user_id", 1]]
# Toy Load (0.3ms)  SELECT "toys".* FROM "toys" INNER JOIN "pets" ON "toys"."pet_id" = "pets"."id" WHERE "pets"."user_id" = $1  [["user_id", 2]]

This introduced an N+1 query! Even worse, we're eager loading toys through pets and then
querying toys once per user! Because we're eager loading toys through pets but are accessing
toys "directly" from users, ActiveRecord requeries toys for each iteration!
Now that we have the has_many through: relationship set up, we should skip the
intermediate key in the includes statement:

User
  .includes(:toys)  .map { |user| user.toys }

# User Load (0.5ms)  SELECT "users".* FROM "users"
# Pet Load (0.3ms)  SELECT "pets".* FROM "pets" WHERE "pets"."user_id" IN ($1, $2)  [["user_id", 1], ["user_id", 2]]
# Toy Load (0.2ms)  SELECT "toys".* FROM "toys" WHERE "toys"."pet_id" IN ($1, $2, $3, $4)  [["pet_id", 1], ["pet_id", 2], ["pet_id", 3], ["pet_id", 4]]

Summary

  1. Eager loading is a useful tool to prevent extra queries from slowing down a page load or stressing
    a database.
  2. ActiveRecord provides convenient utilities to eager load associated records in
    many kinds of situations.
  3. When eager loading, ensure via testing that the extra queries have been removed as Rails might
    read your eager loads differently than you intend.

Interested in building with us?