Validates uniqueness two columns both ways?

rubyonrails-talk
1

Hello!

I'm trying to figure out how to make two columns unique regardless of order. If order mattered, I could scope the validates_uniqueness_of, like this: ruby on rails - How do I validate two fields for uniqueness - Stack Overflow

But that's only one way. What if I have two columns, called "sender" and "receiver" and it doesn't really matter who is the sender and who is the receiver as long as that "grouping" doesn't exist previously.

For example, assume I have this already in the database: Sender = 1, Receiver = 2

Then, the following should occur: Sender = 1, Receiver = 3 # => PASS Sender = 1, Receiver = 2 # => FAIL Sender = 2, Receiver = 1 # => FAIL Sender = 3, Receiver = 1 # => FAIL (if the first one that passed already happened)

I tried this:

validates_uniqueness_of :sender_id, :scope => :receiver_id validates_uniqueness_of :receiver_id, :scope => :sender_id

But that doesn't quite do it because they aren't tied together. I can custom-validate this but I was wondering if this pattern has already been accounted for somehow.

Any pointers/references?

Thanks!

-Danimal

2

Two ideas:

1. Create two unique database indices, one each for each pairing order and catch the exception on save on create.

2. Do it manually.

errors.add(:base, 'already exists') if (Klass.where(:sender_id => sender.id, :receiver_id => receiver.id) + Klass.where(:sender_id => receiver.id, :receiver_id => sender.id)).any?

3

Thanks, Martin!

I've headed down path #2, but I thought of posting here just in case there already existed a custom validation. Maybe this isn't a very common thing. Seems to be that usually it's explicit in the direction (i.e. "husband => wife" relationship or "employer" => "employee" or something like that).

-Danimal

4

You could alway store them in known order so that sender.id <= reciver.id and use scoped validates_uniqueness_of.

Assuming that sender and reciver can be exchanged in this relationship and does not have any additional meaning.

Like 'friendship' when it does not matter who is friend A and who is friend B

Robert Pankowecki

5

Hi Robert! Thanks for the post.

Your thought would work, except that it's very much like 'friendship' in that it's immaterial who requested the 'friendship'. And that's the crux of it. Since Person 1 or Person 2 could be the sender/requester, it has to check both ways.

Then again, I just had a thought. Perhaps given two people, the lower ID is always set as the sender and the other as the receiver. Hmmm... then, the order would always be known.

If Person 2 requests friendship with Person 1, the friendship object is saved with sender=>1, receiver=>2 Then, if later Person 1 requests friendship with Person 2, it's saved as sender=>1, receiver=>2 and it would fail validation.

Interesting... I need to noodle on that a bit more and see if that would work thoroughly.

Thanks!

-Danimal

6

Of course, on further thought,

The downside to this ID reordering is that I'd lose the data about who did the request.

For the 'friendship', it doesn't matter. I.e. Person 1 is connected to Person 2 is exactly the same functionally as Person 2 is connected to Person 1 (in my app, that is). But there may be value in knowing which person started the process (i.e. the "sender").

Hmmm... I guess a custom validation it is.

-Danimal

7

How about a little trick. Make Id column of the table a string and then:

class Friendship < ActiveRecord::Base

belongs_to :sender, ... belongs_to :requester, ...

before_validation :standarize_id, :on => :create validates_uniqueness_of :id

def standarize_id   self.id = [sender.id, requester.id].sort.join(",") end

end

This way we don't forget the information about whose is the sender and who is requester. Instead we relay on standarized unique id for the the couple.

Robert Pankowecki