# Active Record Equivalent to SQL Aggregate Query?

**URL:** <https://www.ruby-forum.com/t/active-record-equivalent-to-sql-aggregate-query/173082>\
**Category:** Rails\
**Created:** [August 1, 2009, 11:05pm UTC](https://www.ruby-forum.com/t/active-record-equivalent-to-sql-aggregate-query/173082 "2009-08-01T23:05:32Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![BrendanC](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e9c0ed/32.png) [@BrendanC](https://www.ruby-forum.com/u/BrendanC)\
**Post date:** [August 1, 2009, 11:05pm UTC](https://www.ruby-forum.com/t/active-record-equivalent-to-sql-aggregate-query/173082/1 "2009-08-01T23:05:32Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![BrendanC](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e9c0ed/32.png) [@BrendanC](https://www.ruby-forum.com/u/BrendanC)\
**Post date:** [August 2, 2009, 7:49am UTC](https://www.ruby-forum.com/t/active-record-equivalent-to-sql-aggregate-query/173082/2 "2009-08-02T07:49:19Z")

</div>

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](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![BrendanC](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e9c0ed/32.png) [@BrendanC](https://www.ruby-forum.com/u/BrendanC)\
**Post date:** [August 4, 2009, 9:19am UTC](https://www.ruby-forum.com/t/active-record-equivalent-to-sql-aggregate-query/173082/3 "2009-08-04T09:19:22Z")

</div>

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

> <https://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-