ActiveRecord order by external array

Question

I have an array of identifiers stored in some external storage (cache rails or redis). In the controller action, I extract this data and select the 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 in exactly the same way as the ides in array_of ids:

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

As a workaround, I use sorting by mysql FIELD function:

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

But I do not like this approach, since it is specific to mysql and creates very long queries in the case of a large ids array. Is there a better DB agnostic way to do this? Retrieving unsorted data from the database and sorting on the ruby ​​side is undesirable because it is an expensive resource and difficult to use with pagination.

Thank.

Answer 1

I just released a gem ( order_as_specified ) that allows you to execute your own SQL order as follows:

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

It returns an ActiveRecord relationship and thus can be bound to other methods:

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

If you are interested, under the hood it is built:

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

Answer 2

If you do not want to receive an array instead of the ActiveRecord collection, you can use:

 result = MyModel.find(ids).sort_by {|m| ids.index(m.id)} 

Answer 3

If the order of the array is always the same, you can add a cached order column to the database table.

MyModel.order ("cached_order")