I have:
class ProjectTotalView < ActiveRecord::Base
set_table_name ‘project_totals_vw’
has_one :principle_investigator,
:class_name => ‘ProjectPiVw’,
:foreign_key => ‘fin_project_id’
end
class ProjectPiVw < ActiveRecord::Base
set_table_name ‘project_pi_vw’
belongs_to :person,
:foreign_key => ‘principal_investigator_id’
end
class Person
set_table_name ‘people_vw’
end
I then try to inner join the projects with the principle investigator
and people _vw to get the principle investigator on each projects’s
name.
#note that conditions is built up from the user interface and is pretty
dynamic
@projects = ProjectTotalView.find(:all,
:joins => ‘inner join project_pi_vw on
(project_totals_vw.fin_project_id = project_pi_vw.fin_project_id)
inner join people_vw on
(project_pi_vw.principal_investigator_id = people_vw.person_id)’,
:include =>
{:principle_investigator => :person},
:conditions =>
conditions)
however the sql that gets produced only performs a select
project_total_vw.* and does not populate / eager load the inner two
relationships.
select * from (select raw_sql_., rownum raw_rnum_ from (SELECT
project_totals_vw. FROM project_totals_vw inner join project_pi_vw on
(project_totals_vw.fin_project_id = project_pi_vw.fin_project_id)
inner join people_vw on (project_pi_vw.principal_investigator_id =
people_vw.person_id) WHERE ((project_id like ‘10%’)) ORDER BY
cost_centre_code DESC, project_totals_vw.project_title DESC) raw_sql_
where rownum <= 100) where raw_rnum_ > 0
How can I get around this, i’d rather not specify a :select everytime if
possible.
On Jun 11, 3:01 am, Alex M. [email protected]
wrote:
(project_totals_vw.fin_project_id = project_pi_vw.fin_project_id)
inner join people_vw on
(project_pi_vw.principal_investigator_id = people_vw.person_id)',
:include =>
{:principle_investigator => :person},
:conditions =>
conditions)
however the sql that gets produced only performs a select
project_total_vw.* and does not populate / eager load the inner two
relationships.
eager loading is no longer based on joins (see
Mixing :include and :conditions - Space Vatican).
It will fall back if it has to (ie if you have conditions or orders on
columns from the :included models.
Fred
Fred, thankyou for your reply.
I came across the article you mentioned as I was trying to solve the
problem. But it doesn’t show how to get around it.
A single inner join as opposed to 100 queries to fetch the association
runs a lot faster.
I’m assuming there has to be a way in rails to say, fetch me these
objects aned eager load this particular relationship without having to
have that relationship in the conditions?
The only other solution that comes to mind is creating a db view of the
joined statement but thats kind of laborious.
On 11 Jun 2008, at 10:08, Alex M. wrote:
Fred, thankyou for your reply.
I came across the article you mentioned as I was trying to solve the
problem. But it doesn’t show how to get around it.
A single inner join as opposed to 100 queries to fetch the association
runs a lot faster.
But that’s not what happens. You get one query per association (so in
your case 1 to get the principle investigators, 1 to load person)
I’m assuming there has to be a way in rails to say, fetch me these
objects aned eager load this particular relationship without having to
have that relationship in the conditions?
Nope.
Fred