Using Rails 3 active relation, I have a scope:
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]
)
}
This has a problem when :email is NULL. It returns back zero rows, when in fact it should return at least 1 row, ie duplicate_contact(contact).size == 0
is true when it should be false.
I think this has to do with this staement from the mysql docs: "In SQL, the NULL value is never true in comparison to any other value, even NULL."
How can I get this to return the correct result?
One possible solution that I found:
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)")
}