Rails 3 refuses to eager load

I'm working in Rails 3.0.7.

I have some many-to-many and some one-to-many associations that fail to eager load.

My associations are:

Person has_many :friends
Person has_many :locations through=> :location_histories
Location belongs_to :location_hour
Location_hour has_many :locations

In my controller I have the following:

@people = Person.includes([[:locations=>:location_hour],:friends]).where("(location_histories.current = true) AND (people.name LIKE ? OR friends.first_name LIKE ? OR friends.last_name LIKE ? OR (friends.first_name LIKE ? AND friends.last_name LIKE ?))").limit(10).all

Then in my view I have:

<% @people.each do |person| %>
  <tr>
    <td><%= link_to person.name, person %></td>
    <td><%= link_to person.friends.collect {|s| [s.full_name]}.join(", "), person.friends.first %></td>
    <td><%= link_to person.locations.current.first.name, person.locations.current.first %></td>
  </tr>
<% end %>

locations.current is a 开发者_如何学Pythonscope defined as:

scope :current, lambda {
  where("location_histories.current = ?", true)
}

This works as expected and first generates 2 database calls: one to get a list of person ids and then a big database call where everything is properly joined. THE PROBLEM is that after that there are n database calls along the lines of:

SELECT 'friends'.* from 'friends' WHERE ('friends'.person_id = 12345)

So for each iteration of the loop in the view. Needless to say this takes a while.

I thought that .all would force eager loading. Anyone have an idea what's going on here?

This spends over 3 seconds on ActiveRecord. Way too long.

I would greatly appreciate any and all suggestions.

Thanks.

OK. Finally Solved.
I needed to call both joins and includes. I've also had to remove the :locations_current association from the join. It was creating some chaos by attempting to

... LEFT OUTER JOIN `locations` ON `location_histories`.current = 1 ...  

Which of course is not a valid association. It seems that the 'current' clause was being carried over into the JOINS.

So now I have the following, which works.

@people = Person.joins([[:locations=>:location_hour],:friends]).includes([[:locations=>:location_hour],:friends]).where("(location_histories.current = true) AND (people.name LIKE ? OR friends.first_name LIKE ? OR friends.last_name LIKE ? OR (friends.first_name LIKE ? AND friends.last_name LIKE ?))")

IN SUMMARY:
1) I needed to use both Joins and Includes for eager loading and proper interpretation of the .while() conditions
2) I needed to keep associations with conditions (i.e. :current_locations) out of the joins clause.

Please correct me if this seems like a glaring mistake to you. It seems to work though. This brings down the Active Record time to just under 1 sec.

Is it common to combine joins and includes?

Thanks!

I've figured out PART of the problem (though there is still some unintended behavior). I had several scopes such as locations.current, defined as indicated above.

I have moved this logic to the association. So in my Person model I now have

has_many :current_locations, :source => :location, :through => :location_histories, :conditions => ["`location_histories`.current = ?", true]

And I call

Person.current_locations.first

instead of

Person.locations.current.first.  

So now the includes do eager load as expected.

The problem is that this messed up the search. For some reason now everything seems to hang when I include a where clause. Things first get dramatically slower with each table that I add to the include and by the time I've included all the necessary tables it just hangs. No errors.

I do know this: when I add symbols to the where clause Rails does an outer join during the query (as explained here), which is what's expected. But why does this cause the whole thing to collapse?

(A minor problem here is that I need string comparisons. In order to get a proper join I call .where as

.where(:table =>{:column=> 'string'})

which is equivalent to

table.column = 'string'

in SQL but I need

table.column LIKE '%string%' 

Oddly, for me, I get the following behavior:

# fails to eager load tags--it only loads one of the tags for me, instead of all of them.
Product.find(:all, :conditions => ["products_tags.tag_id IN (?)", 2], :include => [:tags])

but this succeeds

Product.find(:all, :conditions => ["products_tags.tag_id IN (?)", 2], :include => [:tags], :joins => [:tags])

It's like the query on the inner join table is messing up the eager loading somehow. So your answer may be right. But there may be something odd going on here. (Rails 2.3.8 here).