当预缓存时Rails忽略关联范围

Question

TL;DR:这个问题在https://github.com/skensell/SO-question-example有自己的示例应用程序，您可以使用它来调试自己。我已经在这个问题上提出过一次悬赏，但我不相信(或者我不明白)最高回答者的理由。我要再悬赏一下，因为这让我很沮丧。

原始问题

我有一个模型User，它有这样的关联：

has_many :avatars, -> { order([:sort_order => :asc,:created_at => :asc])}

我有一个端点，它执行用户搜索，并设置视图要使用的@users变量。下面是我在调试器中发现的可怕的部分：

@users.first.avatars[0..2].map(&:id)
# => [2546, 2547, 2548]
# This is the correct order.

@users.to_a.first.avatars[0..2].map(&:id)
# => [2548, 2546, 2547]
# Wrong order.

这里发生什么事情？

唯一的区别是to_a。我甚至尝试删除to_a，但是我认为它是被jbuilder隐式调用的，因为我将它设置为一个json数组。

也许我搜索User的方式和它有什么关系？我正在使用几个包含和连接。

更新

在这里，我可以从rails控制台向您展示这种奇怪行为的简单示例。includes..references似乎是罪犯，但我不明白为什么或如何。

User.order(id: :desc)
    .includes(:avatars, :industries)
    .where(industries: {id:  [5]})
    .references(:industries)
    .limit(5).to_a.second.avatars.map(&:id)
# => [2751, 2748, 2749]
# Wrong order.

User.order(id: :desc)
    .includes(:avatars, :industries)
    .where(industries: {id:  [5]})
    .references(:industries)
    .limit(5).second.avatars.map(&:id)
# => [2748, 2749, 2751]
# Correct order.

我可以验证这些查询是否引用同一个用户，并且标记为正确顺序的查询确实是正确的w.r.t sort_order和created_at (关联就是这样指定排序的)。

更新2

附加的是请求的SQL日志。我将无关字段更改为“省略”，并将34个无关用户字段替换为‘.’。

>> User.order(id: :desc).includes(:avatars, :industries).where(industries: {id:  [5]}).references(:industries).limit(5).to_a.second.avatars.map(&:id)
SQL (18.5ms)  SELECT  DISTINCT "users"."id", "users"."id" AS alias_0 FROM "users" LEFT OUTER JOIN "avatars" ON "avatars"."user_id" = "users"."id" LEFT OUTER JOIN "user_professions" ON "user_professions"."user_id" = "users"."id" LEFT OUTER JOIN "industries" ON "industries"."id" = "user_professions"."industry_id" WHERE "industries"."id" IN (5)  ORDER BY "users"."id" DESC LIMIT 5
SQL (8.3ms)  SELECT "users"."id" AS t0_r0, "users"."OMITTED" AS t0_r1, "users"."OMITTED" AS t0_r2, ... AS t0_r36, "avatars"."id" AS t1_r0, "avatars"."user_id" AS t1_r1, "avatars"."avatar" AS t1_r2, "avatars"."created_at" AS t1_r3, "avatars"."updated_at" AS t1_r4, "avatars"."OMITTED" AS t1_r5, "avatars"."OMITTED" AS t1_r6, "avatars"."sort_order" AS t1_r7, "industries"."id" AS t2_r0, "industries"."name" AS t2_r1, "industries"."created_at" AS t2_r2, "industries"."updated_at" AS t2_r3 FROM "users" LEFT OUTER JOIN "avatars" ON "avatars"."user_id" = "users"."id" LEFT OUTER JOIN "user_professions" ON "user_professions"."user_id" = "users"."id" LEFT OUTER JOIN "industries" ON "industries"."id" = "user_professions"."industry_id" WHERE "industries"."id" IN (5) AND "users"."id" IN (1526, 945, 927, 888, 884)  ORDER BY "users"."id" DESC
=> [2751, 2748, 2749]

>> User.order(id: :desc).includes(:avatars, :industries).where(industries: {id:  [5]}).references(:industries).limit(5).second.avatars.map(&:id)
SQL (0.9ms)  SELECT  DISTINCT "users"."id", "users"."id" AS alias_0 FROM "users" LEFT OUTER JOIN "avatars" ON "avatars"."user_id" = "users"."id" LEFT OUTER JOIN "user_professions" ON "user_professions"."user_id" = "users"."id" LEFT OUTER JOIN "industries" ON "industries"."id" = "user_professions"."industry_id" WHERE "industries"."id" IN (5)  ORDER BY "users"."id" DESC LIMIT 1 OFFSET 1
SQL (0.8ms)  SELECT "users"."id" AS t0_r0, "users"."OMITTED" AS t0_r1, "users"."OMITTED" AS t0_r2, ... AS t0_r36, "avatars"."id" AS t1_r0, "avatars"."user_id" AS t1_r1, "avatars"."avatar" AS t1_r2, "avatars"."created_at" AS t1_r3, "avatars"."updated_at" AS t1_r4, "avatars"."OMITTED" AS t1_r5, "avatars"."OMITTED" AS t1_r6, "avatars"."sort_order" AS t1_r7, "industries"."id" AS t2_r0, "industries"."name" AS t2_r1, "industries"."created_at" AS t2_r2, "industries"."updated_at" AS t2_r3 FROM "users" LEFT OUTER JOIN "avatars" ON "avatars"."user_id" = "users"."id" LEFT OUTER JOIN "user_professions" ON "user_professions"."user_id" = "users"."id" LEFT OUTER JOIN "industries" ON "industries"."id" = "user_professions"."industry_id" WHERE "industries"."id" IN (5) AND "users"."id" IN (945)  ORDER BY "users"."id" DESC
=> [2748, 2749, 2751]
>>

在这里，我将附加一个日志，显示用户的化身(id、sort_order和created_at)，这样您就可以看到顺序应该是确定性的。

>> User.find(945).avatars.pluck(:id,:sort_order,:created_at)
User Load (5.3ms)  SELECT  "users".* FROM "users"  WHERE "users"."id" = $1 LIMIT 1  [["id", 945]]
(0.2ms)  SELECT "avatars"."id", "avatars"."sort_order", "avatars"."created_at" FROM "avatars"  WHERE "avatars"."user_id" = $1  ORDER BY "avatars"."sort_order" ASC, "avatars"."created_at" ASC  [["user_id", 945]]
=> [[2748, 0, Fri, 13 Nov 2015 00:32:53 UTC +00:00], [2749, 0, Fri, 13 Nov 2015 00:47:02 UTC +00:00], [2751, 0, Fri, 13 Nov 2015 00:48:05 UTC +00:00]]

另外，我使用的是Rails 4.1.4和Ruby2.1.10。

更新3

我在这里创建了一个示例应用程序：https://github.com/skensell/SO-question-example。在这个示例应用程序中，更奇怪的是，to_a甚至不重要。即使只是使用includes... references，我也得到了错误的订单。

Answer 1

包含考虑父表的顺序，该表的记录只有在导致联接查询时才会被检索。在上述情况下，当包含查询结果在join中时，将跳过avatars订单，并使用用户订单。您可以向用户添加默认范围并进行确认。

如果您仍然希望按照定义的化身顺序对user.avatars进行排序，则需要用联接替换includes。注意，使用join将检索重复的用户记录。

按预期检索数据的工作解决方案是使用连接和包含在一起。

Loading development environment (Rails 4.1.4)
2.2.0 :001 > User.count
(0.1ms)  SELECT COUNT(*) FROM "users"
=> 2 
2.2.0 :002 > User.pluck :id, :name
(0.2ms)  SELECT "users"."id", "users"."name" FROM "users"
=> [[1, "John"], [2, "Jill"]] 
2.2.0 :003 > User.first.industries.pluck :id, :name
User Load (0.2ms)  SELECT  "users".* FROM "users"   ORDER BY "users"."id" ASC LIMIT 1
(0.2ms)  SELECT "industries"."id", "industries"."name" FROM "industries" INNER JOIN "user_industries" ON "industries"."id" = "user_industries"."industry_id" WHERE "user_industries"."user_id" = ?  [["user_id", 1]]
=> [[1, "Art"], [2, "Music"]] 
2.2.0 :004 > User.last.industries.pluck :id, :name
User Load (1.4ms)  SELECT  "users".* FROM "users"   ORDER BY "users"."id" DESC LIMIT 1
(0.2ms)  SELECT "industries"."id", "industries"."name" FROM "industries" INNER JOIN "user_industries" ON "industries"."id" = "user_industries"."industry_id" WHERE "user_industries"."user_id" = ?  [["user_id", 2]]
=> [[1, "Art"]] 
2.2.0 :005 > User.first.avatars.pluck :id, :sort_order
User Load (0.4ms)  SELECT  "users".* FROM "users"   ORDER BY "users"."id" ASC LIMIT 1
(0.3ms)  SELECT "avatars"."id", "avatars"."sort_order" FROM "avatars"  WHERE "avatars"."user_id" = ?  ORDER BY "avatars"."sort_order" ASC, "avatars"."created_at" ASC  [["user_id", 1]]
=> [[1, 0], [3, 1], [2, 2]] 
2.2.0 :006 > User.last.avatars.pluck :id, :sort_order
User Load (4.1ms)  SELECT  "users".* FROM "users"   ORDER BY "users"."id" DESC LIMIT 1
(0.2ms)  SELECT "avatars"."id", "avatars"."sort_order" FROM "avatars"  WHERE "avatars"."user_id" = ?  ORDER BY "avatars"."sort_order" ASC, "avatars"."created_at" ASC  [["user_id", 2]]
=> [[4, 5], [6, 6], [5, 7]] 
2.2.0 :007 > ap User.joins(:avatars, :industries).where(industries: {id: [1]}).references(:industries).count
(0.2ms)  SELECT COUNT(*) FROM "users" INNER JOIN "avatars" ON "avatars"."user_id" = "users"."id" INNER JOIN "user_industries" ON "user_industries"."user_id" = "users"."id" INNER JOIN "industries" ON "industries"."id" = "user_industries"."industry_id" WHERE "industries"."id" IN (1)
6
=> nil 
2.2.0 :008 > ap User.joins(:avatars, :industries).where(industries: {id: [1]}).references(:industries).uniq.count
(0.3ms)  SELECT DISTINCT COUNT(DISTINCT "users"."id") FROM "users" INNER JOIN "avatars" ON "avatars"."user_id" = "users"."id" INNER JOIN "user_industries" ON "user_industries"."user_id" = "users"."id" INNER JOIN "industries" ON "industries"."id" = "user_industries"."industry_id" WHERE "industries"."id" IN (1)
2
=> nil 
2.2.0 :009 > ap User.joins(:industries).where(industries: {id: [1]}).references(:industries).count
(0.3ms)  SELECT COUNT(*) FROM "users" INNER JOIN "user_industries" ON "user_industries"."user_id" = "users"."id" INNER JOIN "industries" ON "industries"."id" = "user_industries"."industry_id" WHERE "industries"."id" IN (1)
2
=> nil 
 2.2.0 :010 > User.joins(:industries).where(industries: {id: [1]}).references(:industries).each{|user| ap user.avatars }
User Load (0.3ms)  SELECT "users".* FROM "users" INNER JOIN "user_industries" ON "user_industries"."user_id" = "users"."id" INNER JOIN "industries" ON "industries"."id" = "user_industries"."industry_id" WHERE "industries"."id" IN (1)
Avatar Load (0.2ms)  SELECT "avatars".* FROM "avatars"  WHERE "avatars"."user_id" = ?  ORDER BY "avatars"."sort_order" ASC, "avatars"."created_at" ASC  [["user_id", 1]]
[
  [0] #<Avatar:0x007ff03f8ab448> {
            :id => 1,
       :user_id => 1,
    :sort_order => 0,
    :created_at => Tue, 04 Oct 2016 07:05:36 UTC +00:00,
    :updated_at => Tue, 04 Oct 2016 07:05:44 UTC +00:00
},
[1] #<Avatar:0x007ff03ec7e4e0> {
            :id => 3,
       :user_id => 1,
    :sort_order => 1,
    :created_at => Tue, 04 Oct 2016 07:05:40 UTC +00:00,
    :updated_at => Tue, 04 Oct 2016 07:05:40 UTC +00:00
},
[2] #<Avatar:0x007ff03ec7e2d8> {
            :id => 2,
       :user_id => 1,
    :sort_order => 2,
    :created_at => Tue, 04 Oct 2016 07:05:38 UTC +00:00,
    :updated_at => Tue, 04 Oct 2016 07:05:42 UTC +00:00
}
]
Avatar Load (0.2ms)  SELECT "avatars".* FROM "avatars"  WHERE "avatars"."user_id" = ?  ORDER BY "avatars"."sort_order" ASC, "avatars"."created_at" ASC  [["user_id", 2]]
[
  [0] #<Avatar:0x007ff03f9121e8> {
            :id => 4,
       :user_id => 2,
    :sort_order => 5,
    :created_at => Tue, 04 Oct 2016 07:05:44 UTC +00:00,
    :updated_at => Tue, 04 Oct 2016 07:05:48 UTC +00:00
},
[1] #<Avatar:0x007ff03f911fe0> {
            :id => 6,
       :user_id => 2,
    :sort_order => 6,
    :created_at => Tue, 04 Oct 2016 07:05:48 UTC +00:00,
    :updated_at => Tue, 04 Oct 2016 07:05:48 UTC +00:00
},
[2] #<Avatar:0x007ff03f911dd8> {
            :id => 5,
       :user_id => 2,
    :sort_order => 7,
    :created_at => Tue, 04 Oct 2016 07:05:46 UTC +00:00,
    :updated_at => Tue, 04 Oct 2016 07:05:48 UTC +00:00
}
]
=> [#<User id: 1, name: "John", created_at: "2016-10-04 07:05:40", updated_at: "2016-10-04 07:05:40">, #<User id: 2, name: "Jill", created_at: "2016-10-04 07:05:48", updated_at: "2016-10-04 07:05:48">] 
2.2.0 :011 > User.joins(:industries).where(industries: {id: [1]}).references(:industries).includes(:avatars).each{|user| ap user.avatars }

User Load (0.3ms)  SELECT "users".* FROM "users" INNER JOIN "user_industries" ON "user_industries"."user_id" = "users"."id" INNER JOIN "industries" ON "industries"."id" = "user_industries"."industry_id" WHERE "industries"."id" IN (1)
Avatar Load (0.2ms)  SELECT "avatars".* FROM "avatars"  WHERE "avatars"."user_id" IN (1, 2)  ORDER BY "avatars"."sort_order" ASC, "avatars"."created_at" ASC
[
  [0] #<Avatar:0x007ff03c7f0df8> {
            :id => 1,
       :user_id => 1,
    :sort_order => 0,
    :created_at => Tue, 04 Oct 2016 07:05:36 UTC +00:00,
    :updated_at => Tue, 04 Oct 2016 07:05:44 UTC +00:00
},
[1] #<Avatar:0x007ff03c7f0bf0> {
            :id => 3,
       :user_id => 1,
    :sort_order => 1,
    :created_at => Tue, 04 Oct 2016 07:05:40 UTC +00:00,
    :updated_at => Tue, 04 Oct 2016 07:05:40 UTC +00:00
},
[2] #<Avatar:0x007ff03c7f09c0> {
            :id => 2,
       :user_id => 1,
    :sort_order => 2,
    :created_at => Tue, 04 Oct 2016 07:05:38 UTC +00:00,
    :updated_at => Tue, 04 Oct 2016 07:05:42 UTC +00:00
}
]
[
[0] #<Avatar:0x007ff03c7f07b8> {
            :id => 4,
       :user_id => 2,
    :sort_order => 5,
    :created_at => Tue, 04 Oct 2016 07:05:44 UTC +00:00,
    :updated_at => Tue, 04 Oct 2016 07:05:48 UTC +00:00
},
[1] #<Avatar:0x007ff03c7f0588> {
            :id => 6,
       :user_id => 2,
    :sort_order => 6,
    :created_at => Tue, 04 Oct 2016 07:05:48 UTC +00:00,
    :updated_at => Tue, 04 Oct 2016 07:05:48 UTC +00:00
},
[2] #<Avatar:0x007ff03c7f0380> {
            :id => 5,
       :user_id => 2,
    :sort_order => 7,
    :created_at => Tue, 04 Oct 2016 07:05:46 UTC +00:00,
    :updated_at => Tue, 04 Oct 2016 07:05:48 UTC +00:00
}
]
=> [#<User id: 1, name: "John", created_at: "2016-10-04 07:05:40", updated_at: "2016-10-04 07:05:40">, #<User id: 2, name: "Jill", created_at: "2016-10-04 07:05:48", updated_at: "2016-10-04 07:05:48">] 

基本上，我们有两个迫切的加载功能:预加载和eager_load。当使用include时，它要么调用预加载，要么调用eager_load。预加载将产生两个查询(查找用户&为检索的用户查找化身)，其中eager_load只使用一个查询(连接查询)。因此，当在联接查询中包含结果(即eager_load中的结果)时，将跳过要检索的关联的顺序，因为它是一个查询。

User.includes(:avatars, :industries).where(industries: {id: [1]}).references(:industries)

结果在加入，因为你是过滤的用户基于特定的行业，这本身就是一个“通过”的联系。“通过”使用连接。另外，请记住，“联接”会导致内部连接，而eager_load则使用左外部联接。

2.2.0 :050 > User.joins(:industries).where(industries: {id: [1]}).references(:industries)
User Load (0.2ms)  SELECT "users".* FROM "users" INNER JOIN "user_industries" ON "user_industries"."user_id" = "users"."id" INNER JOIN "industries" ON "industries"."id" = "user_industries"."industry_id" WHERE "industries"."id" IN (1)
=> #<ActiveRecord::Relation [#<User id: 1, name: "John", created_at: "2016-10-04 07:05:40", updated_at: "2016-10-04 07:05:40">, #<User id: 2, name: "Jill", created_at: "2016-10-04 07:05:48", updated_at: "2016-10-04 

2.2.0 :054 >   User.includes(:industries).where(industries: {id: [1]}).references(:industries)
SQL (0.3ms)  SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "users"."created_at" AS t0_r2, "users"."updated_at" AS t0_r3, "industries"."id" AS t1_r0, "industries"."name" AS t1_r1, "industries"."created_at" AS t1_r2, "industries"."updated_at" AS t1_r3 FROM "users" LEFT OUTER JOIN "user_industries" ON "user_industries"."user_id" = "users"."id" LEFT OUTER JOIN "industries" ON "industries"."id" = "user_industries"."industry_id" WHERE "industries"."id" IN (1)
=> #<ActiveRecord::Relation [#<User id: 1, name: "John", created_at: "2016-10-04 07:05:40", updated_at: "2016-10-04 07:05:40">, #<User id: 2, name: "Jill", created_at: "2016-10-04 07:05:48", updated_at: "2016-10-04 07:05:48">]> 

2.2.0 :057 >   User.eager_load(:industries).where(industries: {id: [1]}).references(:industries)
SQL (0.3ms)  SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "users"."created_at" AS t0_r2, "users"."updated_at" AS t0_r3, "industries"."id" AS t1_r0, "industries"."name" AS t1_r1, "industries"."created_at" AS t1_r2, "industries"."updated_at" AS t1_r3 FROM "users" LEFT OUTER JOIN "user_industries" ON "user_industries"."user_id" = "users"."id" LEFT OUTER JOIN "industries" ON "industries"."id" = "user_industries"."industry_id" WHERE "industries"."id" IN (1)

=> #<ActiveRecord::Relation [#<User id: 1, name: "John", created_at: "2016-10-04 07:05:40", updated_at: "2016-10-04 07:05:40">, #<User id: 2, name: "Jill", created_at: "2016-10-04 07:05:48", updated_at: "2016-10-04 07:05:48">]>   

您可以参考http://blog.arkency.com/2013/12/rails4-preloading/获得更多的示例并进行解释。我没有发现为什么联接的类型是不同的。不管怎样，我希望这能帮上忙。我将尝试为稍后版本的rails复制相同的内容。

Answer 2

@users.first.avatars[0..2].map(&:id)
# => [2546, 2547, 2548]

@users.to_a.first.avatars[0..2].map(&:id)
# => [2548, 2546, 2547]

• 这里发生什么事情？

没什么不对的。

根据#第一

第一种方法查找按主键排序的第一条记录(默认)

SELECT * FROM clients ORDER BY clients.id ASC LIMIT 1

以及返回为集合@users.to_a order的下一个方法( updated_at )

希望这能帮到你！

Answer 3

因为那些东西不一样

# returns a user which has smallest id (@users are ordered by id)
@users.first # User::ActiveRecord_Relation#first

# is different to
# returns a first user which fetched from database (@users are unordered)
@users.to_a.first # Array#first