Rails - Sep 2006 - :joins or :select associating with a database view... is this possible?

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? :-)

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? :-)

-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/

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

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


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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---