Ruby-on-rails 按照给定 ID 数组的顺序按 ID 查找模型记录

Find model records by ID in the order the array of IDs were given

提问by Jonathan

I have a query to get the IDs of people in a particular order, say: ids = [1, 3, 5, 9, 6, 2]

我有一个查询以特定顺序获取人员的 ID，例如： ids = [1, 3, 5, 9, 6, 2]

I then want to fetch those people by Person.find(ids)

然后我想通过 Person.find(ids)

But they are always fetched in numerical order, I know this by performing:

但它们总是按数字顺序获取，我通过执行以下操作知道这一点：

people = Person.find(ids).map(&:id)
 => [1, 2, 3, 5, 6, 9]

How can I run this query so that the order is the same as the order of the ids array?

如何运行此查询，以便顺序与 ids 数组的顺序相同？

I made this task more difficult as I wanted to only perform the query to fetch people once, from the IDs given. So, performing multiple queries is out of the question.

我使这项任务变得更加困难，因为我只想执行查询以从给定的 ID 中获取一次人员。因此，执行多个查询是不可能的。

I tried something like:

我试过类似的东西：

ids.each do |i|
  person = people.where('id = ?', i)

But I don't think this works.

但我认为这行不通。

采纳答案by Brian Underwood

Note on this code:

请注意此代码：

ids.each do |i|
  person = people.where('id = ?', i)

There are two issues with it:

它有两个问题：

First, the #each method returns the array it iterated on, so you'd just get the ids back. What you want is a collect

首先，#each 方法返回它迭代的数组，因此您只需取回 id。你想要的是收藏

Second, the where will return an Arel::Relation object, which in the end will evaluate as an array. So you'd end up with an array of arrays. You could fix two ways.

其次， where 将返回一个 Arel::Relation 对象，该对象最终将评估为一个数组。所以你最终会得到一个数组数组。你可以通过两种方式修复。

The first way would be by flattening:

第一种方法是展平：

ids.collect {|i| Person.where('id => ?', i) }.flatten

Even better version:

更好的版本：

ids.collect {|i| Person.where(:id => i) }.flatten

A second way would by to simply do a find:

第二种方法是简单地做一个查找：

ids.collect {|i| Person.find(i) }

That's nice and simple

这很好很简单

You'll find, however, that these all do a query for each iteration, so not very efficient.

但是，您会发现这些都对每次迭代进行查询，因此效率不高。

I like Sergio's solution, but here's another I would have suggested:

我喜欢 Sergio 的解决方案，但这是我建议的另一个解决方案：

people_by_id = Person.find(ids).index_by(&:id) # Gives you a hash indexed by ID
ids.collect {|id| people_by_id[id] }

I swear that I remember that ActiveRecord used to do this ID ordering for us. Maybe it went away with Arel ;)

我发誓我记得 ActiveRecord 曾经为我们做这个 ID 排序。也许它和 Arel 一起消失了；)

回答by apeiros

As I see it, you can either map the IDs or sort the result. For the latter, there already are solutions, though I find them inefficient.

在我看来，您可以映射 ID 或对结果进行排序。对于后者，已经有了解决方案，但我发现它们效率低下。

Mapping the IDs:

映射 ID：

ids = [1, 3, 5, 9, 6, 2]
people_in_order = ids.map { |id| Person.find(id) }

Note that this will cause multiple queries to be executed, which is potentially inefficient.

请注意，这将导致执行多个查询，这可能是低效的。

Sorting the result:

结果排序：

ids = [1, 3, 5, 9, 6, 2]
id_indices = Hash[ids.map.with_index { |id,idx| [id,idx] }] # requires ruby 1.8.7+
people_in_order = Person.find(ids).sort_by { |person| id_indices[person.id] }

Or, expanding on Brian Underwoods answer:

或者，扩展 Brian Underwoods 的回答：

ids = [1, 3, 5, 9, 6, 2]
indexed_people = Person.find(ids).index_by(&:id) # I didn't know this method, TIL :)
people_in_order = indexed_people.values_at(*ids)

Hope that helps

希望有帮助

回答by activars

There are two ways to get entries by given an array of ids. If you are working on Rails 4, dynamic method are deprecated, you need to look at the Rails 4 specific solution below.

有两种方法可以通过给定的 id 数组来获取条目。如果您正在使用 Rails 4，不推荐使用动态方法，则需要查看下面的 Rails 4 特定解决方案。

Solution one:

解决方案一：

Person.find([1,2,3,4])

This will raise ActiveRecord::RecordNotFoundif no record exists

ActiveRecord::RecordNotFound如果不存在记录，这将引发

Solution two [Rails 3 only]:

解决方案二 [仅限 Rails 3]：

Person.find_all_by_id([1,2,3,4])

This will not cause exception, simply return empty array if no record matches your query.

这不会导致异常，如果没有记录匹配您的查询，只需返回空数组。

Based on your requirement choosing the method you would like to use above, then sorting them by given ids

根据您的要求选择上面要使用的方法，然后按给定的顺序对它们进行排序 ids

ids = [1,2,3,4]
people = Person.find_all_by_id(ids)
# alternatively: people = Person.find(ids)
ordered_people = ids.collect {|id| people.detect {|x| x.id == id}}

Solution [Rails 4 only]:

解决方案 [仅限 Rails 4]：

I think Rails 4 offers a better solution.

我认为 Rails 4 提供了更好的解决方案。

# without eager loading
Person.where(id: [1,2,3,4]).order('id DESC')

# with eager loading.
# Note that you can not call deprecated `all`
Person.where(id: [1,2,3,4]).order('id DESC').load

回答by Sandip Ransing

If you have idsarray then it is as simple as - Person.where(id: ids).sort_by {|p| ids.index(p.id) } OR

如果你有ids数组，那么它就像 - Person.where(id: ids).sort_by {|p| ids.index(p.id) } 或

persons = Hash[ Person.where(id: ids).map {|p| [p.id, p] }] ids.map {|i| persons[i] }

persons = Hash[ Person.where(id: ids).map {|p| [p.id, p] }] ids.map {|i| persons[i] }

回答by Sergio Tulentsev

You can get users sorted by id ascfrom the database and then rearrange them in the application any way you want. Check this out:

您可以id asc从数据库中对用户进行排序，然后以您想要的任何方式在应用程序中重新排列它们。看一下这个：

ids = [1, 3, 5, 9, 6, 2]
users = ids.sort.map {|i| {id: i}} # Or User.find(ids) or another query

# users sorted by id asc (from the query)
users # => [{:id=>1}, {:id=>2}, {:id=>3}, {:id=>5}, {:id=>6}, {:id=>9}]

users.sort_by! {|u| ids.index u[:id]} 

# users sorted as you wanted
users # => [{:id=>1}, {:id=>3}, {:id=>5}, {:id=>9}, {:id=>6}, {:id=>2}]

The trick here is sorting the array by an artificial value: index of object's id in another array.

这里的技巧是通过人工值对数组进行排序：另一个数组中对象 id 的索引。

回答by Koen.

Old question, but the sorting can be done by ordering using the SQL FIELDfunction. (Only tested this with MySQL.)

老问题，但可以通过使用 SQLFIELD函数进行排序来完成排序。（仅在 MySQL 上测试过。）

So in this case something like this should work:

所以在这种情况下，这样的事情应该有效：

Person.order(Person.send(:sanitize_sql_array, ['FIELD(id, ?)', ids])).find(ids)

Which results in the following SQL:

这导致以下 SQL：

SELECT * FROM people
  WHERE id IN (1, 3, 5, 9, 6, 2)
  ORDER BY FIELD(id, 1, 3, 5, 9, 6, 2)

回答by elliotcm

With Rails 5, I've found that this approach works (with postgres, at least), even for scoped queries, useful for working with ElasticSearch:

使用 Rails 5，我发现这种方法有效（至少使用 postgres），即使对于范围查询，对于使用 ElasticSearch 也很有用：

Person.where(country: "France").find([3, 2, 1]).map(&:id)
=> [3, 2, 1]

Note that using whereinstead of finddoes not preserve the order.

请注意，使用where而不是find不保留顺序。

Person.where(country: "France").where(id: [3, 2, 1]).map(&:id)
=> [1, 2, 3]

回答by Jerph

Most of the other solutions don't allow you to further filter the resulting query, which is why I like Koen's answer.

大多数其他解决方案不允许您进一步过滤结果查询，这就是我喜欢Koen's answer 的原因。

Similar to that answer but for Postgres, I add this function to my ApplicationRecord (Rails 5+) or to any model (Rails 4):

与该答案类似，但对于 Postgres，我将此函数添加到我的 ApplicationRecord（Rails 5+）或任何模型（Rails 4）：

def self.order_by_id_list(id_list)
  values_clause = id_list.each_with_index.map{|id, i| "(#{id}, #{i})"}.join(", ")
  joins("LEFT JOIN (VALUES #{ values_clause }) AS #{ self.table_name}_id_order(id, ordering) ON #{ self.table_name }.id = #{ self.table_name }_id_order.id")
    .order("#{ self.table_name }_id_order.ordering")
end

The query solution is from this question.

查询解决方案来自这个问题。

回答by Eskim0

If you are using MySQL, this might be a simple solution for you.

如果您使用 MySQL，这对您来说可能是一个简单的解决方案。

Post.where(sky: 'blue').order("FIELD(sort_item_field_id, 2,5,1,7,3,4)")

回答by user3033467

This simple solution costs less than joining on values:

这个简单的解决方案成本低于加入值：

order_query = <<-SQL
  CASE persons.id 
    #{ids.map.with_index { |id, index| "WHEN #{id} THEN #{index}" } .join(' ')}
    ELSE #{ids.length}
  END
SQL
Person.where(id: ids).order(order_query)