rails 中 preload、includes、Eager load、Joins 的区别

Rails 提供了四种不同加载关联数据的方法。下面就来介绍一下。

一、Preload

Preload 是以附加一条查询语句来加载关联数据的

User.preload(:posts).to_a

# =>
SELECT "users".* FROM "users"
SELECT "posts".* FROM "posts"  WHERE "posts"."user_id" IN (1)

这种方式与是 includes 默认加载数据的方式

由于 preload 总是生成两条 sql，所以不能在后面使用 where 条件，下面的查询会报错

User.preload(:posts).where("posts.desc='ruby is awesome'")

# =>
SQLite3::SQLException: no such column: posts.desc:
SELECT "users".* FROM "users"  WHERE (posts.desc='ruby is awesome')

在 preload 的 where 条件只能这样使用

User.preload(:posts).where("users.name='Neeraj'")

# =>
SELECT "users".* FROM "users"  WHERE (users.name='Neeraj')
SELECT "posts".* FROM "posts"  WHERE "posts"."user_id" IN (3)

二、Includes

默认情况下 Includes 加载关联数据和 preload 一样

但它比 preload 要聪明一些。上面看到关于 preload 的查询 User.preload(:posts).where("posts.desc='ruby is awesome'") 会失败。下面看看 includes：

User.includes(:posts).where('posts.desc = "ruby is awesome"').to_a

# =>
SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "posts"."id" AS t1_r0,
       "posts"."title" AS t1_r1,
       "posts"."user_id" AS t1_r2, "posts"."desc" AS t1_r3
FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id"
WHERE (posts.desc = "ruby is awesome")

这里你可以看到 includes 把两条分开的 SQL 用 LEFT OUTER JOIN 生成一条数据，并且它还包括了 where 条件

所以在一些情况下 includes 把两条语句合成一条。但最简单情况下它是使用两条的。但如果你就想要 includes 使用一条 sql 语句, references 可以做到：

User.includes(:posts).references(:posts).to_a

# =>
SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "posts"."id" AS t1_r0,
       "posts"."title" AS t1_r1,
       "posts"."user_id" AS t1_r2, "posts"."desc" AS t1_r3
FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id"

上面只会生成一条 sql

三、Eager load

eager loading 是以 LEFT OUTER JOIN 加载所有相关数据的。

User.eager_load(:posts).to_a

# =>
SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "posts"."id" AS t1_r0,
       "posts"."title" AS t1_r1, "posts"."user_id" AS t1_r2, "posts"."desc" AS t1_r3
FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id"

它正是 includes 使用 where 或 order 从 posts表中获取数据时强制生成一条 sql 的情况。


四、Joins

Joins 方式是使用 inner join 加载关联数据

User.joins(:posts)

# =>
SELECT "users".* FROM "users" INNER JOIN "posts" ON "posts"."user_id" = "users"."id"

上面的情况下 posts 数据不会被查询出来，它会得到重复数据。举个例子：

def self.setup
  User.delete_all
  Post.delete_all

  u = User.create name: 'Neeraj'
  u.posts.create! title: 'ruby', desc: 'ruby is awesome'
  u.posts.create! title: 'rails', desc: 'rails is awesome'
  u.posts.create! title: 'JavaScript', desc: 'JavaScript is awesome'

  u = User.create name: 'Neil'
  u.posts.create! title: 'JavaScript', desc: 'Javascript is awesome'

  u = User.create name: 'Trisha'
end

上面的测试数据运行之后会得到：

#<User id: 9, name: "Neeraj">
#<User id: 9, name: "Neeraj">
#<User id: 9, name: "Neeraj">
#<User id: 10, name: "Neil">

我们可以使用 distinct 来去重：

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

如果想要 posts 表的数据也可以 select 出来

records = User.joins(:posts).select('distinct users.*, posts.title as posts_title').to_a
records.each do |user|
  puts user.name
  puts user.posts_title
end

注意：在应用 joins 查询数据的时候使用 user.posts 会生成另外一个 sql 语句

原文地址：

http://blog.bigbinary.com/2013/07/01/preload-vs-eager-load-vs-joins-vs-includes.html