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