:joins or :select associating with a database view... is this possible?


I have a simple view that prints out a table of all my Project
objects. I would like to be able to do a join to a database view (well
actually a query of that view, but the query could be made into
another view).

I get the impression its non obvious how to do this in AR... but
people have suggested to me that I used :select or :joins... Could you
guys give me a hand? I'm not getting anywhere.

I have a model Project which (in the db [postgresql]) looks like this:

                                    Table "public.projects"
    Column | Type |

Robert Hulme wrote:

    @project_pages,@projects = paginate( :projects,
                          :per_page => 100,
                          :include => :client,
                          :select => "*, (chargeable_total FROM
(select project_id, SUM(chargeable_value) as chargeable_total from
project_monthly_totals GROUP BY project_id) as p_totals)",
                          :order => "job_number::float",
                          :conditions => ["projects.title ilike ? OR
projects.job_number ilike ? OR clients.name iLIKE ?",

which makes AR generate:
SELECT projects."id" AS t0_r0, projects."title" AS t0_r1,
projects."job_number" AS t0_r2, projects."budget" AS t0_r3,
projects."client_id" AS t0_r4, projects."user_id" AS t0_r5,
projects."is_chargeable" AS t0_r6, projects."active" AS t0_r7,
clients."id" AS t1_r0, clients."name" AS t1_r1, clients."address" AS
t1_r2 FROM projects LEFT OUTER JOIN clients ON clients.id =
projects.client_id WHERE (projects.title ilike '%foo%' OR
projects.job_number ilike '%foo%' OR clients.name iLIKE '%foo%') ORDER
BY job_number::float LIMIT 100 OFFSET 0

which also doesn't return the chargeable_total column :S

So when I try to do project.chargeable_total I get an error saying it
is not defined (of course!!).

Could someone please point me in the right direction? :slight_smile:

The way Rails' core code is currently written, you will have to forgo
the eager inclusion of the client if you wish to use a custom select.