ActiveRecord 按外部数组排序 [英] ActiveRecord order by external array

问题描述

我有一个 id 数组，存储在一些外部存储(rails 缓存或 redis)中.在控制器的操作中，我获取此数据并使用它选择对象，即

I have an array of ids, stored in some external storage (rails cache or redis). In controller's action I fetch this data and select object using it, i.e.

ids = [5, 1, 17, 84] # really fetched from external source
result = MyModel.where(:id => ids)

我还希望它的顺序与 array_of ids 中的 ids 相同:

I also want it to be ordered just the same as ids in array_of ids:

ids == result.map(&:id) # => true

作为解决方法，我使用 mysql FIELD 函数排序:

As workaround I use sorting by mysql FIELD function:

MyModel.where(:id => ids).order("FIELD(id, #{ids.join ', '})")

但我不喜欢这种方法，因为它是特定于 mysql 的，并且在大 ids 数组的情况下会创建很长的查询.有没有更好的，数据库不可知的方法来做到这一点?从 DB 获取未排序的数据并在 ruby​​ 端排序是不可取的，因为它资源昂贵且难以与分页一起使用.

But I don't like this approach as it is mysql specific and creates very long queries in case of big ids array. Is there better, DB-agnostic way to do it? Fetching unsorted data from DB and sorting on ruby side is undesirable because it's resource-expensive and hard to use with pagination.

谢谢.

推荐答案

我刚刚发布了一个 gem (order_as_specified) 允许您像这样执行本机 SQL 排序:

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

MyModel.where(id: ids).order_as_specified(id: ids)

它返回一个 ActiveRecord 关系，因此可以与其他方法链接:

It returns an ActiveRecord relation, and thus can be chained with other methods:

MyModel.where(id: ids).order_as_specified(id: ids).limit(3)

如果你好奇，它正在构建:

If you're curious, under the hood it's constructing:

... ORDER BY ID='5' DESC, ID='1' DESC, ID='17' DESC, ID='84'  DESC

这篇关于ActiveRecord 按外部数组排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！