Forum: Ruby on Rails plain sql to find

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
25cc4b4f00c2adcef7dd775576adad1e?d=identicon&s=25 Rajkumar S (Guest)
on 2006-12-27 20:05
(Received via mailing list)
Hi,

I have a query like

SELECT call_queue_id,agent_id,count(*) AS count
FROM completed_calls
GROUP BY call_queue_id,agent_id
ORDER BY call_queue_id,agent_id

on a table with following structure:

+-----------------+-------------+------+-----+---------+----------------+
| Field           | Type        | Null | Key | Default | Extra          |
+-----------------+-------------+------+-----+---------+----------------+
| id              | int(11)     |      | PRI | NULL    | auto_increment |
| call_queue_id   | int(11)     | YES  |     | NULL    |                |
| agent_id        | int(11)     |      |     | 0       |                |
| start_time      | datetime    | YES  |     | NULL    |                |
| end_time        | datetime    | YES  |     | NULL    |                |
| status          | char(1)     | YES  |     | c       |                |
+-----------------+-------------+------+-----+---------+----------------+

How can I express this query using find?

Right now I am using find_by_sql, but I would like to use find because
I want to use with_scope (I am assuming that with_scope will not work
with find_by_sql)

regards,

raj
86e084334c00a932a3675adbf769de67?d=identicon&s=25 Paul Corcoran (Guest)
on 2006-12-27 22:05
(Received via mailing list)
Try:

YourModelClass.find(:all, :select => "call_queue_id,agent_id,count(*)
AS count", :group => "call_queue_id,agent_id", :order =>
"call_queue_id,agent_id")

-Paul
25cc4b4f00c2adcef7dd775576adad1e?d=identicon&s=25 Rajkumar S (Guest)
on 2006-12-28 07:07
(Received via mailing list)
On 12/28/06, Paul Corcoran <prcorcoran@comcast.net> wrote:

> YourModelClass.find(:all, :select => "call_queue_id,agent_id,count(*)
> AS count", :group => "call_queue_id,agent_id", :order =>
> "call_queue_id,agent_id")

Thanks!! That does work, but when I tried to use :include => this as
well as :group stopped working. Is there a way to get this working
with another table included in?

raj
86e084334c00a932a3675adbf769de67?d=identicon&s=25 Paul Corcoran (Guest)
on 2006-12-28 17:08
(Received via mailing list)
Unfortunately that won't work. Rails doesn't support :group when an
:include is specified.

-Paul
This topic is locked and can not be replied to.