当前位置： 文江博客话题详情

按指定顺序按 id 查找 ActiveRecord 对象的简洁方法

发布于 2024-07-18 03:42:17 字数 615 浏览 3 评论 0原文

我想获取给定 id 数组的 ActiveRecord 对象数组。

我假设

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

会按顺序返回一个包含对象 5、对象 2、然后对象 3 的数组，但相反，我得到一个按对象 2、对象 3、然后对象 5 排序的数组。

ActiveRecord Base find method API 提到您不应该期望它按照提供的顺序（其他文档没有给出这一点）警告）。

以相同顺序查找 id 数组？<中给出了一种可能的解决方案/a>，但 order 选项似乎对 SQLite 无效。

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

原文

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

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.

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

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.

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?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（10）

烏雲後面有陽光 2024-07-25 03:42:17

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

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

这不指定顺序，仅指定您想要返回的记录集。 事实证明，通常 MySQL 会按 'id' 顺序返回数据库行，但不能保证这一点。

让数据库按保证顺序返回记录的唯一方法是添加 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}}

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:

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.

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:

ids = [5, 2, 3]
records = Model.find(ids)
sorted_records = ids.collect {|id| records.detect {|x| x.id == id}}
回复 原文
不奢求什么 2024-07-25 03:42:17

根据我之前对 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]}

这里是我使用的基准，

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

现在另一个

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

更新

您可以在大多数使用 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]

Based on my previous comment to Jeroen van Dijk you can do this more efficiently and in two lines using 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.

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]
回复 原文
顾挽 2024-07-25 03:42:17

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

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

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

ids = [5,2,3]
@things = Object.find( ids, :order => "field(id,#{ids.join(',')})" )
回复 原文
输什么也不输骨气 2024-07-25 03:42:17

一个可移植的解决方案是在 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

使用一些 Ruby 即可轻松生成：

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

上面假设您正在使用 ids 中的数字或其他一些安全值； 如果情况并非如此，那么您需要使用 < code>connection.quote 或 ActiveRecord SQL 清理方法，以正确引用您的 ids。

然后使用 order 字符串作为您的排序条件：

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

或者在现代世界中：

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

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

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:

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:

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.

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.

回复 原文
为你鎻心 2024-07-25 03:42:17

正如我在此处回答的那样，我刚刚发布了一个 gem (order_as_specified），允许您像这样进行本机 SQL 排序：

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

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

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

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

Just tested and it works in SQLite.

回复 原文
寄与心 2024-07-25 03:42:17

Justin Weiss 写了一篇

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

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

允许您编写：

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

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

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]
回复 原文
糖粟与秋泊 2024-07-25 03:42:17

这是一个高性能的（哈希查找，而不是像检测中那样的 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

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

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
回复 原文
君勿笑 2024-07-25 03:42:17

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

Another (probably more efficient) way to do it in 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] }
回复 原文
没有伤那来痛 2024-07-25 03:42:17

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

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

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 }
回复 原文
酒几许 2024-07-25 03:42:17
@things = [5,2,3].map{|id| Object.find(id)}

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

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

回复 原文
~没有更多了~

关于作者

你没皮卡萌

暂无简介

0 文章
0 评论
21 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

不再见

文章 0 评论 0

真是无聊啊

文章 0 评论 0

樱娆

文章 0 评论 0

浅语花开

文章 0 评论 0

烛光

文章 0 评论 0

绻影浮沉

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文