get latest of each out of one rails model

Question

Hi everybody,

I have one model, which I save changes witch happens to certain person. I want to get now the latest state of all person.

so I need to: - order: created_at asc. - find_all: limit: one change each person

how can I accomplish that?

Thanks, Markus

Answer 1

You need to use :group, which translates to a GROUP BY statement in SQL.

YourModel.all { :order => 'created_at DESC', :group => :person_id } 

Answer 2

Try this:

person_hash = OrderState.maximum(:created_at, :group => :person_id)
person_hash.each |person_id, created_at|
  state = OrderState.find_by_person_id_and_created_at(person_id, created_at)
end

Refer to the ActiveRecord documentation for more details.

If you want to do most of the calculations in the DB, try this:

OrderState.find_by_sql("SELECT A.* FROM order_states A,
                (SELECT person_id, MAX(BA.created_at) AS created_at 
                 FROM order_states BA 
                 GROUP BY BA.person_id
                 ) AS B
WHERE  A.person_id = B.person_id AND 
       A.created_at = B.created_at")

In both approaches you should index created_at column. If you have millions of users this is not a good strategy. You should store the last state in the Person table.