ruby on rails - ActiveRecord order by external array

Question

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)

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

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

As workaround I use sorting by mysql FIELD function:

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

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.

Thanks.

Answer 1

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)

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