CODEWITHSUNDEEP
sqlruby-on-railsrubyactiverecord
Orion Edwards
Orion Edwards

Reputation: 123612

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

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

Upvotes: 33

Views: 7247

Answers (4)

Tomek Wałkuski
Tomek Wałkuski

Reputation: 1009

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:

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 )

Or AR3 way:

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

Upvotes: 2

anon
anon

Reputation:

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'

Upvotes: 3

user19898
user19898

Reputation: 21

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

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.

Upvotes: 1

Toby Hede
Toby Hede

Reputation: 37123

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

Upvotes: 7

Related Questions