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

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}} 

Based on my previous comment to Jeroen van Dijk you can do this more efficiently and in two lines using each_with_object

result_hash = Model.find(ids).each_with_object({}) {|result,result_hash| result_hash[result.id] = result }ids.map {|id| result_hash[id]}

For reference here is the benchmark i used

ids = [5,3,1,4,11,13,10]results = Model.find(ids)Benchmark.measure do   100000.times do     result_hash = results.each_with_object({}) {|result,result_hash| result_hash[result.id] = result }    ids.map {|id| result_hash[id]}  endend.real#=>  4.45757484436035 seconds

Now the other one

ids = [5,3,1,4,11,13,10]results = Model.find(ids)Benchmark.measure do   100000.times do     ids.collect {|id| results.detect {|result| result.id == id}}  endend.real# => 6.10875988006592

Update

You can do this in most using order and case statements, here is a class method you could use.

def self.order_by_ids(ids)  order_by = ["case"]  ids.each_with_index.map do |id, index|    order_by << "WHEN id='#{id}' THEN #{index}"  end  order_by << "end"  order(order_by.join(" "))end#   User.where(:id => [3,2,1]).order_by_ids([3,2,1]).map(&:id) #   #=> [3,2,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(',')})" )