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?
-
Or Ruby on Rails: Is there any way to pull items from the database and have them returned in a specified order?mu is too short– mu is too short2018-01-06 18:47:31 +00:00Commented Jan 6, 2018 at 18:47
Add a comment
|
2 Answers
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
1 Comment
Jerome
I appreciate the succinct-ness! But this is spot on to the train of thought, using the index of the array.
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]
3 Comments
mu is too short
This is the sane approach IMO. I'd probably
include FindByOrderedIdsActiveRecordExtension in ApplicationRecord in Rails5 though.Jerome
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!
spickermann
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.