按指定顺序按 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 查找方法 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').如果不是这种情况,则必须在代码中进行排序:

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

相关文章