清洁方式按指定的顺序通过id查找ActiveRecord对象

I want to obtain an array of ActiveRecord objects given an array of ids.

我想在给定一组id的情况下获得一个ActiveRecord对象数组。

I assumed that

我认为

Object.find([5,2,3])

Would return an array with object 5, object 2, then object 3 in that order, but instead I get an array ordered as object 2, object 3 and then object 5.

将返回一个数组，其中包含对象5，对象2，然后按顺序返回对象3，但我得到的数组按对象2，对象3和对象5排序。

The ActiveRecord Base find method API mentions that you shouldn't expect it in the order provided (other documentation doesn't give this warning).

ActiveRecord Base查找方法API提到您不应该按照提供的顺序期望它（其他文档不提供此警告）。

One potential solution was given in Find by array of ids in the same order?, but the order option doesn't seem to be valid for SQLite.

一个可能的解决方案是以相同的顺序在“按数组查找”中给出的，但是顺序选项似乎对SQLite无效。

I can write some ruby code to sort the objects myself (either somewhat simple and poorly scaling or better scaling and more complex), but is there A Better Way?

我可以编写一些ruby代码来自己对对象进行排序（有点简单，缩放比例较差或缩放比较复杂），但有更好的方法吗？

10 个解决方案

#1

It's not that MySQL and other DBs sort things on their own, it's that they don't sort them. When you call Model.find([5, 2, 3]), the SQL generated is something like:

MySQL和其他数据库并不是自己排序的，而是他们不对它们进行排序。当您调用Model.find（[5,2,3]）时，生成的SQL类似于：

SELECT * FROM models WHERE models.id IN (5, 2, 3)

This doesn't specify an order, just the set of records you want returned. It turns out that generally MySQL will return the database rows in 'id' order, but there's no guarantee of this.

这不指定订单，只指定要返回的记录集。事实证明，通常MySQL会以“id”顺序返回数据库行，但不能保证这一点。

The only way to get the database to return records in a guaranteed order is to add an order clause. If your records will always be returned in a particular order, then you can add a sort column to the db and do Model.find([5, 2, 3], :order => 'sort_column'). If this isn't the case, you'll have to do the sorting in code:

使数据库以保证顺序返回记录的唯一方法是添加一个order子句。如果您的记录将始终按特定顺序返回，那么您可以向db添加排序列并执行Model.find（[5,2,3]，：order =>'sort_column'）。如果不是这种情况，您将不得不在代码中进行排序：

ids = [5, 2, 3]
records = Model.find(ids)
sorted_records = ids.collect {|id| records.detect {|x| x.id == id}}

#2

Based on my previous comment to Jeroen van Dijk you can do this more efficiently and in two lines using each_with_object

根据我之前对Jeroen van Dijk的评论，您可以使用each_with_object更有效地使用两行

result_hash = Model.find(ids).each_with_object({}) {|result,result_hash| result_hash[result.id] = result }
ids.map {|id| result_hash[id]}

For reference here is the benchmark i used

这里参考的是我使用的基准

ids = [5,3,1,4,11,13,10]
results = Model.find(ids)

Benchmark.measure do 
  100000.times do 
    result_hash = results.each_with_object({}) {|result,result_hash| result_hash[result.id] = result }
    ids.map {|id| result_hash[id]}
  end
end.real
#=>  4.45757484436035 seconds

Now the other one

现在是另一个

ids = [5,3,1,4,11,13,10]
results = Model.find(ids)
Benchmark.measure do 
  100000.times do 
    ids.collect {|id| results.detect {|result| result.id == id}}
  end
end.real
# => 6.10875988006592

Update

更新

You can do this in most using order and case statements, here is a class method you could use.

您可以在大多数情况下使用order和case语句执行此操作，这是您可以使用的类方法。

def self.order_by_ids(ids)
  order_by = ["case"]
  ids.each_with_index.map do |id, index|
    order_by << "WHEN id='#{id}' THEN #{index}"
  end
  order_by << "end"
  order(order_by.join(" "))
end

#   User.where(:id => [3,2,1]).order_by_ids([3,2,1]).map(&:id) 
#   #=> [3,2,1]

#3

Apparently mySQL and other DB management system sort things on their own. I think that you can bypass that doing :

显然，mySQL和其他数据库管理系统可以自行排序。我认为你可以绕过这样做：

ids = [5,2,3]
@things = Object.find( ids, :order => "field(id,#{ids.join(',')})" )

#4

A portable solution would be to use an SQL CASE statement in your ORDER BY. You can use pretty much any expression in an ORDER BY and a CASE can be used as an inlined lookup table. For example, the SQL you're after would look like this:

可移植的解决方案是在ORDER BY中使用SQL CASE语句。您可以在ORDER BY中使用几乎任何表达式，CASE可以用作内联查找表。例如，您之后的SQL将如下所示：

select ...
order by
    case id
    when 5 then 0
    when 2 then 1
    when 3 then 2
    end

That's pretty easy to generate with a bit of Ruby:

用一点Ruby生成它很容易：

ids = [5, 2, 3]
order = 'case id ' + (0 .. ids.length).map { |i| "when #{ids[i]} then #{i}" }.join(' ') + ' end'

The above assumes that you're working with numbers or some other safe values in ids; if that's not the case then you'd want to use connection.quote or one of the ActiveRecord SQL sanitizer methods to properly quote your ids.

以上假设您正在使用ID中的数字或其他安全值;如果不是这种情况，那么您需要使用connection.quote或其中一个ActiveRecord SQL清理程序方法来正确引用您的ID。

Then use the order string as your ordering condition:

然后使用订单字符串作为您的订购条件：

Object.find(ids, :order => order)

or in the modern world:

或者在现代世界：

Object.where(:id => ids).order(order)

This is a bit verbose but it should work the same with any SQL database and it isn't that difficult to hide the ugliness.

这有点冗长，但它应该与任何SQL数据库一样，并且隐藏丑陋并不困难。

#5

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排序：

Object.where(id: [5, 2, 3]).order_as_specified(id: [5, 2, 3])

Just tested and it works in SQLite.

刚刚测试过，它可以在SQLite中运行。

#6

Justin Weiss wrote a blog article about this problem just two days ago.

就在两天前，Justin Weiss写了一篇关于这个问题的博客文章。

It seems to be a good approach to tell the database about the preferred order and load all records sorted in that order directly from the database. Example from his blog article:

这似乎是告诉数据库有关首选顺序并直接从数据库加载按该顺序排序的所有记录的好方法。他博客文章的例子：

# in config/initializers/find_by_ordered_ids.rb
module FindByOrderedIdsActiveRecordExtension
  extend ActiveSupport::Concern
  module ClassMethods
    def find_ordered(ids)
      order_clause = "CASE id "
      ids.each_with_index do |id, index|
        order_clause << "WHEN #{id} THEN #{index} "
      end
      order_clause << "ELSE #{ids.length} END"
      where(id: ids).order(order_clause)
    end
  end
end

ActiveRecord::Base.include(FindByOrderedIdsActiveRecordExtension)

That allows you to write:

这允许你写：

Object.find_ordered([2, 1, 3]) # => [2, 1, 3]

#7

Another (probably more efficient) way to do it in Ruby:

在Ruby中另一种（可能更有效）的方法：

ids = [5, 2, 3]
records_by_id = Model.find(ids).inject({}) do |result, record| 
  result[record.id] = record
  result
end
sorted_records = ids.map {|id| records_by_id[id] }

#8

Here's the simplest thing I could come up with:

这是我能想到的最简单的事情：

ids = [200, 107, 247, 189]
results = ModelObject.find(ids).group_by(&:id)
sorted_results = ids.map {|id| results[id].first }

#9

Here's a performant (hash-lookup, not O(n) array search as in detect!) one-liner, as a method:

这是一个高性能（散列查找，而不是检测中的O（n）数组搜索！）one-liner，作为一种方法：

def find_ordered(model, ids)
  model.find(ids).map{|o| [o.id, o]}.to_h.values_at(*ids)
end

# We get:
ids = [3, 3, 2, 1, 3]
Model.find(ids).map(:id)          == [1, 2, 3]
find_ordered(Model, ids).map(:id) == ids

#10

-1

@things = [5,2,3].map{|id| Object.find(id)}

This is probably the easiest way, assuming you don't have too many objects to find, since it requires a trip to the database for each id.

这可能是最简单的方法，假设您没有太多要查找的对象，因为它需要为每个id访问数据库。

#1