Activerecord get multiple most recent records

Question

I have the following, simplified table:

| id | order_id | created_at |  
| 1  |  1       | 2011-07-11 |  
| 2  |  1       | 2011-07-12 |  
| 3  |  2       | 2011-07-13 |  
| 4  |  2       | 2011-07-14 |

Now i'm trying to get all records for a list of orders, but only the latest one for each order_id

EdpuOrder.find(:all, :conditions => "edpu_orders.order_id IN (#{ids})", :order => "edpu_orders.created_at")

The ids variable is generated by a subquery, let's say it's "1, 2" for now. The statement above should return the records 2 and 4. Any ideas?

Take a look at this answer and see if it helps: http://stackoverflow.com/questions/6953512/rails-3-1-with-postgresql-group-by-must-be-used-in-an-aggregate-function/6953704#6953704 — rubish, Aug 19 '11 at 07:43

score 0 · Answer 1 · answered Aug 19 '11 at 07:40

0

Find a way to put the ids in an array.

ids = [1, 2]
EdpuOrder.where(:order_id => ids).order(:created_at)

By the way this will only work for Rails 3 and above.

answered Aug 19 '11 at 07:40

Sunny

1,973
2
17
22

That would give me all four records, ordered by date while i need only need records 2 and 4... – Herp Derp Aug 19 '11 at 07:42

score 0 · Answer 2 · answered Aug 19 '11 at 08:58

0

Try this scope (i replace your_table with proper table name):

named_scope :top_orders, :from=>"(select * from your_table order by created_at desc) as tmp", :group => "order_id"

Now YourModel.top_orders should do the thing

answered Aug 19 '11 at 08:58

dfens

5,413
4
35
50

score 0 · Answer 3 · answered Aug 19 '11 at 11:23

0

EdpuOrder.where("(edpu_orders.order_id, edpu_orders.id) IN 
         (SELECT order_id, MAX(id) FROM edpu_orders GROUP BY order_id)").
order("created_at DESC")

answered Aug 19 '11 at 11:23

Sector

1,210
12
14

Activerecord get multiple most recent records

3 Answers3