Request ActiveRecord request when point / period is in condition value

Question

See updates below. I significantly narrowed it down.

I also created a barebones application demonstrating this error: https://github.com/coreyward/bug-demo

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

If someone can tell me how to defuse this or explain where it happens in Rails, I would really appreciate it.

---

I get some strange / unexpected behavior. This would make me believe that there is a mistake (confirmation that this is a mistake will be the perfect answer), or I miss something that is right under my nose (or I don’t understand this).

The code

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 

• I have Portfolios that can have several Galleries each.
• Galleries have the attributes ordinal , vanity_url and domain .
• gallery ordinals are set as integers from zero to. I 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.

Problem

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

Inquiries

 # 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 

Thus, the query generated by find_by_domain does not have an ORDER operator, so things are not ordered as desired. My question is ...

Why is this happening? What makes Rails 3 generate different queries for these two columns?

---

Update

This is really weird. I reviewed and ruled out all of the following:

• Column Indices
• Reserved / special words in Rails
• Column of column names between tables (i.e. domain located on both tables)
• Field type, both in the database and in the schema
• Value allow null
• Separate area

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

---

Another update

I narrowed it down to when the parameter has a period (.) In the name:

 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 

I am not familiar enough with the internal ones to say where the generated request may change depending on the value of the WHERE .

Answer 1

The difference between the two strategies for intensive loading is discussed here in the comments.

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

From the documentation:

 # 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 (eg when the +:conditions+ # option references an association column), it will fallback to the table # join strategy. 

I believe that the dot in "foo.bar" makes the active record think that you are placing a condition in a table that is outside the original model, which requests the second strategy discussed in the documentation.

Two separate requests start 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 

Since you run the second query for the Item model, it inherits the default scope in which you specified order(:ordinal) .

The second request, which he tries to load with the full launch of the user model and will not use the default communication area.

  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' 

Think about it a bit, but I see how it would be with several different ways that you can present a list of options to make sure that you catch them all, would scan the completed “WHERE” conditions for the point and use the second strategy, and they leave her that way because both strategies are functional. In fact, I would say that aberrant behavior is in the first request, and not in the second. If you want the order to be saved for this request, I recommend one of the following:

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

Source: 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) Another method would be to simply add an order operator to the request in question.

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

3) A named area will be created along the same lines

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

And to call it:

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

Answer 2

This is issue # 950 in the Rails GitHub project . It seems that the implicit load (which causes this error) is deprecated in Rails 3.2 and removed in Rails 4.0. Instead, you explicitly tell Rails that you need a JOIN for the WHERE clause - for example:

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

If you desperately need this bug fixed in Rails 3.1. *, you can hack ActiveRecord::Relation#tables_in_string less aggressively in matching table names. I created the essence of my (inelegant and slow) decision . This is diff:

 diff --git a/activerecord/lib/active_record/relation.rb b/activerecord/lib/active_record/relation.rb index 30f1824..d7335f3 100644 --- a/activerecord/lib/active_record/relation.rb +++ b/activerecord/lib/active_record/relation.rb @@ -528,7 +528,13 @@ module ActiveRecord return [] if string.blank? # always convert table names to downcase as in Oracle quoted table names are in uppercase # ignore raw_sql_ that is used by Oracle adapter as alias for limit/offset subqueries - string.scan(/([a-zA-Z_][.\w]+).?\./).flatten.map{ |s| s.downcase }.uniq - ['raw_sql_'] + candidates = string.scan(/([a-zA-Z_][.\w]+).?\./).flatten.map{ |s| s.downcase }.uniq - ['raw_sql_'] + candidates.reject do |t| + s = string.partition(t).first + s.chop! if s.last =~ /['"]/ + s.reverse! + s =~ /^\s*=/ + end end end end 

It only works in my specific case (Postgres and the equality condition), but maybe you can change it to work for you.