diff --git a/app/models/section.rb b/app/models/section.rb index 4d4cadf..116e95f 100644 --- a/app/models/section.rb +++ b/app/models/section.rb @@ -63,20 +63,17 @@ def self.update_numbers!(*ids, phase:) # Ensure only section ids belonging to this Phase are included. - ids = ids.map(&:to_i) & phase.section_ids + ids = ids.map(&:to_i) & phase.section_ids return if ids.empty? - # Build an Array with each ID and its relative position in the Array - values = ids.each_with_index.map { |id, i| "(#{id}, #{i + 1})" }.join(", ") - # Run a single UPDATE query for all records. - query = <<~SQL - UPDATE #{table_name} \ - SET number = svals.number \ - FROM (VALUES #{sanitize_sql(values)}) AS svals(id, number) \ - WHERE svals.id = #{table_name}.id; - SQL - connection.execute(query, "Section Update number") + case connection.adapter_name + when "PostgreSQL" then update_numbers_postgresql!(ids) + when "Mysql2" then update_numbers_mysql2!(ids) + else + update_numbers_sequentially!(ids) + end end + ## # return the title of the section # @@ -108,7 +105,32 @@ end private - def set_defaults - self.modifiable = true if modifiable.nil? + + def self.update_numbers_postgresql!(ids) + # Build an Array with each ID and its relative position in the Array + values = ids.each_with_index.map { |id, i| "(#{id}, #{i + 1})" }.join(", ") + # Run a single UPDATE query for all records. + query = <<~SQL + UPDATE #{table_name} \ + SET number = svals.number \ + FROM (VALUES #{sanitize_sql(values)}) AS svals(id, number) \ + WHERE svals.id = #{table_name}.id; + SQL + connection.execute(query) + end + + def self.update_numbers_mysql2!(ids) + ids_string = ids.map { |id| "'#{id}'" }.join(",") + update_all(%Q{ number = FIELD(id, #{sanitize_sql(ids_string)}) }) + end + + def self.update_numbers_sequentially!(ids) + ids.each_with_index.map do |id, number| + find(id).update_attribute(:number, number + 1) end + end + + def set_defaults + self.modifiable = true if modifiable.nil? + end end