Unique Validation on one column and conditional on second column based on row value

Question

I have a database in which email is set as unique validation with status. I have set scope as a int 'status' which is 1 when particular account is active. So if user switches his country, his account status is switched to 0 and a new account is created with same email and status is set as 1. So to solve this I have used a composite unique key on 'email' and 'status' as,

validates :email, uniqueness: { scope: :status }

So, let's have a small dry run. currently, user is in X country and his email is [email protected] so database may look like

email: [email protected]
status: 1

Now if he goes to country Y database updates as,

[for company X]
email: [email protected]
status: 0
[for company Y]
email:[email protected]
status: 1

works fine right but now if our Traveler goes to Country Z now database will stuck as now for company Y status will update to 0 and as now this combination is not unique so will return error. Also the challenging task here to only use email and status as the keys. What I want to achieve is that for a particular email status:1 must be unique while status:0 can be any number of times. So I was trying to condition the statement like

validates :email, uniqueness: { scope: :status }, if: :status == 1

but no luck as I cannot take status value of particular user in model. Thanks, in advance!! PS: I am new to ROR, so do provide relevant links so I can learn more xD.

Answer 1

You might want to rethink your database structure.

But one of the solutions is to do:

validates_uniqueness_of :email, scope: :status, if: :active?

def active?
  status == 1
end

Check the api for this validation here.

But I strongly suggest you redo the database structure to prevent this from happening on the database-level. Something like:

inactive_emails table
email              |    company
--------------------------------
[email protected]    |     A
[email protected]    |     B
[email protected]    |     C
[email protected]    |     A


active_emails table
email              |    company
--------------------------------
[email protected]    |      D
[email protected]    |      E

For the inactive emails model, you could have:

class InactiveEmails
  validates_uniqueness_of :email, scope: :company
end

And for the active emails model:

class ActiveEmails
  validates_uniqueness_of :emails, scope: :company
end

This way, you ensure that each company has unique active emails and each email can only be active for one company at a time.

Now it's up to you to switch emails between the two tables. You could use a callback for example:

before_save :check_if_email_is_inactive_for_company

def check_if_email_is_inactive_for_company
  if InactiveEmail.where(email: email, company: company).exists?
    # remove from inactive? or inform user this email was deactivated before?
  end
end

Rails API page is your friend.

Answer 2

You can solve your problem in many ways but I will tell 2 ways

1. You can apply scope as follows

   validates :email, uniqueness: { scope: [:status,:country]}  
   

2. If you want to handle it with scope on single field

   validates :email, uniqueness: { scope: :status }, unless: lambda{ |user| user.status == 0 }
   

If user id is x, the above validation allows user id: x, status: 0 for multiple times, but you can have only one user_id: x, status 1 combination.