(NULL) 中的 NULL 未正确匹配

Question

使用 Rails 3 主动关系，我有一个范围：

  scope :duplicate_contact, lambda {|contact| where(
                  :person_id          => contact.person_id,
                  :salutation         => contact.salutation,
                  :first_name         => contact.first_name,
                  :last_name          => contact.last_name,
                  :suffix             => contact.suffix,
                  :birthday           => contact.birthday,
                  :address            => contact.address,
                  :city               => contact.city,
                  :state              => contact.state,
                  :zip                => contact.zip,
                  :phone_1            => [contact.phone_1,contact.phone_2,contact.phone_3],
                  :phone_1_type       => [contact.phone_1_type,contact.phone_2_type,contact.phone_3_type],
                  :phone_2            => [contact.phone_1,contact.phone_2,contact.phone_3],
                  :phone_2_type       => [contact.phone_1_type,contact.phone_2_type,contact.phone_3_type],
                  :phone_3            => [contact.phone_1,contact.phone_2,contact.phone_3],
                  :phone_3_type       => [contact.phone_1_type,contact.phone_2_type,contact.phone_3_type],
                  :email              => [contact.email,contact.alternate_email],
                  :alternate_email    => [contact.email,contact.alternate_email]
            )
          }

当 :email 为 NULL 时，这会出现问题。它返回零行，而实际上它应该返回至少 1 行，即 duplicate_contact(contact).size == 0 为 true，而它应该为 false。

我认为这与 mysql 文档中的这句话有关：“在 SQL 中，与任何其他值（甚至 NULL）相比，NULL 值永远不是真的。”

我怎样才能让它返回正确的结果？

Answer 1

我发现的一种可能的解决方案：

 scope :duplicate_contact, lambda {|contact| 

    q = where(
      :person_id          => contact.person_id,
      :salutation         => contact.salutation,
      :first_name         => contact.first_name,
      :last_name          => contact.last_name,
      :suffix             => contact.suffix,
      :birthday           => contact.birthday,
      :address            => contact.address,
      :city               => contact.city,
      :state              => contact.state,
      :zip                => contact.zip
    )
    [contact.phone_1,contact.phone_2,contact.phone_3].compact.each{|p| q=q.has_phone(p)}
    [contact.phone_1_type,contact.phone_2_type,contact.phone_3_type].compact.each{|p| q=q.has_phone_type(p)}
    [contact.email,contact.alternate_email].compact.each{|p| q=q.has_email(p)}
    q
  }
  scope :has_phone, lambda {|phone|
    where("'#{phone}' IN (phone_1,phone_2,phone_3)")
  }
  scope :has_phone_type, lambda {|phone|
    where("'#{phone}' IN (phone_1_type,phone_2_type,phone_3_type)")
  }
  scope :has_email, lambda {|email|
    where("'#{email}' IN (email,alternate_email)")
  }