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:
@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 ?",
@phrase,@phrase,@phrase]
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?
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.