Ruby Datamapper group by with max

Question

I have a MySQL table:

id       int
a        varchar
b        varchar
c        varchar
version  int
active   bool

I want to grab the max version group by a, b and c, so I have the following query to do that:

select a, b, c, max(version) as version from mytbl where active = 1 group by a, b, c

I am using Datamapper with Sinatra. The above table model name is "mytbl". What would be the datamapper equivalent of the above query?

score 1 · Accepted Answer · answered Oct 18 '12 at 21:51

I got it :)

mytbl.aggregate(:version.max, :active => 1, :fields => [:a, :b, :c], :unique => true, :order => nil)

or

mytbl.aggregate(:version.max, :conditions => [ 'active = ?', 1], :fields => [:a, :b, :c], :unique => true, :order => nil)

However I could not find a way to alias max(version) as version. It returns max(version) as the column name. Thats not a big deal ;)

1 Answers1