Skip to content

Instantly share code, notes, and snippets.

@typeoneerror
Created November 15, 2014 22:30
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save typeoneerror/c2814867a9bceedbe11e to your computer and use it in GitHub Desktop.
Save typeoneerror/c2814867a9bceedbe11e to your computer and use it in GitHub Desktop.
Concern for PostgreSQL sort records by ID in one query
module DokiCore
module Concerns
module Sortable
extend ActiveSupport::Concern
module ClassMethods
# Run a "single" query to update multiple records by position.
#
# The Array of passed in IDs are sorted in the order they are
# provided in the array.
#
# @param [Array] ids IDs of records to sort
#
def sort_all_by(ids)
ids = Array(ids)
records = self.where({ id: ids })
records.update_all([
"position = (STRPOS(?, ','||lpad(cast(id as text), 20, '0')||',') - 1)/21 + 1",
",#{ids.map{|x| "%020d" % x }.join(',')},"
])
records
end
end
end
end
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment