ruby-on-rails – 当包含预缓存时,Rails忽略关联范围

TL; DR：这个问题在 https://github.com/skensell/SO-question-example有自己的示例应用程序,您可以使用它来调试自己.我在这个问题上已经有了一次赏金,但我不相信(或我不明白)最高回答者的推理.我会再给他一个赏金,因为这让我感到很沮丧.

原题

我有一个模型用户具有如下所示的关联：

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数组.

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

UPDATE

在这里,我可以从rails控制台向您显示一个这个奇怪行为的简单例子.看来,包括…是对罪犯的引用,但我不知道为什么或如何.

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日志.我将不相关的字段更改为“OMITTED”,我用“…”替换了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,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,"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,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,Fri,13 Nov 2015 00:32:53 UTC +00:00],[2749,13 Nov 2015 00:47:02 UTC +00:00],[2751,13 Nov 2015 00:48:05 UTC +00:00]]

另外,我使用的是Rails 4.1.4和Ruby 2.1.10.

更新3

我在这里创建了一个示例应用程序：https://github.com/skensell/SO-question-example.在这个示例应用程序中甚至更陌生的是,to_a甚至不重要.我得到错误的排序,即使只是包括…参考.

解决方法

Includes考虑父表的顺序,只有当它导致连接查询时,才会检索其记录.即在上述情况下,将要跳过化身顺序,并且当包含查询导致连接时将使用用户顺序.您可以向用户添加默认范围并进行确认.

如果您仍然希望按照定义的头像命令对user.avatars进行排序,则需要将连接替换为连接.请注意,使用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"],"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",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,0],[3,1],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",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,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,:sort_order => 1,04 Oct 2016 07:05:40 UTC +00:00,04 Oct 2016 07:05:40 UTC +00:00
},[2] #<Avatar:0x007ff03ec7e2d8> {
            :id => 2,:sort_order => 2,04 Oct 2016 07:05:38 UTC +00:00,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,2]]
[
  [0] #<Avatar:0x007ff03f9121e8> {
            :id => 4,:user_id => 2,:sort_order => 5,04 Oct 2016 07:05:44 UTC +00:00,04 Oct 2016 07:05:48 UTC +00:00
},[1] #<Avatar:0x007ff03f911fe0> {
            :id => 6,:sort_order => 6,04 Oct 2016 07:05:48 UTC +00:00,[2] #<Avatar:0x007ff03f911dd8> {
            :id => 5,:sort_order => 7,04 Oct 2016 07:05:46 UTC +00:00,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,[1] #<Avatar:0x007ff03c7f0bf0> {
            :id => 3,[2] #<Avatar:0x007ff03c7f09c0> {
            :id => 2,04 Oct 2016 07:05:42 UTC +00:00
}
]
[
[0] #<Avatar:0x007ff03c7f07b8> {
            :id => 4,[1] #<Avatar:0x007ff03c7f0588> {
            :id => 6,[2] #<Avatar:0x007ff03c7f0380> {
            :id => 5,updated_at: "2016-10-04 07:05:48">]

基本上,我们有2个加载功能：预加载和eager_load.当您使用include时,它可以调用preload或eager_load.预加载导致2个查询(查找用户和检索用户的替身)wheres eager_load仅使用1个查询(连接查询).因此,当包含连接查询中的结果(即,结果为eager_load)时,从单个查询中跳过要检索的关联的顺序.

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

导致加入,因为您正在基于本身是“通过”关联的特定行业过滤用户. ‘through’使用连接.另外,请记住,’join’结果是INNER JOIN,而eager_load使用LEFT OUTER JOIN.

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,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,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,"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,updated_at: "2016-10-04 07:05:48">]>

您可以参考http://blog.arkency.com/2013/12/rails4-preloading/了解更多示例.我没有发现为什么连接的类型是不同的.无论如何,我希望这有帮助.我会尝试为稍后版本的rails重现相同的.

ruby-on-rails – 当包含预缓存时,Rails忽略关联范围

解决方法

猜你在找的Ruby相关文章