Rails - Nov 2006 - Re: linking database when primary key is not id and doing some counting

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

--~--~---------~--~----~------------~-------~--~----~
 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?hl=en
-~----------~----~----~----~------~----~------~--~---