使用不区分大小写的匹配检查多个字段中的重复值

Question

我有两个字段，name和email，它们的组合应该是唯一的，而不考虑大小写。下面是类和规范的代码。前3个测试通过了，但第四个没有通过。

class Person < ActiveRecord::Base
  validates :name, presence: true
  validates :email, presence: true

  validates_uniqueness_of :email, :scope => :name, :case_sensitive => false
  validates_uniqueness_of :name, :scope => :email, :case_sensitive => false
end

describe Person do
  context "with duplicate name and email" do
    before do
      @person1 = create(:person)
    end
    it "for case-sensitive match of both" do
      expect(build(:person, {name: @person1.name, email: @person1.email})).to_not be_valid
    end
    it "for case-insensitive match of name" do
      expect(build(:person, {name: @person1.name.swapcase, email: @person1.email})).to_not be_valid
    end
    it "for case-insensitive match of email" do
      expect(build(:person, {name: @person1.name, email: @person1.email.swapcase})).to_not be_valid
    end
    it "for case-insensitive match of both" do
      expect(build(:person, {name: @person1.name.swapcase, email: @person1.email.swapcase})).to_not be_valid
    end
  end
end

Answer 1

我尝试使用以下方法来解决您遇到的问题：

class Person < ActiveRecord::Base
  validates :name,  presence: true 
  validates :email, presence: true

  validates_uniqueness_of :name,  :case_sensitive => false, :scope => [ :email ]
  validates_uniqueness_of :email, :case_sensitive => false, :scope => [ :name ]
end

和

require 'spec_helper'

describe Person do
  context 'with duplicate name and email' do
    before do
      @person1 = Person.new(name: 'test@example.com', email: 'TEST@EXAMPLE.COM')
    end
    subject { @person1 }
    it { should be_valid }
    describe '(I) for case-sensitive match of both' do
      before do
        person = @person1.dup
        person.save
      end
      it { should_not be_valid }
    end
    describe  '(II) for case-insensitive match of name' do
      before do
        person = @person1.dup
        person.name.swapcase!
        person.save
      end
      it { should_not be_valid }
    end
    describe  '(III) for case-insensitive match of email' do
      before do
        person = @person1.dup
        person.email.swapcase!
        person.save
      end
      it { should_not be_valid }
    end
    describe '(IV) for case-insensitive match of both' do
      before do
        person = @person1.dup
        person.name.swapcase!
        person.email.swapcase!
        person.save
      end
      it { should_not be_valid }
    end
  end
end

从我的示例案例日志中可以看到，case_sensitive的行为有些奇怪：

(0.4ms)  SAVEPOINT active_record_1
Person Exists (1.2ms)  SELECT 1 AS one FROM "people" WHERE (LOWER("people"."name") = LOWER('TEST@EXAMPLE.COM') AND "people"."email" = 'test@example.com') LIMIT 1
Person Exists (0.5ms)  SELECT 1 AS one FROM "people" WHERE (LOWER("people"."email") = LOWER('test@example.com') AND "people"."name" = 'TEST@EXAMPLE.COM') LIMIT 1
SQL (3.7ms)  INSERT INTO "people" ("created_at", "email", "name", "updated_at") VALUES ($1, $2, $3, $4) RETURNING "id"  [["created_at", Sat, 07 Sep 2013 14:21:05 UTC +00:00], ["email", "test@example.com"], ["name", "TEST@EXAMPLE.COM"], ["updated_at", Sat, 07 Sep 2013 14:21:05 UTC +00:00]]
(0.2ms)  RELEASE SAVEPOINT active_record_1
Person Exists (0.3ms)  SELECT 1 AS one FROM "people" WHERE (LOWER("people"."name") = LOWER('test@example.com') AND "people"."email" = 'TEST@EXAMPLE.COM') LIMIT 1
Person Exists (0.3ms)  SELECT 1 AS one FROM "people" WHERE (LOWER("people"."email") = LOWER('TEST@EXAMPLE.COM') AND "people"."name" = 'test@example.com') LIMIT 1
(0.2ms)  ROLLBACK

问题似乎是“较低”的语句只在电子邮件或姓名上使用，而不是同时在两者上使用。基本上，我希望你的代码能正常工作。

然而，正如在db-log中看到的并且在另一个问题(Rails 3. Validating email uniqueness and case sensitive fails)中指出的，当性能是问题时，在约束中确保不区分大小写的行为可能不是一个好主意;-)而是使用之前的过滤器来以小写保存电子邮件/名称。因为这可能也不是最好的想法(因为您很可能希望不丢失名称的大小写信息)，您可以使用另一个小写的名称列来确保约束，或者相应地使用after_valition过滤器。

使用下面的模型应该会让你的测试套件变绿：

class Person < ActiveRecord::Base
  validates :name,  presence: true
  validates :email, presence: true

  before_validation :downcase_name_email

  validates_uniqueness_of :name,  :case_sensitive => false, :scope => [ :email ]
  validates_uniqueness_of :email, :case_sensitive => false, :scope => [ :name ]

  private

    def downcase_name_email
      self.email = self.email.downcase if self.email.present?
      self.name = self.name.downcase if self.name.present?
    end

end

最好的，本。

附注:如果您要采用小写方式，请确保迁移您的db-data：

Person.update_all('email = LOWER(email)')
Person.update_all('name = LOWER(name)')