association followed by named scope yields duplicate SQL query

Question

I came across this strange issue at work so I created a minimal app to highlight the problem. I have two models with a simple association:

class Parent < ActiveRecord::Base
  has_many :children
end

and

class Child < ActiveRecord::Base
  belongs_to :parent
  named_scope :only_adults, :conditions => "adult is true"
end

Now when I do

 p = Parent.first
 p.children.only_adults.all()

I would expect rails to generate a single SQL query containing all the conditions. However, here's what I see in the log:

Child Load (0.5ms)   SELECT * FROM "children" WHERE ("children".parent_id = 1) 
Child Load (0.3ms)   SELECT * FROM "children" WHERE ("children".parent_id = 1) AND ((adult is true) AND ("children".parent_id = 1)) 

The first query is basically useless and can be very time-comsuming in the case of large collections.

Does anyone have an idea why rails is behaving as such?

Note that instead of doing

p.children.only_adults.all()

I do

Child.by_parent(p.id).only_adults.all()

where by_parent is a named scope, then I get only one query.

Also note the duplication of the parent_id condition. This is not a big deal.

Thank you for your feedback.

Answer 1

The reason has to do with the way queries are executed in Rails 2.3.x, as opposed to Rails 3.0.x. In Rails 2, calling p.children will automatically execute the query, regardless of the named scopes you have attached to the rest of the query. The only way to get around this is to use named_scopes in combination, like you do in "Child.by_parent(p.id).only_adults.all()" since the named scopes keep this behavior of postponing the query. In Rails 3 the query is built up until an execute keyword is found (count, all, first, last), so it's possible to do the following in a single query:

class Child < ActiveRecord::Base
  belongs_to :parent
  scope :only_adults, where(adult: true)
end

Parent.first.children.only_adults.all()

# SELECT * FROM "children" WHERE ("children".parent_id = 1) AND (adult is true)