ActiveRecord.find(array_of_ids), preserving order

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?

Solution 1:

Oddly, no one has suggested something like this:

index = Something.find(array_of_ids).group_by(&:id)
array_of_ids.map { |i| index[i].first }

As efficient as it gets besides letting SQL backend do it.

Edit: To improve on my own answer, you can also do it like this:

Something.find(array_of_ids).index_by(&:id).slice(*array_of_ids).values

#index_by and #slice are pretty handy additions in ActiveSupport for arrays and hashes respectively.

Solution 2:

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(',')})")
=> [100, 1, 6]

For new Version
>> WordDocument.where(id: ids).order("field(id, #{ids.join ','})")

Update: This will be removed in Rails 6.1 Rails source code

Solution 3:

As Mike Woodhouse stated in his answer, this occurs becase, under the hood, Rails is using an SQL query with a WHERE id IN... clause to retrieve all of the records in one query. This is faster than retrieving each id individually, but as you noticed it doesn't preserve the order of the records you are retrieving.

In order to fix this, you can sort the records at the application level according to the original list of IDs you used when looking up the record.

Based on the many excellent answers to Sort an array according to the elements of another array, I recommend the following solution:

Something.find(array_of_ids).sort_by{|thing| array_of_ids.index thing.id}

Or if you need something a bit faster (but arguably somewhat less readable) you could do this:

Something.find(array_of_ids).index_by(&:id).values_at(*array_of_ids)

Solution 4:

This seems to work for postgresql (source) - and returns an ActiveRecord relation

class Something < ActiveRecrd::Base

  scope :for_ids_with_order, ->(ids) {
    order = sanitize_sql_array(
      ["position((',' || id::text || ',') in ?)", ids.join(',') + ',']
    )
    where(:id => ids).order(Arel.sql(order))
  }    
end

# usage:
Something.for_ids_with_order([1, 3, 2])

can be extended for other columns as well, e.g. for the name column, use position(name::text in ?) ...

Solution 5:

As I answered here, I just released a gem (order_as_specified) that allows you to do native SQL ordering like this:

Something.find(array_of_ids).order_as_specified(id: array_of_ids)

As far as I've been able to test, it works natively in all RDBMSes, and it returns an ActiveRecord relation that can be chained.