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

Answer 1

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.

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

Answer 3

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.