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 see how to do this 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