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

activerecord, ruby, ruby-on-rails, sql

Solution

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:

- Contractors have_many CouncilAreas

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

Problem

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 ```

Original source