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

Solution 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.

Solution 2

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.

Solution 3

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.

Solution 4

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')

View more solutions

Comments

[David]

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]

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?