Rails validates_uniqueness_of is causing an exponential number of queries. How do you optimize?

I have the following:

class List < ActiveRecord::Base
   accepts_nested_attributes_for :list_options, :reject_if => lambda { |a| a[:title].blank? }, :allow_destroy => true
   validates_associated :list_options

class ListOption < ActiveRecord::Base
    validates_uniqueness_of :title, :scope => [:list_id]

When a new list is created along with lets say 10 l开发者_JS百科ist items in the controller:

@list = List.create(params[:list].merge(:user_id => current_user.id)

The database get queried individually to check each listOption to see if it is unique. Is there a way to optimize the above so the db doesn't keep getting hit to check every item?

Thanks

I would suggest you create a unique index inside your database. First run script/rails generate migration unique_index to get a migration. It will be called something like db/migrate/20120313180200_unique_index.rb

Put this inside:

class UniqueIndex < ActiveRecord::Migration
  def up
    add_index(:list_options, [:list_id, :title], :unique => true)
  end

  def down
    remove_index(:list_options, :column => [:list_id, :title])
  end
end

Run rake db:migrate in your rails-root. This will fail if there already are duplicate titles as your database will immediately check that each combination of list_id and title is unique.

If you now try to save an duplicate entry into the database it will raise a ActiveRecord::RecordNotUnique exception. To handle it try something like

begin
  ListOption.save
rescue ActiveRecord::RecordNotUnique
  # Some handling code
  # use $! to access the exception for further details
end

Of cause you can also ignore these errors.

A side-effect of adding the unique-index to the database is, that queries to check whether a certain pair of list_id and title is stored inside the database, are speed up quite a bit, so you may be able to leave the uniqueness-check inside your model and still have a much better response time.