Validates uniqueness two columns both ways?

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:

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

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?

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

On Mar 10, 7:26pm, Martin S. [email protected]

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

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

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

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

On Mar 11, 5:08am, “Robert Pankowecki (rupert)”