orderable.rb

module Orderable

  extend ActiveSupport::Concern

  module ClassMethods

    ##
    # Run a "single" query to update multiple records by a position field.
    #
    # The Array of passed in IDs are sorted in the order they are provided in the array.
    #
    # @param [Array,Integer] ids IDs or ID of records to sort
    # @param [String] parent_field If set, reparent the record to a new parent
    # @param [Integer] parent_id New parent record ID
    #
    # @return [Array] The newly sorted records
    def sort_all_by(ids, parent_field = nil, parent_id = nil)
      unless ids.is_a?(Array)
        ids = [ids]
      end
      ids = ids.map { |id| id.to_i }.reject { |id| id.zero? }.uniq

      fields = "position = (STRPOS(?, ','||lpad(cast(id as text), 20, '0')||',') - 1)/21 + 1"
      sort_values = ",#{ids.map { |x| format('%020d', x) }.join(',')},"

      fields = "#{fields}, #{parent_field} = ?" if [parent_field, parent_id].any?

      query = [fields, sort_values]
      query.push(parent_id) unless parent_id.nil?

      records = where({ id: ids })
      records.update_all(query)
      records
    end

  end

end