Ruby-on-rails ActiveRecord.find(array_of_ids)，保留顺序

ActiveRecord.find(array_of_ids), preserving order

提问by Leonid Shevtsov

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.

当你Something.find(array_of_ids)在Rails中，所得到的数组的顺序不依赖的顺序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.

ATM 我根据 ID 的顺序手动对记录进行排序，但这有点蹩脚。

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

UPD：如果可以使用:order参数和某种 SQL 子句指定顺序，那么如何？

采纳答案by kovyrin

The answer is for mysql only

答案仅适用于 mysql

There is a function in mysql called FIELD()

mysql 中有一个函数叫FIELD()

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

以下是在 .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 ','})")

回答by Gunchars

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.

除了让 SQL 后端来做之外，它尽可能高效。

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_byand #sliceare pretty handy additions in ActiveSupportfor arrays and hashes respectively.

#index_by并且#slice是ActiveSupport中分别用于数组和散列的非常方便的补充。

回答by Ajedi32

As Mike Woodhousestated in his answer, this occurs becase, under the hood, Rails is using an SQL query with a WHERE id IN... clauseto 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.

正如Mike Woodhouse在他的回答中所说的那样，发生这种情况的原因是，Rails 正在使用带有 的 SQL 查询WHERE id IN... clause来检索一个查询中的所有记录。这比单独检索每个 id 更快，但正如您注意到的那样，它不会保留您正在检索的记录的顺序。

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.

为了解决这个问题，您可以根据您在查找记录时使用的原始 ID 列表在应用程序级别对记录进行排序。

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)

回答by gingerlime

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

这似乎适用于postgresql( source) - 并返回一个 ActiveRecord 关系

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 namecolumn, use position(name::text in ?)...

也可以扩展到其他列，例如对于name列，使用position(name::text in ?)...

回答by JacobEvelyn

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

正如我在这里回答的那样，我刚刚发布了一个 gem ( order_as_specified)，它允许您像这样执行本机 SQL 排序：

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.

据我已经能够测试，它在所有 RDBMS 中本机工作，并且它返回一个可以链接的 ActiveRecord 关系。

回答by Omar Qureshi

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:

不幸的是，在 SQL 中不可能在所有情况下都适用，您要么需要为每个记录编写单个查找或在 ruby​​ 中编写订单，尽管可能有一种方法可以使用专有技术使其工作：

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

回答by Chris Bloom

@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_byto use the OrderedHash class:

@Gunchars 的回答很好，但它在 Rails 2.3 中不能开箱即用，因为 Hash 类没有排序。一个简单的解决方法是扩展 Enumerable 类index_by以使用 OrderedHash 类：

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

现在@Gunchars 的方法将起作用

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)

回答by Christian Fazzini

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

假设Model.pluck(:id)返回[1,2,3,4]并且您想要顺序[2,4,1,3]

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

这个概念是为了利用ORDER BY CASE WHENSQL 子句。例如：

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:

在 Rails 中，您可以通过在模型中使用公共方法来构建类似的结构来实现这一点：

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)

关于这一点的好处。结果表明ActiveRecord的关系返回（允许你使用类似的方法last，count，where，pluck，等）

回答by khiav reoy

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

有一个 gem find_with_order，它允许您使用本机 SQL 查询有效地完成它。

And it supports both Mysqland PostgreSQL.

并且它同时支持Mysql和PostgreSQL。

For example:

例如：

Something.find_with_order(array_of_ids)

If you want relation:

如果你想要关系：

Something.where_with_order(:id, array_of_ids)

回答by XML Slayer

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

虽然我没有在 CHANGELOG 的任何地方看到它提到它，但看起来这个功能随着 version 的发布而改变5.2.0。

Here commitupdating the docs tagged with 5.2.0However it appears to have also been backportedinto version 5.0.

在这里提交更新标记为的文档5.2.0但是它似乎也被反向移植到 version 5.0。