ActiveRecord.find(array_of_ids), preserving order

Question

When you do Something.find(array_of_ids) in Rails, the order of the resulting array does not depend on the order of array_of_ids.

Is there any way to do the find and preserve the order?

ATM I manually sort the records based on order of IDs, but that is kind of lame.

UPD: if it's possible to specify the order using the :order param and some kind of SQL clause, then how?

Answer 1

There is an order clause in find (:order=>'...') which does this when fetching records. You can get help from here also.

link text

Answer 2

Under the hood, find with an array of ids will generate a SELECT with a WHERE id IN... clause, which should be more efficient than looping through the ids.

So the request is satisfied in one trip to the database, but SELECTs without ORDER BY clauses are unsorted. ActiveRecord understands this, so we expand our find as follows:

Something.find(array_of_ids, :order => 'id')

If the order of ids in your array is arbitrary and significant (i.e. you want the order of rows returned to match your array irrespective of the sequence of ids contained therein) then I think you'd be best server by post-processing the results in code - you could build an :order clause but it would be fiendishly complicated and not at all intention-revealing.

Answer 3

Not possible in SQL that would work in all cases unfortunately, you would either need to write single finds for each record or order in ruby, although there is probably a way to make it work using proprietary techniques:

First example:

sorted = arr.inject([]){|res, val| res << Model.find(val)}

VERY INEFFICIENT

Second example:

unsorted = Model.find(arr)
sorted = arr.inject([]){|res, val| res << unsorted.detect {|u| u.id == val}}

Answer 4

The answer is for mysql only

There is a function in mysql called FIELD()

Here is how you could use it in .find():

>> ids = [100, 1, 6]
=> [100, 1, 6]

>> WordDocument.find(ids).collect(&:id)
=> [1, 6, 100]

>> WordDocument.find(ids, :order => "field(id, #{ids.join(',')})").collect(&:id)
=> [100, 1, 6]