Rails 3: .group() ordered by created_at

Question

What is the Rails 3 way to order .group() results in Activerecord (here by "created_at")?

@messages = Message.group(:foo)

only results in displaying the oldest message. I'd need the latest to be shown.

I tried

@messages = Message.group(:foo).having("created_at = MAX(created_at)")

with no success. Any hints appreciated!

To clarify: I'm looking to have the group ordered within itself, not a normal messages.order("..."). Should there be no easy Activerecord syntax, i'd be happy with raw SQL as well

Update: trying the SQL way, this was supposed to work:

@messages = Message.find_by_sql("
  SELECT messages.* 
  FROM messages 
  GROUP BY messages.foo 
  HAVING messages.created_at = MAX(messages.created_at) 
  ORDER BY messages.created_at DESC")

But this retrieves solely single records (those that are not grouped). Supposedly grouped ones are being omitted. Do not know why, all records have :created_at and :foo values

score 7 · Answer 1 · answered Mar 29 '12 at 21:08

You can't use order then group in the same query under some databases, so you need to do it using two queries:

message_ids = Message.select("MAX(id) AS id").group(:foo).collect(&:id)
@messages = Message.order("created_at DESC").where(:id => message_ids)

Note: this assumes that you have an auto-incremented id column, which most Rails tables do.

score 2 · Answer 2 · answered May 08 '14 at 20:56

2

You can go SQL, but you can also mostly stay in activerecord land with

@messages = Message.select('DISTINCT ON (foo) *').order(:created_at).reverse_order

The DISTINCT ON () syntax is postgres. There's a similar but slightly different syntax in MYSQL.

I think this query will end up being a lot more efficient, too, than the accepted answer because it's just a single sort and scan, instead of N sorts.

answered May 08 '14 at 20:56

TKH

828
6
11

Thank you for this. I tried to get a ordered and unique result list and had to change order on Postgresql: Deadline.select('DISTINCT ON (client_id) *').order(:client_id, :reminder_date) – Michael Schmitz Jul 22 '15 at 14:07

score 1 · Answer 3 · answered Aug 03 '14 at 20:59

I have just come across this same problem. The following Rails query worked for me:

Message.where("id IN (SELECT MAX(id) FROM messages GROUP BY id) AND state = 'unread'")

Here we use a sub-query to get the largest (and therefore most recent) ID in each group, and then filter those to only show ones where state == 'unread'.

Tip: I made a self.latest method in my Message model class that consisted of:

def self.latest
  where("id IN (SELECT MAX(id) FROM messages GROUP BY id)
end

Which meant I could use it in controllers like this:

Message.latest.where(state: 'unread')

score 1 · Accepted Answer · edited May 23 '17 at 10:33

1

Got it all working by using a more dedicated subquery, without a GROUP BY call:

SELECT *
   FROM `messages`
   WHERE `id` = (
      SELECT `id`
      FROM `messages` as `alt`
      WHERE `alt`.`foo` = `messages`.`foo`
      ORDER BY `created_at` DESC
      LIMIT 1
   )
   ORDER BY `created_at` DESC

All thanks to Chaos' answer in this thread: Select the 3 most recent records where the values of one column are distinct

Don't know about any speed implications yet though. If anyone wants to step in, please feel free to do so.

edited May 23 '17 at 10:33

Community

1
1

answered Nov 13 '11 at 12:44

David

937
1
12
22

I recommend you now consider converting this into 'rails' queries with :conditions, etc. This is part of using rails "as intended" and getting the hang of it with a fairly simple example like this would be a great exercise. Writing find_by_sql (which I did for quite a while as my background is sql databases) is part of not using the rails structures and tends to lead to quick results but they are hardcoded and often vendor specific (which also means developer specific..) and less maintainable. – Michael Durrant Nov 13 '11 at 12:46
Thanks Michael, I agree with you. Would you have an example for this query at hand? – David Nov 13 '11 at 14:54

score 0 · Answer 5 · answered Nov 07 '11 at 08:56

I don't see how this would be done using Arel easily - but if you just go with constructing your own SQL like you did in your question, then amend it as follows:

@messages = Message.find_by_sql("
  SELECT messages.* 
  FROM messages 
  GROUP BY messages.foo 
  HAVING messages.created_at = (SELECT max(created_at) FROM messages AS sub_messages WHERE sub_messages.foo = messages.foo)

Note the subquery in the HAVING clause. Your HAVING clause calculated the MAX across all your messages - as opposed to only the messages within foo. The subquery should solve that problem.

I aliased the messages table name to sub_messages - but that shouldn't be necessary. I just did it to 'show' how the parent and subquery work together.

Thank you Joerg. Tried your suggestion but the query does not return any records for me. I think it looks very resonable a query though! So I would like to figure out why this is the case? — David, Nov 12 '11 at 14:00
Just tested this with one single message, which is shown correctly. But as soon as there are more than one (so a GROUP is in effect?), neither is found. Any ideas? — David, Nov 12 '11 at 14:10

Rails 3: .group() ordered by created_at

5 Answers5