: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/
http://robhu.livejournal.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.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs