dweebd

So you forgot to add a uniqueness constraint to a table in your db and over time, race conditions have wreaked havoc on your validates_uniqueness_of validations.

Time to clean out those duplicates!

module DuplicateFinder
 
  def find_duplicate_sets_on_keys *uniqueness_keys
    uniqueness_list = uniqueness_keys.join(', ')
    find(:all, :select => "#{uniqueness_list}, COUNT(#{primay_key}) AS total", :group => "#{uniqueness_list} HAVING total > 1")
  end
 
  def find_duplicates_for_set duplicate_set, options = {}
    uniqueness_keys = duplicate_set.attributes.reject do |attr_name, attr_value| attr_name == 'total' end.keys
    condition_values = uniqueness_keys.map do |uniqueness_key| duplicate_set.send uniqueness_key end
    uniqueness_condition = [ uniqueness_keys.map do |uniqueness_key| "#{uniqueness_key} = ?" end.join(' AND ') ] + condition_values
 
    with_scope :find => { :conditions => uniqueness_condition } do
      find(:all, options)
    end
  end
 
end
 
class ActiveRecord::Base
 
  class << self
    include DuplicateFinder
  end
 
end

Now you find and operate on duplicates within your migration like so:

require 'duplicate_finder'
 
class AddIndexForFavoriteColor < ActiveRecord::Migration
  class FavoriteColor < ActiveRecord::Base
    def self.destroy_duplicates!
      find_duplicate_sets_on_keys(:user_id, :color_id).each do |duplicate_set|
        dups = find_duplicates_for_set(duplicate_set)
 
        # All but the last
        dups[0..-2].each do |duplicate|
          duplicate.destroy
        end
 
      end
    end
  end
 
  def self.up
    FavoriteColor.destroy_duplicates!
    add_index :favorite_colors, [:user_id, :color_id], :unique => true
  end
 
  def self.down
    remove_index :favorite_colors, :column => [:user_id, :color_id]
  end
end

I split the find_duplicate_sets_on_keys and find_duplicates_for_set up to minimize the number of records instantiated at any given time.