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.
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
If you don't mind receiving an array instead of an ActiveRecord Collection, you can use:
result = MyModel.find(ids).sort_by {|m| ids.index(m.id)}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With