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?

回答1:

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]

回答2:

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.

回答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)

回答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(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 ?) ...

回答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.

回答6:

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

回答7:

@Gunchars answer is great, but it doesn't work out of the box in Rails 2.3 because the Hash class is not ordered. A simple workaround is to extend the Enumerable class' index_by to use the OrderedHash class:

module Enumerable
  def index_by_with_ordered_hash
    inject(ActiveSupport::OrderedHash.new) do |accum, elem|
      accum[yield(elem)] = elem
      accum
    end
  end
  alias_method_chain :index_by, :ordered_hash
end

Now @Gunchars' approach will work

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

Bonus

module ActiveRecord
  class Base
    def self.find_with_relevance(array_of_ids)
      array_of_ids = Array(array_of_ids) unless array_of_ids.is_a?(Array)
      self.find(array_of_ids).index_by(&:id).slice(*array_of_ids).values
    end
  end
end

Then

Something.find_with_relevance(array_of_ids)

回答8:

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.

回答9:

Although I don't see it mentioned anywhere in a CHANGELOG, it looks like this functionality was changed with the release of version 5.2.0.

Here commit updating the docs tagged with 5.2.0 However it appears to have also been backported into version 5.0.

回答10:

Assuming Model.pluck(:id) returns [1,2,3,4] and you want the order of [2,4,1,3]

The concept is to to utilize the ORDER BY CASE WHEN SQL clause. For example:

SELECT * FROM colors
  ORDER BY
  CASE
    WHEN code='blue' THEN 1
    WHEN code='yellow' THEN 2
    WHEN code='green' THEN 3
    WHEN code='red' THEN 4
    ELSE 5
  END, name;

In Rails, you can achieve this by having a public method in your model to construct a similar structure:

def self.order_by_ids(ids)
  if ids.present?
    order_by = ["CASE"]
    ids.each_with_index do |id, index|
      order_by << "WHEN id='#{id}' THEN #{index}"
    end
    order_by << "END"
    order(order_by.join(" "))
  end
else
  all # If no ids, just return all
end

Then do:

ordered_by_ids = [2,4,1,3]

results = Model.where(id: ordered_by_ids).order_by_ids(ordered_by_ids)

results.class # Model::ActiveRecord_Relation < ActiveRecord::Relation

The good thing about this. Results are returned as ActiveRecord Relations (allowing you to use methods like last, count, where, pluck, etc)

回答11:

There is a gem find_with_order which allows you to do it efficiently by using native SQL query.

And it supports both Mysql and PostgreSQL.

For example:

Something.find_with_order(array_of_ids)

If you want relation:

Something.where_with_order(:id, array_of_ids)

回答12:

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

link text