Preload, Eagerload, Includes and Joins

Rails provides four different ways to load association data. In this blog we are going to look at each of those.

Preload

Preload loads the association data in a separate query.

1User.preload(:posts).to_a
2
3# =>
4SELECT "users".* FROM "users"
5SELECT "posts".* FROM "posts"  WHERE "posts"."user_id" IN (1)
Copy

This is how includes loads data in the default case.

Since preload always generates two sql we can't use posts table in where condition. Following query will result in an error.

1User.preload(:posts).where("posts.desc='ruby is awesome'")
2
3# =>
4SQLite3::SQLException: no such column: posts.desc:
5SELECT "users".* FROM "users"  WHERE (posts.desc='ruby is awesome')
6
Copy

With preload where clauses can be applied.

1User.preload(:posts).where("users.name='Neeraj'")
2
3# =>
4SELECT "users".* FROM "users"  WHERE (users.name='Neeraj')
5SELECT "posts".* FROM "posts"  WHERE "posts"."user_id" IN (3)
6
Copy

Includes

Includes loads the association data in a separate query just like preload.

However it is smarter than preload. Above we saw that preload failed for query User.preload(:posts).where("posts.desc='ruby is awesome'"). Let's try same with includes.

1User.includes(:posts).where('posts.desc = "ruby is awesome"').to_a
2
3# =>
4SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "posts"."id" AS t1_r0,
5       "posts"."title" AS t1_r1,
6       "posts"."user_id" AS t1_r2, "posts"."desc" AS t1_r3
7FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id"
8WHERE (posts.desc = "ruby is awesome")
9
Copy

As you can see includes switches from using two separate queries to creating a single LEFT OUTER JOIN to get the data. And it also applied the supplied condition.

So includes changes from two queries to a single query in some cases. By default for a simple case it will use two queries. Let's say that for some reason you want to force a simple includes case to use a single query instead of two. Use references to achieve that.

1User.includes(:posts).references(:posts).to_a
2
3# =>
4SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "posts"."id" AS t1_r0,
5       "posts"."title" AS t1_r1,
6       "posts"."user_id" AS t1_r2, "posts"."desc" AS t1_r3
7FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id"
8
Copy

In the above case a single query was done.

Eager load

eager loading loads all association in a single query using LEFT OUTER JOIN.

1User.eager_load(:posts).to_a
2
3# =>
4SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "posts"."id" AS t1_r0,
5       "posts"."title" AS t1_r1, "posts"."user_id" AS t1_r2, "posts"."desc" AS t1_r3
6FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id"
7
Copy

This is exactly what includes does when it is forced to make a single query when where or order clause is using an attribute from posts table.

Joins

Joins brings association data using inner join.

1User.joins(:posts)
2
3# =>
4SELECT "users".* FROM "users" INNER JOIN "posts" ON "posts"."user_id" = "users"."id"
5
Copy

In the above case no posts data is selected. Above query can also produce duplicate result. To see it let's create some sample data.

1def self.setup
2  User.delete_all
3  Post.delete_all
4
5  u = User.create name: 'Neeraj'
6  u.posts.create! title: 'ruby', desc: 'ruby is awesome'
7  u.posts.create! title: 'rails', desc: 'rails is awesome'
8  u.posts.create! title: 'JavaScript', desc: 'JavaScript is awesome'
9
10  u = User.create name: 'Neil'
11  u.posts.create! title: 'JavaScript', desc: 'Javascript is awesome'
12
13  u = User.create name: 'Trisha'
14end
15
Copy

With the above sample data if we execute User.joins(:posts) then this is the result we get

1#<User id: 9, name: "Neeraj">
2#<User id: 9, name: "Neeraj">
3#<User id: 9, name: "Neeraj">
4#<User id: 10, name: "Neil">
5
Copy

We can avoid the duplication by using distinct .

1User.joins(:posts).select('distinct users.*').to_a
2
Copy

Also if we want to make use of attributes from posts table then we need to select them.

1records = User.joins(:posts).select('distinct users.*, posts.title as posts_title').to_a
2records.each do |user|
3  puts user.name
4  puts user.posts_title
5end
6
Copy

Note that using joins means if you use user.posts then another query will be performed.