1

With a hard-coded array

array = [30,29,31,13,10,12,6,7,8,9,11]

trying to execute a query 

 @pick = Item.where('id IN (?)', array).to_a

How can the order of Items chosen keep the order of initial array?

Jerome
  • 5,583
  • 3
  • 33
  • 76
  • Or [**Ruby on Rails: Is there any way to pull items from the database and have them returned in a specified order?**](https://stackoverflow.com/q/12502222/479863) – mu is too short Jan 06 '18 at 18:47

2 Answers2

5

Assuming you're fetching all the items in a single request (i.e. no pagination) then you could sort the items after fetching using the indices from the initial array e.g.

@pick = Item.where('id IN (?)', array).sort_by do |item|
  array.index(item.id)
end
mikej
  • 65,295
  • 17
  • 152
  • 131
  • 1
    I appreciate the succinct-ness! But this is spot on to the train of thought, using the index of the array. – Jerome Jan 06 '18 at 17:07
2

It is a good approach to tell the database the preferred order and load all records sorted in that order directly from the database instead of sorting the records in Ruby.

Add the following extension to your application:

# e.g. in config/initializers/find_by_ordered_ids.rb
module FindByOrderedIdsActiveRecordExtension
  extend ActiveSupport::Concern

  module ClassMethods
    def find_ordered(ids)
      order_clause = "CASE #{self.table_name}.id "
      ids.each_with_index do |id, index|
        order_clause << "WHEN #{id} THEN #{index} "
      end
      order_clause << "ELSE #{ids.length} END"

      where(id: ids).order(order_clause)
    end
  end
end

ActiveRecord::Base.include(FindByOrderedIdsActiveRecordExtension)

Than you can write queries like this:

Item.find_ordered([2, 1, 3]) # => [2, 1, 3]
spickermann
  • 100,941
  • 9
  • 101
  • 131
  • This is the sane approach IMO. I'd probably `include FindByOrderedIdsActiveRecordExtension` in `ApplicationRecord` in Rails5 though. – mu is too short Jan 06 '18 at 18:49
  • I do see the sanity of this approach. Getting an error `ERROR: syntax error at or near "{"` when the module fires up. I do not see where the proposed code can be off! – Jerome Jan 09 '18 at 09:49
  • I am sorry, I missed a `#` in front of the `{self.table_name}` in the first line of the `find_ordered` method. I updated my answer. – spickermann Jan 09 '18 at 12:16