Dealing with complex query by find_by_sql


#1

Hi
I have a query like
sql = “SELECT count(DISTINCT r.id) as req_count,
round(SUM(h.assigned_days)/count(DISTINCT r.id),1) as
avg_days,h.role_id,EXTRACT(YEAR from r.created_at)||’-’||EXTRACT(MONTH
from r.created_at) as period, max(r.created_at) as created_date FROM
my_requests r, my_request_histories h WHERE r.id > 0 AND h.my_request_id
= r.id GROUP BY period,h.role_id”

Here I get results like
req_count | avg_days | role_id | period | created_date
-----------±---------±--------------------±-------±---------------------------
18 | 0.0 | 11 | 2009-3 | 2009-03-23
12:50:17.34481
6 | 0.0 | 12 | 2009-3 | 2009-03-22
14:29:03.141328
4 | 0.0 | 13 | 2009-3 | 2009-03-22
14:29:03.200008
6 | 0.0 | 14 | 2009-3 | 2009-03-22
14:29:03.100028
20 | 2 | 11 | 2009-4 | 2009-04-23
12:50:17.78481
7 | 1 | 12 | 2009-4 | 2009-04-22
14:29:03.297128
10 | 4 | 13 | 2009-4 | 2009-04-22
14:29:03.297128
6 | 0.0 | 14 | 2009-4 | 2009-04-22
14:29:03.297128

So to get the above I executed like
records = MyRequest.find_by_sql(sql)
I would like to know how to collect the records from above by
role and month wise. I need to draw a graph(x axis req_count and y axis
avg_days).For example for role_id 11 in march 2009 values (18,0.0) and
for april 2009 values(20,2) Similarly for role_id 12,13 etc
here I have to draw 4 graphs That is for role 11,12,13 and 14
I know how to draw graph but don’t know how to collect the data
from above array.

Please help
Thanks in advance
Sijo


#2

Hi
I didnot get an answer to this yet
Sijo