Benito Serna
Ruby, Rails, TDD...

What is the difference between includes, preload, eager_load and joins in ActiveRecord?

Are you dealing with includes, preload, eager_load and joins, maybe fixing some n+1 queries, but you are not really sure what’s the difference between all of them?

Would you like to be able to say things like… “Here I will use preload instead of includes because I have tested it and in this case an INNER JOIN is faster than a LEFT OUTER JOIN”

Well, here is a little guide to understand what they do and what is the difference between them, to help you decide which is better for your case.

Example data

To explain the concepts imagine that you have the next data in your database

class Post < ActiveRecord::Base
  has_many :comments
end

class Comment < ActiveRecord::Base
  belongs_to :post
end

post = Post.create(title: "Post 1", body: "Post 1 body")
post.comments.create(body: "Comment 1")
post.comments.create(body: "Comment 2")

post = Post.create(title: "Post 2", body: "Post 2 body")
post.comments.create(body: "Comment 1")
post.comments.create(body: "Comment 2")

Preload

It preloads the associociations using different queries.

For example, this code will use one query for the posts and other for the comments.

posts = Post.preload(:comments).to_a
# SELECT "posts".* FROM "posts"
# SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN ($1, $2)  [[nil, 1], [nil, 2]]

And you will be able to use the comments without n+1 queries.

posts.map(&:comments)

You can include conditions for the base model.

posts = Post.preload(:comments).where(title: "Post 1").to_a
# SELECT "posts".* FROM "posts" WHERE "posts"."title" = $1  [["title", "Post 1"]]
# SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN ($1, $2)  [[nil, 1], [nil, 2]]

But you can’t add conditions for the preloaded associations.

Post.preload(:comments).where(comments: {body: "Hola"}).to_a
# `prepare': PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "comments" (ActiveRecord::StatementInvalid)
# LINE 1: SELECT "posts".* FROM "posts" WHERE "comments"."body" = $1

Eager load

It forces the eager loading performing a LEFT OUTER JOIN.

For example, this code will use just one query to load both, the posts and the comments.

posts = Post.eager_load(:comments).to_a
# SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."body" AS t0_r2, "comments"."id" AS t1_r0, "comments"."body" AS t1_r1, "comments"."post_id" AS t1_r2 FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id"

And you will be able to use the comments without n+1 queries.

posts.map(&:comments)

You will be able to add conditions for the base model.

Post.eager_load(:comments).where(title: "Hola").to_a
# SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."body" AS t0_r2, "comments"."id" AS t1_r0, "comments"."body" AS t1_r1, "comments"."post_id" AS t1_r2 FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id" WHERE "posts"."title" = $1  [["title", "Hola"]]

And you also will be able to add conditions for the associated models.

Post.eager_load(:comments).where(comments: {body: "Hola"}).to_a
# SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."body" AS t0_r2, "comments"."id" AS t1_r0, "comments"."body" AS t1_r1, "comments"."post_id" AS t1_r2 FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id" WHERE "comments"."body" = $1  [["body", "Hola"]]

Includes

By default it will load the included associations using different queries. Like preload.

For example, this code will use one query for the posts and other for the comments.

posts = Post.includes(:comments).to_a
# SELECT "posts".* FROM "posts"
# SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN ($1, $2)  [[nil, 1], [nil, 2]]

And you will be able to use the comments without new queries.

posts.map(&:comments)

You will also be able to include add conditions for the base model.

# Post.includes(:comments).where(title: "Hola").to_a
# SELECT "posts".* FROM "posts" WHERE "posts"."title" = $1  [["title", "Post 1"]]
# SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN ($1, $2)  [[nil, 1], [nil, 2]]

And in this case, you will also be able to add conditions for the included associations. Because it will perform a LEFT OUTER JOIN like eager load does.

Post.includes(:comments).where(comments: {body: "Hola"}).to_a
# SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."body" AS t0_r2, "comments"."id" AS t1_r0, "comments"."body" AS t1_r1, "comments"."post_id" AS t1_r2 FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id" WHERE "comments"."body" = $1  [["body", "Hola"]]

Note that if you want to pass the coditions as strings, you will need to use the references method, like this…

Post.includes(:comments).where("comments.body = ?", "Hola"}).references(:comments).to_a

So, includes is like a mix between preload and eager_load.

Joins

By default it will perform an INNER JOIN that you could use to create a more specific query using the joined tables.

For example you can ask for the comments for a post with an specific title, like this…

comments = Comment.joins(:post).where(post: {title: "Post 1"}).to_a
# SELECT "comments".* FROM "comments" INNER JOIN "posts" post ON post."id" = "comments"."post_id" WHERE "post"."title" = $1  [["title", "Post 1"]]

But it will not preload the post for each comment.

# comments.map(&:post)
# SELECT "posts".* FROM "posts" WHERE "posts"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
# SELECT "posts".* FROM "posts" WHERE "posts"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]

If you want to preload the post, you would need to use one of the methods above (includes, preload, eager load)…

For example…

comments = Comment.joins(:post).where(post: {title: "Post 1"}).preload(:post).to_a
# SELECT "comments".* FROM "comments" INNER JOIN "posts" post ON post."id" = "comments"."post_id" WHERE "post"."title" = $1  [["title", "Post 1"]]
# SELECT "posts".* FROM "posts" WHERE "posts"."id" = $1  [["id", 2]]

And now you will avoid the n+1 queries when you do…

comments.map(&:post)

Do you want more examples?

Maybe more examples will help you understand this concepts better. I have prepared some examples to help you understand how you can use this 4 methods. Give it look!

Examples to learn the difference between preload, includes or eager_load

Do you want to practice?

Practice will help you understand this concepts better. I have prepared some examples/excercises to help you understand how you can use this 4 methods. Give it try!

Quiz to practice the difference between preload, includes or eager_load

Useful links

You can use the rails docs to learn more:

Related articles