Rails 2.1 - Inner Join, only selecting from *

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.

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

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.

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