How to use several aggregate functions at once in Rails/ActiveRecord?

Question

I want to do several aggregate functions at once, eg to get the max and min id's grouped by status:

Model.maximum(:id).minimum(:id).group(:status)

This doesnt work (at least with Rails 3.1.1) - you get an error on the minimum call, saying its not defined on Fixnum.

NoMethodError: undefined method `minimum' for 22377:Fixnum

I could do raw sql for it - but just wondering if there is a higher level/Rails option...

Thanks, Chris

Answer 1

I've had a similar issue which I solved using group with pluck in Rails 4. E.g.

Model.group(:status).pluck('min(id)','max(id)')

Also works with having, order etc and with computed columns.

Model.group('CAST(created_at AS Date), EXTRACT(HOUR FROM created_at), floor(EXTRACT(MINUTE FROM created_at)/15) * 15')
     .having('count(id) > 10')
     .order('min(created_at)')
     .pluck('count(id)')

Cheers

Answer 2

I believe @topek is right that you can't chain the calculation functions like this. I think you'll have to use SQL in a select predicate, e.g.:

Model.select('MAX(id) AS `maximum`, MIN(id) AS `minimum`').group(:status)

I just tested this in a project of my own and it appears to work as expected.