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.
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
On Jun 11, 3:01 am, Alex Moore <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> I have: > 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.eager loading is no longer based on joins (see http://www.spacevatican.org/2008/4/29/include-and-conditions). It will fall back if it has to (ie if you have conditions or orders on columns from the :included models. Fred --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 11 Jun 2008, at 10:08, Alex Moore 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---