ruby-on-rails - 比较包含与 eager_load 的 .references 要求

Question

我知道当您使用 includes 并在联接表上指定 where 子句时，您应该使用 .references

例子:

# will error out or throw deprecation warning in logs
users = User.includes(:orders).where("Orders.cost < ?", 20)

在 rails 4 或更高版本中，您将收到如下错误:

Mysql2::Error: Unknown column 'Orders.cost' in 'where clause': SELECT customers.* FROM customers WHERE (Orders.cost < 100)

否则您将收到弃用警告:

DEPRECATION WARNING: It looks like you are eager loading table(s) (one of: users, addresses) that are referenced in a string SQL snippet. For example:

Post.includes(:comments).where("comments.title = 'foo'") Currently, Active Record recognizes the table in the string, and knows to JOIN the comments table to the query, rather than loading comments in a separate query. However, doing this without writing a full-blown SQL parser is inherently flawed. Since we don't want to write an SQL parser, we are removing this functionality. From now on, you must explicitly tell Active Record when you are referencing a table from a string:

Post.includes(:comments).where("comments.title = 'foo'").references(:comments)

If you don't rely on implicit join references you can disable the feature entirely by setting config.active_record.disable_implicit_join_references = true. (

SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "users"."email" AS t0_r2, "users"."created_at" AS t0_r3, "users"."updated_at" AS t0_r4, "addresses"."id" AS t1_r0, "addresses"."user_id" AS t1_r1, "addresses"."country" AS t1_r2, "addresses"."street" AS t1_r3, "addresses"."postal_code" AS t1_r4, "addresses"."city" AS t1_r5, "addresses"."created_at" AS t1_r6, "addresses"."updated_at" AS t1_r7 FROM "users" LEFT OUTER JOIN "addresses" ON "addresses"."user_id" = "users"."id" WHERE (addresses.country = 'Poland')

所以我们这样做:

# added .references(:orders)
users = User.includes(:orders).where("Orders.cost < ?", 20).references(:orders)

它执行得很好:

SELECT "users"."id"     AS t0_r0, 
  "users"."name"        AS t0_r1, 
  "users"."created_at"  AS t0_r2, 
  "users"."updated_at"  AS t0_r3, 
  "orders"."id"         AS t1_r0, 
  "orders"."cost"       AS t1_r1, 
  "orders"."user_id"    AS t1_r2, 
  "orders"."created_at" AS t1_r3, 
  "orders"."updated_at" AS t1_r4 
FROM "users" 
LEFT OUTER JOIN "orders" 
ON "orders"."user_id" = "users"."id" 
WHERE  ( orders.cost < 20 ) 

我知道 .includes 只是两个方法的包装:eager_load 和 preload。我知道，由于我上面的查询是在连接表(本例中为 orders)上进行筛选，includes 很聪明并且知道选择 eager_load 在 preload 上实现，因为 preload 无法处理执行此查询，因为 preload 不连接表。

这就是我感到困惑的地方。好的:所以在上面的查询中:在引擎盖下 includes 将利用 eager_load 实现。但是请注意，当我对同一个查询显式使用 eager_load 时(这是 includes 本质上所做的):我不需要使用 .references!它运行查询并加载数据就好了。没有错误也没有弃用警告:

# did not specify .references(:orders), and yet no error and no deprecation warning 
users = User.eager_load(:orders).where("Orders.cost < ?", 20)

并且它毫无问题地执行了完全相同的过程:

SELECT "users"."id"     AS t0_r0, 
  "users"."name"        AS t0_r1, 
  "users"."created_at"  AS t0_r2, 
  "users"."updated_at"  AS t0_r3, 
  "orders"."id"         AS t1_r0, 
  "orders"."cost"       AS t1_r1, 
  "orders"."user_id"    AS t1_r2, 
  "orders"."created_at" AS t1_r3, 
  "orders"."updated_at" AS t1_r4 
FROM "users" 
LEFT OUTER JOIN "orders" 
ON "orders"."user_id" = "users"."id" 
WHERE  ( orders.cost < 20 ) 

这看起来很奇怪。为什么 .references 需要为查询的 includes 版本指定，而 .references 不需要为 指定>eager_load 版本的查询？我在这里缺少什么？

Answer 1

归结为他们在弃用警告中提到的问题:

Currently, Active Record recognizes the table in the string, and knows to JOIN the comments table to the query, rather than loading comments in a separate query. However, doing this without writing a full-blown SQL parser is inherently flawed. Since we don't want to write an SQL parser, we are removing this functionality.

在旧版本中，Rails 试图帮助选择要使用的查询模式，includes 会尽可能使用 preload 策略，但切换到 eager_load 策略，当它看起来像是在引用连接表中的某些内容时。但是如果没有一个完整的 SQL 解析器来确定实际引用了哪些表，它就像 parsing XHTML with a Regex - 你可以完成一些事情，但 Rails 不能在所有情况下都做出正确的决定。考虑:

User.includes(:orders).where("Orders.cost < 20")

这是一个不错的简单示例，Rails 可以判断您需要加入 Orders。现在试试这个:

User.includes(:orders).where("id IN (select user_id from Orders where Orders.cost < 20)")

这给出了相同的结果，但是子查询呈现了加入 Orders 的不必要。这是一个人为的例子，我不知道 Rails 是否会决定是否需要加入第二个查询，但重点是有些情况下启发式可能会做出错误的决定。在这些情况下，Rails 要么执行不必要的连接，消耗内存并减慢查询速度，要么不执行必要的连接，从而导致错误。

开发人员决定只询问程序员是否需要连接，而不是用一个非常糟糕的失败案例来保持启发式。与 Rails 相比(希望如此)，您可以更频繁地做对，并且当您做错时，很清楚要更改什么。

您可以切换到 eager_load 而不是添加 references，但保持 includes 和 references 分开允许实现查询模式的灵 active 。您可以想象 .includes(:orders, :addresses).references(:orders) 并在第二个 preload 样式查询中加载 addresses因为在连接期间不需要它(尽管 Rails 实际上只是在连接中包含 addresses )。使用 eager_load 时不需要指定 references，因为 eager_load 总是 加入，其中 preload 总是 执行多个查询。 references 所做的只是指示 includes 使用必要的 eager_load 策略并指定需要哪些表。