按指定顺序按 id 查找 ActiveRecord 对象的干净方法

Clean way to find ActiveRecord objects by id in the order specified(按指定顺序按 id 查找 ActiveRecord 对象的干净方法)

问题描述

我想获得一个给定 id 数组的 ActiveRecord 对象数组.

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

我认为

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

会按顺序返回一个包含对象 5、对象 2、然后是对象 3 的数组，但我得到的数组顺序为对象 2、对象 3 和对象 5.

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.

ActiveRecord Base find method API 提到您不应该期望它按照提供的顺序(其他文档没有给出这个警告).

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

按同一数组中的 id 查找提供了一种可能的解决方案order?，但 order 选项似乎对 SQLite 无效.

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.

我可以自己编写一些 ruby​​ 代码来对对象进行排序(有点简单但缩放效果不佳，或者缩放效果更好但更复杂)，但有更好的方法吗?

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?

推荐答案

不是 MySQL 和其他 DB 自己排序，而是它们不排序.当你调用 Model.find([5, 2, 3]) 时，生成的 SQL 是这样的:

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)

这并没有指定顺序，只是您想要返回的记录集.事实证明，一般 MySQL 会以 'id' 顺序返回数据库行，但不能保证这一点.

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.

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