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