Clean way to find ActiveRecord objects by id in the order specified

Question

I want to obtain an array of ActiveRecord objects given an array of ids.

I assumed that

Object.find([5,2,3])

Would return an array with object 5, object 2, then object 3 in that order, but instead I get an array ordered as object 2, object 3 and then object 5.

The ActiveRecord Base find method API mentions that you shouldn't expect it in the order provided (other documentation doesn't give this warning).

One potential solution was given in Find by array of ids in the same order?, but the order option doesn't seem to be valid for SQLite.

I can write some ruby code to sort the objects myself (either somewhat simple and poorly scaling or better scaling and more complex), but is there A Better Way?

Answer 1

Apparently mySQL and other DB management system sort things on their own. I think that you can bypass that doing :

ids = [5,2,3]
@things = Object.find( ids, :order => "field(id,#{ids.join(',')})" )

Hope that helps

Answer 2

@things = [5,2,3].map{|id| Object.find(id)}

This is probably the easiest way, assuming you don't have too many objects to find, since it requires a trip to the database for each id.

Answer 3

It's not that MySQL and other DBs sort things on their own, it's that they don't sort them. When you call Model.find([5, 2, 3]), the SQL generated is something like:

SELECT * FROM models WHERE models.id IN (5, 2, 3)

This doesn't specify an order, just the set of records you want returned. It turns out that generally MySQL will return the database rows in 'id' order, but there's no guarantee of this.

The only way to get the database to return records in a guaranteed order is to add an order clause. If your records will always be returned in a particular order, then you can add a sort column to the db and do Model.find([5, 2, 3], :order => 'sort_column'). If this isn't the case, you'll have to do the sorting in code:

ids = [5, 2, 3]
records = Model.find(ids)
sorted_records = ids.collect {|id| records.detect {|x| x.id == id}}