How do I validate_uniqueness_of when scoped to two attributes only when one attribute on matching existing records does not equal a specific value?

Question

I have an Invitation model that represents an invitation to join a subscription. There should only be one Invitation at any given time with a specific email / subscription_id combination unless the other records with the matching email / subscription_id also have a state of 'declined'.

I can currently validate for uniqueness given that the email and subscription_id combination is unique:

My Invitation model:

validates :email, :uniqueness => { :scope => :subscription_id }

Rspec (passes):

it { should validate_uniqueness_of(:email).scoped_to(:subscription_id) }

However, I want to skip the uniqueness check if the matching model(s) in the database have a state that is equal to 'declined'.

If the existing model's state is 'declined', the validation should pass.

The first thing that comes to mind is:

validates :email, :uniqueness => { :scope => :subscription_id },
                                   :unless => lambda { |asset| asset.state == 'declined' }

But this is wrong because it checks if the newly created model has a state of 'declined', I want to check if the previously existing records have a state of 'declined'.

I also tried this:

validates :email, :uniqueness => { :scope => :subscription_id, :message => 'subscriptionery do' }, 
                                   :if => lambda { |asset| asset.state == 'declined' }

But that fails for what I assume is the same reason.

How would I write a validation that checks an additional scope?

---

I feel like writing something like the following, but this is just made up syntax to help explain my idea:

it { should validate_uniqueness_of(:email).scoped_to(:subscription_id) } 
                                   unless MyModel.where(:email == new_object.email,
                                                        :subscription_id == new_object.subscription_id,
                                                        :state == 'declined')

---

Update:

I did this and it worked:

validates :email, uniqueness: { scope: :subscription_id, message: 'The email address %{value} is already associated with this subscription.' }, if: :state_of_others_are_not_declined?, on: :create

def state_of_others_are_not_declined?
  Invitation.where(email: email).where(subscription_id: subscription_id).where.not(state: 'declined').any?
end

Answer 1

How does this work for you;

validate :unique_email_with_subscription_and_state


def unique_email_with_subscription_and_state
    errors.add(:email,"YOUR MESSAGE") if Invitation.where(email: self.email, subscription_id: self.subscription_id).where.not(state: 'declined').any?
end

This will select all Invitiations where the the email matches, subscription_id matches and the state is not declined. If it finds any it will add an error to :email. Something like this

"SELECT invitations.* FROM invitatations WHERE email = 'me@example.com' AND subscription_id = 2 AND state <> 'declined'"  

Is that the desired result?