ActiveRecord/MySQL query to return grouped set of objects

Question

I have a model called a Statement that belongs to a Member. Given an array of members, I want to create a query that will return the most recent statement for each of those members (preferably in one nice clean query).

I thought I might be able to achieve this using group and order - something like:

# @members is already in an array

@statements = Statement.all(
  :conditions => ["member_id IN(?)", @members.collect{|m| m.id}],
  :group      => :member_id,
  :order      => "created_at DESC"
)

But unfortunately the above always returns the oldest statement for each member. I've tried swapping the order option round, but alas it always returns the oldest statement of the group rather than the most recent.

I'm guessing group_by isn't the way to achieve this - so how do I achieve it?

PS - any non Ruby/Rails people reading this, if you know how to achieve this in raw MySQL, then fire away.

Answer 1

In MySQL directly, you need a sub-query that returns the maximum created_at value for each member, which can then be joined back to the Statement table to retrieve the rest of the row.

SELECT *
FROM Statement s
    JOIN (SELECT 
              member_id, MAX(created_at) max_created_at 
          FROM Statement 
          GROUP BY member_id
    ) latest
        ON  s.member_id  = latest.member_id 
        AND s.created_at = latest.max_created_at

Answer 2

If you are using Rails 3 I would recommend taking a look at the new ActiveRecord query syntax. There is an overview at http://guides.rubyonrails.org/active_record_querying.html

I am pretty certain you could do what you are trying to do here without writing any SQL. There is an example in the "Grouping" section on that page which looks similar to what you are trying to do.