How to eager load objects with a custom join in rails?

Question

Background

Normal rails eager-loading of collections works like this:

Person.find(:all, :include=>:companies)

This generates some sql which does

LEFT OUTER JOIN companies ON people.company_id = companies.id

Question

However, I need a custom join (this could also arise if I was using find_by_sql) so I can't use the vanilla :include => :companies

The custom join/sql will get me all the data I need, but how can I tell activerecord that it belongs to the associated Company objects rather than just being a pile of extra rows?

Update

I need to put additional conditions in the join. Something like this:

SELECT blah blah blah
LEFT OUTER JOIN companies ON people.company_id = companies.id AND people.magical_flag IS NULL 
<Several other joins>
WHERE blahblahblah

Answer 1

Can you not add the join conditions using ActiveRecord?

For example, I have a quite complex query using several dependent records and it works fine by combining conditions and include directives

Contractors.find(
  :all, 
  :include => {:council_areas => :suburbs},
  :conditions => ["suburbs.postcode = ?", customer.postcode]                 
)

Assuming that:

1. Contractors have_many CouncilAreas
2. CouncilAreas have_many Suburbs

This join returns the Contractors in the suburb identified by customer.postcode.

The generated query looks like:

SELECT contractors.*, council_areas.*, suburbs.*
FROM `contractors` 
LEFT OUTER JOIN `contractors_council_areas` ON `contractors_council_areas`.contractor_id = `contractors`.id 
LEFT OUTER JOIN `council_areas` ON `council_areas`.id = `contractors_council_areas`.council_area_id 
LEFT OUTER JOIN `council_areas_suburbs` ON `council_areas_suburbs`.council_area_id = `council_areas`.id 
LEFT OUTER JOIN `suburbs` ON `suburbs`.id = `council_areas_suburbs`.suburb_id WHERE (suburbs.postcode = '5000')

(Note: I edited the column list for brevity).

Answer 2

Can you elaborate a bit more on exactly what you are trying to accomplish with this query?

Also take a look at at the :joins option for find. It allows you to specify how you want the tables joined. link text

And beware when using :include, the behavior changes a bit in Rails 2.1 and may cause some problems when used in conjunction with a :conditions option that references an included table. link text and link text are two articles from Pivotal that mention this gotcha.

Answer 3

I'm having the same problem as the poster - I am using the :joins param, which does the join correctly, but ActiveRecord doesn't create the objects fetched by those joins - it just returns a pile of rows. This means when I reference them in my view it loads them again, unnecessarily.

Answer 4

You can use something like the following to get the appropriate left outer join syntactical magic.

Person.reflect_on_association(:companies).options[:conditions] = 'people.magical_flag IS NULL'