Script to repair duplicate positions on subjects/topics/steps

duplicate_position.rb

## The following was written to be run via the console. But can be easily modifed to fit within a service object that we can call to check and repair issues with duplicate positions. 

## There may and probably will be a more effiency query, as I wrote this rather quickly. Feel to free to alter as you see fit. Just as long as it still serves its intended purpose.

# Returns the items that have a duplicate position
def items_with_duplicated_position(account)
  @duped_positions = account.curriculums.select(:account_id, :position).group(:position, :account_id).having("COUNT(*) > 1").pluck(:position)
  account.curriculums.where(position: @duped_positions)
end

# Checks if a list has no repeating indices
# Returns true if at least one value position_column has a duplicate value
def does_list_have_duplicates?(account)
  items_with_duplicated_position(account).exists?
end

# Repairs a list that has duplicate positions by moving each of the duplicated up in position until there are not more duplicates
# NOTE: We cannot ensure a repeatable order of the duplicate entries since their original position information was lost upon duplication
def repair_duplicate_positions_in_list(account)
  # While there are duplicates we will increment the position of the second repetition for every number
  while does_list_have_duplicates?(account) do
    # Get one of the entries that have a repeated position
    first_repeated_entry = items_with_duplicated_position(account).first
    # Get the position with repetitions
    repeated_pos = first_repeated_entry.send(:position)
    # Increment the position of all entries with position equal or greater than the current repeted position that is not the first entry
    account.curriculums.where("curriculums.position >= ?", repeated_pos).where.not("curriculums.id = ?", first_repeated_entry.id).increment_all
  end
end

#Identify accounts and call the method to check and fix them

# Accounts with known issues or can be all active accounts.
@bad_accounts = Account.where(id: [])
@bad_accounts.each do |account|
  repair_duplicate_positions_in_list(account)
end