Ruby-on-rails 按指定顺序按 id 查找 ActiveRecord 对象的清洁方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/801824/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Clean way to find ActiveRecord objects by id in the order specified
提问by Andrew Grimm
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 APImentions that you shouldn't expect it in the order provided (other documentation doesn't give this warning).
ActiveRecord Base find 方法 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.
Find 中按相同顺序的 id 数组给出了一种可能的解决方案？，但 order 选项似乎对 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 代码来自己对对象进行排序（有些简单且缩放性差，或者缩放性更好但更复杂），但是有更好的方法吗？
采纳答案by tomafro
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 和其他 DB 自己对事物进行排序，而是它们不对它们进行排序。当您调用 时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 子句。如果您的记录总是以特定顺序返回，那么您可以向数据库添加一个排序列并执行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}}
回答by Schneems
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]
回答by marcgg
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(',')})" )
回答by mu is too short
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.quoteor one of the ActiveRecord SQL sanitizer methodsto properly quote your ids.
以上假设您正在使用数字或其他一些安全值ids；如果不是这样，那么你想使用connection.quote或的一个ActiveRecord的SQL消毒方法正确引用您ids。
Then use the orderstring as your ordering condition:
然后使用order字符串作为您的订购条件：
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 数据库，并且隐藏丑陋并不难。
回答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 排序：
Object.where(id: [5, 2, 3]).order_as_specified(id: [5, 2, 3])
Just tested and it works in SQLite.
刚刚测试过，它可以在 SQLite 中使用。
回答by spickermann
Justin Weiss wrote a blog article about this problemjust 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]
回答by Kanat Bolazar
Here's a performant (hash-lookup, not O(n) array search as in detect!) one-liner, as a method:
这是一种高性能（哈希查找，而不是检测中的 O(n) 数组搜索！）单行，作为一种方法：
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
回答by Jeroen van Dijk
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] }
回答by jasongarber
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 }
回答by jcnnghm
@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 访问数据库。
