sql - 如何使用自定义加入 rails 来加载对象？

Question

背景

正常的 rails 预加载集合是这样工作的:

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

这会生成一些执行的 sql

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

问题

但是，我需要一个自定义连接(如果我使用 find_by_sql 也会出现这种情况)所以我不能使用 vanilla :include => :companies

自定义 join/sql 将为我提供我需要的所有数据，但我如何告诉 activerecord 它属于关联的 Company 对象而不仅仅是一堆额外的行？

更新

我需要在联接中添加其他条件。像这样:

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

可以不使用ActiveRecord添加连接条件吗？

例如，我有一个使用多个相关记录的非常复杂的查询，它通过组合条件和包含指令工作得很好

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

假设:

1. 承包商有_many CouncilAreas
2. CouncilAreas 有_many 郊区

此联接返回由 customer.postcode 标识的郊区的承包商。

生成的查询如下所示:

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

(注意:为简洁起见，我编辑了列列表)。