当点/句点处于条件值时，ActiveRecord 查询会发生变化 [英] ActiveRecord query changing when a dot/period is in condition value

问题描述

查看底部的更新.我已经大大缩小了范围.

我还创建了一个演示此错误的准系统应用程序:https://github.com/coreyward/错误演示

I've also created a barebones app demonstrating this bug: https://github.com/coreyward/bug-demo

而且我还在官方跟踪器中创建了一个错误票:https://rails.lighthouseapp.com/projects/8994/tickets/6611-activerecord-query-changed-when-a-dotperiod-is-in-条件值

And I've also created a bug ticket in the official tracker: https://rails.lighthouseapp.com/projects/8994/tickets/6611-activerecord-query-changing-when-a-dotperiod-is-in-condition-value

如果有人能告诉我如何修补这个或解释这在 Rails 中发生的位置，我将不胜感激.

If someone can either tell me how to monkey-patch this or explain where this is happening in Rails I'd be very grateful.

我有一些奇怪/意外的行为.这会让我相信要么存在错误(确认这是一个错误将是一个完美的答案)，要么我错过了我眼皮底下的东西(或者我不明白).

I'm getting some bizarre/unexpected behavior. That'd lead me to believe either there is a bug (confirmation that this is a bug would be a perfect answer), or I am missing something that is right under my nose (or that I don't understand).

class Gallery < ActiveRecord::Base
  belongs_to :portfolio
  default_scope order(:ordinal)
end

class Portfolio < ActiveRecord::Base
  has_many :galleries
end

# later, in a controller action
scope = Portfolio.includes(:galleries) # eager load galleries
if some_condition
  @portfolio = scope.find_by_domain('domain.com')
else
  @portfolio = scope.find_by_vanity_url('vanity_url')
end

• 我有 Portfolios，其中每个都可以有多个 Galleries.
• gallery 具有 ordinal、vanity_url 和 domain 属性.
• gallery ordinals 被设置为从零开始的整数.我已经通过检查 Gallery.where(:portfolio_id => 1).map &:ordinal 确认这按预期工作，它返回 [0,1,2,3,4,5,6] 符合预期.
• vanity_url 和 domain 都是 t.string, :null =>带有唯一索引的 false 列.
• I have Portfolios which can have multiple Galleries each.
• The galleries have ordinal, vanity_url, and domain attributes.
• The gallery ordinals are set as integers from zero on up. I've confirmed that this works as expected by checking Gallery.where(:portfolio_id => 1).map &:ordinal, which returns [0,1,2,3,4,5,6] as expected.
• Both vanity_url and domain are t.string, :null => false columns with unique indexes.

如果 some_condition 为真并且 find_by_domain 运行，则返回的画廊不遵守默认范围.如果运行 find_by_vanity_url，画廊将根据默认范围进行排序.我查看了生成的查询，它们非常不同.

If some_condition is true and find_by_domain is run, the galleries returned do not respect the default scope. If find_by_vanity_url is run, the galleries are ordered according to the default scope. I looked at the queries being generated, and they are very different.

# find_by_domain SQL: (edited out additional selected columns for brevity)

Portfolio Load (2.5ms)  SELECT DISTINCT `portfolios`.id FROM `portfolios` LEFT OUTER JOIN `galleries` ON `galleries`.`portfolio_id` = `portfolios`.`id` WHERE `portfolios`.`domain` = 'lvh.me' LIMIT 1
Portfolio Load (0.4ms)  SELECT `portfolios`.`id` AS t0_r0, `portfolios`.`vanity_url` AS t0_r2, `portfolios`.`domain` AS t0_r11, `galleries`.`id` AS t1_r0, `galleries`.`portfolio_id` AS t1_r1, `galleries`.`ordinal` AS t1_r6 FROM `portfolios` LEFT OUTER JOIN `galleries` ON `galleries`.`portfolio_id` = `portfolios`.`id` WHERE `portfolios`.`domain` = 'lvh.me' AND `portfolios`.`id` IN (1)

# find_by_vanity_url SQL:

Portfolio Load (0.4ms)  SELECT `portfolios`.* FROM `portfolios` WHERE `portfolios`.`vanity_url` = 'cw' LIMIT 1
Gallery Load (0.3ms)  SELECT `galleries`.* FROM `galleries` WHERE (`galleries`.portfolio_id = 1) ORDER BY ordinal

所以由 find_by_domain 生成的查询没有 ORDER 语句，因此事情没有按预期排序.我的问题是...

So the query generated by find_by_domain doesn't have an ORDER statement, hence things aren't being ordered as desired. My question is...

为什么会这样?是什么促使 Rails 3 对这两列生成不同的查询?

这真的很奇怪.我已经考虑并排除了以下所有情况:

This is really strange. I've considered and ruled out all of the following:

• 列上的索引
• Rails 中的保留/特殊词
• 表之间的列名冲突(即域在两个表上)
• DB 和 Schema 中的字段类型
• 允许为空"设置
• 单独的范围

我得到与 find_by_vanity_url 相同的行为，包括位置、电话和标题；我在使用电子邮件时得到与 find_by_domain 相同的行为.

I get the same behavior as find_by_vanity_url with location, phone, and title; I get the same behavior as find_by_domain with email.

我已将范围缩小到当参数名称中包含句点 (.) 时:

find_by_something('localhost') # works fine
find_by_something('name_routed_to_127_0_0_1') # works fine
find_by_something('my_computer.local') # fails
find_by_something('lvh.me') #fails

我对内部结构不够熟悉，无法根据 WHERE 条件的值说明所形成的查询可能在何处发生变化.

I'm not familiar enough with the internals to say where the query formed might change based on the value of a WHERE condition.

推荐答案

这里的评论中讨论了两种预加载策略的区别

The difference between the two strategies for eager loading are discussed in the comments here

https://github.com/rails/rails/blob/3-0-stable/activerecord/lib/active_record/association_preload.rb

来自文档:

# The second strategy is to use multiple database queries, one for each
# level of association. Since Rails 2.1, this is the default strategy. In
# situations where a table join is necessary (e.g. when the +:conditions+
# option references an association's column), it will fallback to the table
# join strategy.

我相信foo.bar"中的点导致活动记录认为您正在将条件放在原始模型之外的表上，这会提示文档中讨论的第二种策略.

I believe that the dot in "foo.bar" is causing active record to think that you are putting a condition on a table that is outside of the originating model which prompts the second strategy discussed in the documentation.

这两个单独的查询使用 Person 模型运行一个，第二个使用 Item 模型运行.

The two separate queries runs one with the Person model and the second with the Item model.

 Person.includes(:items).where(:name => 'fubar')

Person Load (0.2ms)  SELECT "people".* FROM "people" WHERE "people"."name" = 'fubar'
Item Load (0.4ms)  SELECT "items".* FROM "items" WHERE ("items".person_id = 1) ORDER BY items.ordinal

因为您针对 Item 模型运行第二个查询，所以它继承了您指定 order(:ordinal) 的默认范围.

Because you run the second query against the Item model, it inherits the default scope where you specified order(:ordinal).

第二个查询，它尝试预先加载完整的人模型，不会使用关联的默认范围.

The second query, which it attempts eager loading with the full runs off the person model and will not use the default scope of the association.

 Person.includes(:items).where(:name => 'foo.bar')

Person Load (0.4ms)  SELECT "people"."id" AS t0_r0, "people"."name" AS t0_r1, 
"people"."created_at" AS t0_r2, "people"."updated_at" AS t0_r3, "items"."id" AS t1_r0, 
"items"."person_id" AS t1_r1, "items"."name" AS t1_r2, "items"."ordinal" AS t1_r3, 
"items"."created_at" AS t1_r4, "items"."updated_at" AS t1_r5 FROM "people" LEFT OUTER JOIN 
"items" ON "items"."person_id" = "people"."id" WHERE "people"."name" = 'foo.bar'

这样想有点麻烦，但我可以看到用几种不同的方式来呈现选项列表会怎样，确保你抓住所有选项的方法是扫描完成的点的WHERE"条件并使用第二个策略，他们保持这种方式，因为这两个策略都是功能性的.我实际上会说异常行为出现在第一个查询中，而不是第二个查询中.如果您希望此查询的顺序保持不变，我建议您使用以下方法之一:

It is a little buggy to think that, but I can see how it would be with the several different ways you can present a list of options, the way to be sure that you catch all of them would be to scan the completed "WHERE" conditions for a dot and use the second strategy, and they leave it that way because both strategies are functional. I would actually go as far as saying that the aberrant behavior is in the first query, not the second. If you would like the ordering to persist for this query, I recommend one of the following:

1) 如果您希望关联在调用时具有顺序，那么您可以使用关联指定该顺序.奇怪的是，这在文档中，但我无法让它工作.

1) If you want the association to have an order by when it is called, then you can specify that with the association. Oddly enough, this is in the documentation, but I could not get it to work.

来源:http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html#method-i-has_many

class Person < ActiveRecord::Base
  has_many :items, :order => 'items.ordinal'
end

2) 另一种方法是将订单语句添加到相关查询中.

2) Another method would be to just add the order statement to the query in question.

Person.includes(:items).where(:name => 'foo.bar').order('items.ordinal')

3) 沿着同样的思路设置一个命名范围

3) Along the same lines would be setting up a named scope

class Person < ActiveRecord::Base
  has_many :items
  named_scope :with_items, includes(:items).order('items.ordinal')
end

并称之为:

Person.with_items.where(:name => 'foo.bar')

这篇关于当点/句点处于条件值时，ActiveRecord 查询会发生变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！