比较include和eager_load的.references要求 [英] Comparing .references requirement on includes vs. eager_load

问题描述

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

I know that when you utilize includes and you specify a where clause on the joined table, you should use .references

示例：

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

在rails 4或更高版本中，您将收到类似以下的错误：

In rails 4 or later, you will get an error like the following:

Mysql2 :: Error： where子句中未知的 Orders.cost列：SELECT
位客户。*来自客户所在位置（Orders.cost< 100）

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

否则您将收到弃用警告：

Or you will get a deprecation warning:

DEPRECATION警告：您似乎渴望加载在字符串SQL代码段中引用的表（一个
：用户，地址）。以
为例：

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'）当前，
有效记录识别字符串中的表，并知道将
条评论表加入查询中，而不是在
条单独的查询中加载评论。但是，在不编写成熟的SQL
解析器的情况下执行此操作本质上是有缺陷的。由于我们不想编写SQL
解析器，因此我们将删除此功能。从现在开始，当您从
字符串引用表时，必须
明确告诉Active Record：

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）

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

如果您不依赖隐式联接引用，则可以通过设置
config.active_record.disable_implicit_join_references = true完全禁用
功能。 （

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. (

选择 users。 id AS t0_r0， users。 name AS t0_r1， users。电子邮件
AS t0_r2 ， users。 created_at AS t0_r3，用户。 updated_at AS
t0_r4，地址。 id AS t1_r0，地址。 user_id AS t1_r1，
地址。国家 AS t1_r2，地址。街道 AS t1_r3，
地址。邮政编码 AS t1_r4，地址。城市 AS t1_r5，
地址 。 created_at AS t1_r6，地址。 updated_at AS t1_r7
从 users左外加入 addresses ON addresses。 user_id =
users。 id在哪里（addresses.country ='波兰'）

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 不联接表。

I know that .includes is just a wrapper for two methods: eager_load and preload. I know that since my query above is doing a filter on a joined table (orders in this example), includes is smart and knows to pick the eager_load implementation over preload because preload cannot handle doing this query since preload does not join tables.

在这里我很困惑。好的：因此，在上面的查询中： includes 将利用 eager_load 实现。但是请注意，当我针对同一查询明确使用 eager_load 时（这实际上是 includes 所做的事情）：不需要使用 .references ！它运行查询并加载数据就好了。没有错误也没有弃用警告：

Here is where I am confused. Ok: So on that query above: under the hood includes will utilize the eager_load implementation. But notice how when I explicitly use eager_load for this same query (which is what includes is essentially doing): I do not need to use .references! It runs the query and loads the data just fine. No error and no deprecation warning:

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

它执行相同的过程没有问题：

And it executes the exact same process with no problem:

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 版本指定 .references ，而。在查询的 eager_load 版本中是否不需要指定引用？我在这里缺少什么？

That seems odd. Why does .references need to be specified for the includes version of the query, whereas .references does not need to be specified for the eager_load version of the query? What am I missing here?

推荐答案

这归因于他们在弃用警告中提到的问题：

It comes down to the problem they mention in the deprecation warning:

当前，Active Record识别字符串中的表，并知道将注释表加入查询中，而不是在单独的查询中加载注释。但是，在不编写成熟的SQL解析器的情况下执行此操作本质上是有缺陷的。由于我们不想编写SQL解析器，因此我们删除了此功能。

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解析器来确定实际引用了哪些表，就像使用Regex解析XHTML 一样-您可以获得一些事情已经完成，但Rails无法在每种情况下都正确地做出决定。考虑：

In older versions, Rails tried to be helpful about selecting the query pattern to use, and includes would use the preload strategy if it could, but switch to the eager_load strategy when it looks like you're referencing something in a joined table. But without a full SQL parser figuring out what tables are actually referenced, it's like parsing XHTML with a Regex - you can get some things done, but Rails can't decide correctly in every case. Consider:

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

这是一个很好的简单示例，Rails会告诉您需要 Orders 加入。现在尝试以下操作：

This is a nice, simple example, and Rails could tell that you need Orders joined. Now try this one:

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

这给出相同的结果，但是子查询呈现为联接订单是不必要的。这是一个人为的例子，我不知道Rails是否会决定需要加入的第二个查询，但关键是在某些情况下启发式方法可能会做出错误的决定。在这些情况下，Rails会执行不必​​要的联接，消耗内存并降低查询速度，或者不执行必要的联接而导致错误。

This gives the same result, but the subquery rendered joining Orders unnecessary. It's a contrived example, and I don't know whether Rails would decide the second query needed to join or not, but the point is there are cases when the heuristic could make the wrong decision. In those cases, either Rails would perform an unnecessary join, burning memory and slowing the query down, or not perform a necessary join, causing an error.

而不是维护启发式的失败案例非常糟糕，开发人员决定只询问程序员是否需要联接。您可以比Rails更经常地正确使用它（希望如此），并且当您发现错误时，很清楚可以更改的内容。

Rather than maintain a heuristic with a pretty bad failure case, the developers decided to just ask the programmer whether the join is needed. You're able to get it right more often than Rails can (hopefully), and when you get it wrong, it's clear what to change.

而不是添加引用，您可以切换到 eager_load ，但保留 includes 和引用单独允许其查询模式具有实现灵活性。您可以想象 .includes（：orders，：addresses）.references（：orders）并将 addresss 加载到第二个 preload 样式的查询，因为在连接过程中不需要它（尽管Rails实际上只是在连接中包括地址） 。使用 eager_load 时，无需指定引用，因为 eager_load 总是联接，其中 preload 总是进行多个查询。所有引用都被指示 includes 使用必要的 eager_load 策略并指定需要哪些表。

Instead of adding references you could switch to eager_load, but keeping includes and references separate allows the implementation flexibility in its query pattern. You could conceivably .includes(:orders, :addresses).references(:orders) and have addresses loaded in a second preload-style query because it's not needed during the join (though Rails actually just includes addresses in the join anyway). You don't need to specify references when you're using eager_load because eager_load always joins, where preload always does multiple queries. All references does is instruct includes to use the necessary eager_load strategy and specify which tables are needed.

这篇关于比较include和eager_load的.references要求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！