How can I use uniqueness validation for all records, include deleted.

[dmitrychopey]

In my application I need to validate email uniqueness for all records, not only not deleted.

[gkunwar]

@ALL @davidpiegza @radar is there any progress in this issue?

I am facing same problem.
Here is my problem:
I am using this gem with MySQL database. I have used devise for authentication. Once the user is deleted, it gives following error when same user try to signup.

Mysql2::Error: Duplicate entry 'pram@hotmail.com' for key 'index_users_on_email'

Or is there any ideas to resolve this issue?

[BenMorganIO]

@gkunwar You'll have to use partial indexes. Sadly, MySQL doesn't support them. You're SoL on that unless you move from MySQL to something like PostgreSQL. Then you can do:

# pg only
add_index :users, :email, unique: true, where: 'deleted_at IS NOT NULL`

[BenMorganIO]

@dmitrychopey see #333

[gkunwar]

Thank you @BenMorganIO for the response.

[chiragd]

Currently the validation for uniqueness runs on non deleted records.

Is it possible to tell the rails validation to run on all records in order to properly validate the uniqueness.

Some use cases allow for multiple entries of the same object as long as only one is active and the rest are deleted.

In my particular use case for email address I only ever want one record. If a user deleted a record and tries to add it again, I want the validation for uniqueness to fail so I can give them the option to restore the record. Any ideas please?

[rbr]

@gkunwar You could create an extra column is_deleted, storing if the user is deleted or not (boolean-wise) and then create a unique index on (email + is_deleted).

@chiragd That's exactly what this issue is about and what #333 tries to fix.

[rgo]

I solved it "undoing" what paranoia does(check this https://github.com/rubysherpas/paranoia/blob/core/lib/paranoia.rb#L299).

Briefly, Paranoia implements UniquenessParanoiaValidator and overwrites build_relation to add paranoia's scope. Then prepend its validator in ActiveRecord::Validations::UniquenessValidator overriding build_relation with its method(i.e. adding paranoia's scope)

What I have done is overwrite UniquenessParanoiaValidator#build_relation method again but this time with its original implementation.

Code:

module ActiveRecord
  module Validations
    module UniquenessParanoiaValidator
      def build_relation(klass, table, attribute, value)
        super(klass, table, attribute, value)
      end
    end
  end
end

I hope it helps, regards

[TheRealNeil]

I have been having a similar issue but I want to be able to selectively include deleted records. I have a working solution (I think) similar to the one above. I created a new validator in app/validators/uniqueness_with_deleted_validator.rb using the standard code from Active Record (Rails 4.2).

class UniquenessWithDeletedValidator < ActiveRecord::Validations::UniquenessValidator
  protected
  def build_relation(klass, table, attribute, value) #:nodoc:
    if reflection = klass._reflect_on_association(attribute)
      attribute = reflection.foreign_key
      value = value.attributes[reflection.klass.primary_key] unless value.nil?
    end

    attribute_name = attribute.to_s

    # the attribute may be an aliased attribute
    if klass.attribute_aliases[attribute_name]
      attribute = klass.attribute_aliases[attribute_name]
      attribute_name = attribute.to_s
    end

    column = klass.columns_hash[attribute_name]
    value  = klass.connection.type_cast(value, column)
    if value.is_a?(String) && column.limit
      value = value.to_s[0, column.limit]
    end

    if !options[:case_sensitive] && value && column.text?
      # will use SQL LOWER function before comparison, unless it detects a case insensitive collation
      klass.connection.case_insensitive_comparison(table, attribute, column, value)
    else
      klass.connection.case_sensitive_comparison(table, attribute, column, value)
    end
  end
end

Then in my model I can use something like
validates :book, presence: true, uniqueness_with_deleted: true
or
validates :book, presence: true, uniqueness_with_deleted: { scope: :author_id }

**UPDATE: This does not work in Rails 5.0, I am using the solution from @nghiabk 👍 **

[nghiabk]

I validated uniqueness email in my project when we used gem paranoia. Remember in gem paranoia, with_deleted is available
validates :email, uniqueness: {conditions: ->{with_deleted}}