Rails - Aug 2009 - Active Record Equivalent to SQL Aggregate Query?

Hi,
Just started learning active record and am wondering how to best
retrieve data from multiple tables where an SQL aggregate query is
involved.

In the following example (from a medical app) I''m looking for the most
recent events of various types for each patient (e.g. last visit, last
labtest etc). As you can see from the sql query below I''m looking for
the max(date) value from a grouped query. I resorted to find_by_sql to
do this - however I''d like to learn how to do this type of query
without using find_by_sql.

IOW - how would you get the required data here using a pure
ActiveRecord approach. Below are the Table and Class defs I''m testing
with:


# Find by Sql to retrieve most recent entries for each type - note the
''max(event_date)'' here
strsql = "select  p.lname, e.patient_id, e.event_type, max
(e.event_date) as event_date
	 from events e
         inner join patients p on e.patient_id = p.id
         group by p.lname, e.patient_id, e.event_type"

Here''s the sample sql query result:

lname, patient_id, event_type, latest
''Hunt'', 3, ''Labtest'', ''2003-05-01
00:00:00''
''Hunt'', 3, ''Visit'', ''2003-03-01
00:00:00''
''Seifer'', 2, ''Labtest'', ''2002-05-01
00:00:00''
''Seifer'', 2, ''Visit'', ''2002-03-01
00:00:00''

Table Relationships are:
>>>>> Tables ---> Patients --> Events
                               --> visits
                               --> labtests
                               --> ... other
patients
      t.string :lname
      t.date :dob

events
      t.column :patient_id, :integer
      t.column :event_date, :datetime
      t.column :event_type, :string

visits
      t.column :event_id, :integer
      t.column :visittype, :string

labtests
      t.column :event_id, :integer
      t.column :testtype, :string
      t.column :testvalue, :string
>>>>> Classes
class Patient < ActiveRecord::Base
  has_many :events
  has_many :visits, :through =>:events
  has_many :labtests, :through => :events
end

class Event < ActiveRecord::Base
  has_many :visits
  has_many :labtests
  belongs_to :patient
end

class Visit < ActiveRecord::Base
  belongs_to :event
end

class Labtest < ActiveRecord::Base
    belongs_to :event
end

BrendanC wrote:
> Hi,
> Just started learning active record and am wondering how to best
> retrieve data from multiple tables where an SQL aggregate query is
> involved.
[...]

Check out the Calculations module (part of ActiveRecord).  It contains 
functions that generate SQL aggregate queries.

Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

OP here - for completeness I''m adding a link to the solution provided
on Stack Overflow:

http://stackoverflow.com/questions/1217514/can-rails-active-record-handle-sql-aggregate-queries

BC

On Aug 1, 10:49 pm, Marnen Laibow-Koser <rails-mailing-l...@andreas-
s.net> wrote:
> BrendanC wrote:
> > Hi,
> > Just started learning active record and am wondering how to best
> > retrieve data from multiple tables where an SQL aggregate query is
> > involved.
>
> [...]
>
> Check out the Calculations module (part of ActiveRecord).  It contains
> functions that generate SQL aggregate queries.
>
> Best,
> --
> Marnen Laibow-Koserhttp://www.marnen.org
> mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
> --
> Posted viahttp://www.ruby-forum.com/.