Sequel unique validation across multiple columns

Question

I'm trying to add a validates_unique validation using the Sequel gem and a MySQL database.

I'd like it to fail validation if the value of column2 matches an existing value in column3, so something like the following (non-working) example:

validates_unique(:email_address){|ds| ds.where('unconfirmed_email NOT LIKE confirmed_email')}

Here's a sqlfiddle where I've used this query for the desired results:

SELECT DISTINCT value
FROM (
  SELECT DISTINCT confirmed_email AS value FROM email_addresses
  UNION SELECT DISTINCT unconfirmed_email AS value FROM email_addresses
) AS derived

I'm struggling with mapping it to the sequel validation. Any guidance is appreciated.

Answer 1

Adding a custom validation instead of validates_unique resolved the issue.

errors.add(:unconfirmed_email, 'email in use') if email_in_use?(unconfirmed_email)

And a private method

def email_in_use?(email)
  EmailAddress.where(confirmed_email: email).first ? true : false
end

Answer 2

Index column3 and just query your table for whatever value from column2 that you're validating against. If your query produces a record then your validation should fail.