How to fetch records in order with an array of ids using the sequel gem

fetching-records-with-sequel-gem-in-order-of-array.rb

# http://sequel.jeremyevans.net/rdoc/classes/Sequel/SQL/Builders.html#method-i-case
# http://sequel.jeremyevans.net/rdoc/classes/Sequel/Dataset.html#method-i-order
# http://www.justinweiss.com/articles/how-to-select-database-records-in-an-arbitrary-order/

# Given an array of ids. How can we retrieve the records in that order.
# Using the Sequel gem.

# SELECT * FROM items WHERE id IN (2, 3, 1, 4) ORDER BY
# CASE id 
# WHEN 2 THEN 0 
# WHEN 3 THEN 1
# WHEN 1 THEN 2
# ELSE 3

# Sequel has a builder for constructing case statement.

item_ids = [2, 3, 1, 4]

Item.where(:id => item_ids)
    .order(order_same_as_array(item_ids, :id))

def order_same_as_array(array_of_ids, column_name)
  ids_with_index = array_of_ids.map.with_index.to_h

  # ids_with_index is in the form {id=>index, id=>index}
  Sequel.case(ids_with_index, ids_with_index.size, column_name)
end

# this did not work with an empty array of ids
# Sequel::Error: CaseExpression conditions must be a hash or array of all two pairs
# Maybe a bug in the specific version I was using (sequel 4.31.0)
def order_same_as_array(array_of_ids, column_name)
  ids_with_index = array_of_ids.map.with_index.map{ |id, index| [id, index] }
  # or this is shorter
  # ids_with_index = array_of_ids.map.with_index.to_a
  
  # ids_with_index is in the form [[id, index], [id, index]]
  Sequel.case(ids_with_index, ids_with_index.size, column_name)
end