15

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.

Ineu
  • 1,363
  • 9
  • 15
  • 1
    Possible duplicate of [ActiveRecord.find(array\_of\_ids), preserving order](https://stackoverflow.com/questions/1680627/activerecord-findarray-of-ids-preserving-order) – MatayoshiMariano Aug 11 '17 at 19:32

4 Answers4

15

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
JacobEvelyn
  • 3,901
  • 1
  • 40
  • 51
  • 3
    This is quite a clever solution. Rather than add a gem, I just wrote a simple one-liner for my case that does what I need: `sql = ids.map.with_index { |id, index| index == ids.length - 1 ? "ID='#{id}' DESC" : "ID='#{id}' DESC, " }.join('')`. Thanks for the inspiration! – Daniel Bonnell Apr 30 '17 at 18:28
  • 4
    A shorter way to write the code in the comment above would be `sql = ids.map { |id| "ID='#{id}' DESC" }.join(', ')` – Anuj Jun 03 '19 at 08:30
  • This just solved a massive headache for me. Thank you! – Brad Jun 13 '22 at 06:28
11

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)}
AWM
  • 1,130
  • 11
  • 23
1

Rails 7 introduced in_order_of, which solves this exact problem. You can just write

MyModel.in_order_of(:id, ids)

Note that using it automatically introduces a where clause on the ids as well

Bruno Degomme
  • 883
  • 10
  • 11
0

If the order of the array is always the same you could add a cached order column to you're database table.

MyModel.order("cached_order")

Tom Maeckelberghe
  • 1,969
  • 3
  • 21
  • 24
  • Unfortunately it is not. Array of ids changes fast. For example it can be list of last visited #show pages, stored in Redis. – Ineu Dec 14 '11 at 14:16