如何通过rails中的自定义连接来加载对象？

Background

Normal rails eager-loading of collections works like this:

正常的铁路集合急切加载如下：

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

This generates some sql which does

这会生成一些sql

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

但是，我需要一个自定义连接（如果我使用find_by_sql也会出现这种情况）所以我不能使用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?

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

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

4 个解决方案

#1

Can you not add the join conditions using ActiveRecord?

你能不能使用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
CouncilAreas拥有很多郊区

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

此连接返回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).

（注意：为简洁起见，我编辑了列列表）。

#2

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'

#3

I'm not sure it's what you want (I'm not 100% sure I've understood your question and what you want to accomplish) but:

我不确定这是你想要的（我不是100％确定我已经理解了你的问题以及你想要完成的事情）但是：

What about providing both :joins and :includes?

那么提供两个：连接和：包括？

Person.find( :all, :joins => 'LEFT OUTER JOIN companies ON people.company_id = companies.id AND _pass_custom_conditions_here_', :includes => :companies )

Person.find（：all，：joins =>'LEFT OUTER JOIN公司ON people.company_id = companies.id AND _pass_custom_conditions_here_'，：includes =>：companies）

Or AR3 way:

或AR3方式：

Person.includes(:companies).joins('LEFT OUTER JOIN companies ON people.company_id = companies.id AND _pass_custom_conditions_here_')

Person.includes（：companies）.joins（'LEFT OUTER JOIN公司ON people.company_id = companies.id AND _pass_custom_conditions_here_'）

#4

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

还可以查看：find的连接选项。它允许您指定表格的连接方式。链接文字

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.

并且在使用时要小心：include，Rails 2.1中的行为会有所改变，并且当与引用包含表的：conditions选项结合使用时可能会导致一些问题。链接文本和链接文本是Pivotal的两篇文章，提到了这个问题。

#1