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.
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")
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
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"]]
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.
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)
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
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
You can use the rails docs to learn more:
Learn just enough fundamentals to be fluent preloading associations with ActiveRecord, and start helping your team to avoid n+1 queries on production.