:joins or :select associating with a database view... is thi

Hi,

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          |

Modifiers
---------------±-----------------------±------------------------------------------------------
id | integer | not null default
nextval(‘projects_id_seq’::regclass)
title | character varying(255) | not null
job_number | character varying(10) | not null
budget | numeric(15,2) |
client_id | integer | not null
user_id | integer |
is_chargeable | boolean |
active | boolean | not null default true
Indexes:
“projects_pkey” PRIMARY KEY, btree (id)
“projects_job_number_key” UNIQUE, btree (job_number)
Foreign-key constraints:
“$1” FOREIGN KEY (client_id) REFERENCES clients(id)
“$2” FOREIGN KEY (user_id) REFERENCES users(id)

and a timesheet_entries whose table looks like this:

                                   Table "public.timesheetentries"
Column     |            Type             |

Modifiers
---------------±----------------------------±--------------------------------------------------------------
id | integer | not null default
nextval(‘timesheetentries_id_seq’::regclass)
start_time | timestamp without time zone | not null
end_time | timestamp without time zone | not null
project_id | integer | not null
user_id | integer | not null
notes | text | not null default ‘’::text
work_type | character varying(100) |
is_chargeable | boolean |
Indexes:
“timesheetentries_pkey” PRIMARY KEY, btree (id)
Check constraints:
“$1” CHECK (end_time >= start_time)
Foreign-key constraints:
“$2” FOREIGN KEY (project_id) REFERENCES projects(id)

One of the views I have is the project_monthly_totals view which looks
like this:

    View "public.project_monthly_totals"
    Column        |       Type       | Modifiers

----------------------±-----------------±----------
project_id | integer |
month | date |
chargeable_value | double precision |
non_chargeable_value | double precision |
View definition:
SELECT project_work_type_monthly_totals.project_id,
project_work_type_monthly_totals.“month”,
sum(project_work_type_monthly_totals.chargeable_value) AS
chargeable_value,
sum(project_work_type_monthly_totals.non_chargeable_value) AS
non_chargeable_value
FROM project_work_type_monthly_totals
GROUP BY project_work_type_monthly_totals.project_id,
project_work_type_monthly_totals.“month”
ORDER BY project_work_type_monthly_totals.project_id,
project_work_type_monthly_totals.“month”;

The actual information I want is derived from “INNER JOIN (select
project_id, SUM(chargeable_value) as chargeable_total from
project_monthly_totals GROUP BY project_id) as p_totals ON
p_totals.project_id = projects.id”

What I want to be able to do is do some join magic stuff with AR then
when I’m looking through the returned Projects just do <%=
project.chargeable_total %>…

The first thing I tried was using :joins like so:

@project_pages,@projects  = paginate( :projects,
                      :per_page   => 100,
                      :include    => :client,
                      :joins      => "INNER JOIN (select

project_id, SUM(chargeable_value) as chargeable_total from
project_monthly_totals GROUP BY project_id) as p_totals ON
p_totals.project_id = projects.id",
:order => “job_number::float”,
:conditions => [“projects.title ilike ? OR
projects.job_number ilike ? OR clients.name iLIKE ?”,
@phrase,@phrase,@phrase]

Causes AR to generate the following SQL:
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 INNER JOIN (select project_id,
SUM(chargeable_value) as chargeable_total from project_monthly_totals
GROUP BY project_id) as p_totals ON p_totals.project_id = projects.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 as you can see doesn’t actually select the stuff from my
joined query… I tried using :select then but people told me that you
use one or the other so I tried to put the join in the select as
something like:

@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? :slight_smile:

Some people suggested making a new model based on the view (well
making the query into a view first) but that seems quite heavy
weight… I’d much much rather just have a snippet of SQL sprinkled in
if that’s possible… any ideas? :slight_smile:

-Rob

“I am a strong advocate for free thought on all subjects, yet it
appears to me (whether rightly or wrongly) that direct arguments
against christianity & theism produce hardly any effect on the public;
& freedom of thought is best promoted by the gradual illumination of
men’s minds, which follow[s] from the advance of science. It has,
therefore, been always my object to avoid writing on religion, & I
have confined myself to science.” - Darwin

“PHP is a programming language like penguins are birds. And people try
to make it fly” - int-e, #haskell

http://www.robhulme.com/

Robert H. wrote:

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.


We develop, watch us RoR, in numbers too big to ignore.