Hello all,
I am adding one more question to my previous posting.
I have 2 tables that I had created with migrations, but these are
based on existing database. So the automatically created id column is
not used. These tables have the following structure.
±------------±-------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------------±-------------±-----±----±--------±---------------+
| id | int(11) | | PRI | NULL | auto_increment |
| clid | varchar(80) | YES | | NULL | |
| src | varchar(80) | YES | | NULL | |
| dst | varchar(80) | YES | | NULL | |
| start | datetime | YES | | NULL | |
| ivr_id | int(11) | YES | MUL | NULL | |
| file_name | varchar(255) | YES | | NULL | |
±------------±-------------±-----±----±--------±---------------+
and Table clients
±----------±--------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±----------±--------±-----±----±--------±---------------+
| id | int(11) | | PRI | NULL | auto_increment |
| ivr_id | int(11) | YES | MUL | NULL | |
| is_online | char(1) | YES | | NULL | |
±----------±--------±-----±----±--------±---------------+
The ivr_id in calls table is the foreign key referring to ivr_id in
clients table.
I have my models defined as:
class Call < ActiveRecord::Base
belongs_to :client, :foreign_key => “ivr_id”
class Client < ActiveRecord::Base
has_one :call, :foreign_key => “ivr_id”
now I want to find out the count of all online clients, which means
all calls which have is_online = ‘Y’ in the clients table with
corresponding ivr_id. I did kind of solved this by the following
method.
def self.total_calls_online (from_time, to_time)
find_by_sql ([“SELECT count(*) AS count_all
FROM calls,clients
WHERE (calls.start > :from_time and calls.start <=
:to_time
and calls.file_name like ‘%wav’
and calls.ivr_id = clients.ivr_id
and clients.is_online = ‘Y’)”,
{:from_time => from_time, :to_time => to_time}])
end
Now this to me seems not the rails way of ding things. My another
query in the model is
def self.total_calls (from_time, to_time)
count(:conditions => [ "start > :from_time and start <= :to_time
and file_name like ‘%wav’$ {:from_time => from_time, :to_time =>
to_time} ])
end
here I am just using the count method to get the total count of rows.
I would like to rewrite the previous method also to use count, from
the find_by_sql I did look a lot to find out how to do this, but could
not, and finally settled with find_by_sql
Thanks a lot in advance for your help,
raj