How to use an aggregate in an order by in DataMapper?

Question

I've got a simple class like:

class Foo
    include DataMapper::Resource
    property :id, Serial
    property :bar, Text, lazy: false
    property :created_on, DateTime, default: lambda { |r,p| DateTime.now }
end

I want to select them, grouped by bar and ordered by max(created_on). The SQL I need is:

SELECT "bar" FROM "foo" GROUP BY "bar" ORDER BY MAX("created_on") DESC

but I don't know how to get this with DataMapper.

I've tried something like:

Foo.all(fields: [:bar], unique: true, order: [:created_on.desc.max])

but you can't use max like that. I can't find out how to do it.

Can you help?

I had this same problem and couldn't figure out a "good" solution. The things I did were call to either SQL directly or add a field that's an aggregate (which I don't think would work in your case). — AlexQueue, Apr 02 '13 at 18:17

score 1 · Accepted Answer · answered May 13 '13 at 14:49

It seems as if using the max aggregate is not necessary. By ordering in descending order of the created_on column its going to find the MAX and go from there.

You could probably get away with:

Foo.all(fields: [:bar], unique: true, order: [created_on.desc])

without using .max in the order.

This would be same as:

SELECT "bar" FROM "foo" GROUP BY "bar" ORDER BY "created_on" DESC

Hope this works.

Also take a look at http://datamapper.org/docs/find.html

How to use an aggregate in an order by in DataMapper?

1 Answers1