Find (array_of_ids) ，保持顺序

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

link text

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.

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

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

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.

@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)

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)

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.

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 ?) ...

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.

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)

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)

With reference to the answer here

Object.where(id: ids).order("position(id::text in '#{ids.join(',')}')") works for Postgresql.

According to official doc, it should be in the same order.

https://api.rubyonrails.org/classes/ActiveRecord/FinderMethods.html#method-i-find

Something.find(array_of_ids)

the order of the resulting array should be the same order as array_of_ids. I've tested this in Rails 6.

I liked the order based options a lot. My 2c is that it makes sense to add it in a scope, so that you can use it chained with other AR methods

scope :find_in_order, ->(ids) {
where(id: ids).order([Arel.sql('FIELD(id, ?)'), ids])
}