Rails 2.1 - Inner Join, only selecting from *

rubyonrails-talk
1

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.

2

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

3

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.

4

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