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

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' 

Answer 2

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).