How to model this complex validation for uniqueness on combined fields

Question

A link has two components: componenta_id and componentb_id. To this end, in the Link model file I have:

belongs_to :componenta, class_name: "Component"
belongs_to :componentb, class_name: "Component"

validates :componenta_id, presence: true
validates :componentb_id, presence: true
validates :componenta_id, uniqueness: { scope: :componentb_id }
validates :componentb_id, uniqueness: { scope: :componenta_id }

And in the migration file:

create_table :links do |t|
  t.integer  :componenta_id, null: false 
  t.integer  :componentb_id, null: false
  ...
end
add_index :links, :componenta_id
add_index :links, :componentb_id
add_index :links, [:componenta_id, :componentb_id], unique: true

Question: This all works. Now I want the combination of componanta and componentb to be unique, irrespective their order. So irrespective which component is componenta and which one is componentb (after all that's the same link; a link between the two same components). So the two records below should not be allowed since they represent the same link and thus are not unique:

componenta_id = 1 ; componentb_id = 2
componenta_id = 2 ; componentb_id = 1

How can I create this uniqueness validation? I have model validation working (see below) but wonder whether and how I should also add validation at the migration/db level...?

Model validation
I have model validation working with the code below:

before_save :order_links
validates :componenta_id, uniqueness: { scope: :componentb_id }

private
  def order_links
    if componenta_id > componentb_id
      compb = componentb_id
      compa = componenta_id
      self.componenta_id = compb
      self.componentb_id = compa
    end
  end

The following test confirms the above works:

  1. test "combination of two links should be unique" do
  2.   assert @link1.valid?
  3.   assert @link2.valid?
  4.   @link1.componenta_id = 3     #@link2 already has combination 3-4
  5.   @link1.componentb_id = 4
  6.   assert_not @link1.valid?
  7.   @link1.componenta_id = 4
  8.   @link1.componentb_id = 3
  9.   assert_raises ActiveRecord::RecordNotUnique do
  10.    @link1.save
  11.  end
  12.end

Migration/db validation:
As an extra level of security, is there also a way to incorporate validation for this at the db level? Otherwise it is still possible to write both of the following records to the database: componenta_id = 1 ; componentb_id = 2 as well as componenta_id = 2 ; componentb_id = 1.

In [this conversation](http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql) the suggestion is to create a many-to-many relation: `has many :components` `validates_length_of :components,maximum:2` — skahlert, Jan 04 '16 at 21:06

score 4 · Answer 1 · answered Jan 05 '16 at 07:37

4

Perhaps it is possible to control the creation of the links with:

def create_unique_link( comp_1, comp_2 )
  if comp_1.id > comp_2.id
    first_component = comp_1
    second_component = comp_2
  end
  link = Link.find_or_create_by( componenta_id: first_comp.id, componentb_id: second_comp.id )
end

If you need the validation, then you can custom validate:

def ensure_uniqueness_of_link
  if comp_1.id > comp_2.id
    first_component = comp_1
    second_component = comp_2
  end

  if Link.where( componenta_id: first_component.id, componentb_id: second_component ).first
    errors.add( :link, 'Links should be unique' )
  end

end

answered Jan 05 '16 at 07:37

roob

1,116
7
11

But that's all model validation, right? For model validation the code as I have it works. It is validation at the db level, so I mean in the migration file, that is still missing. – Nick Jan 05 '16 at 10:30
I can't think of a generic way at the database level. Perhaps use some form of database trigger to prevent it? There could also be database vendor specific methods. – roob Jan 05 '16 at 12:15

Richard Peck · Answer 2 · 2015-12-29T12:23:23.163

2

 validates :componenta_id, uniqueness: { scope: :componentb_id }
 validates :componentb_id, uniqueness: { scope: :componenta_id }

edited Dec 29 '15 at 12:23

answered Dec 29 '15 at 12:18

Richard Peck

76,116
9
93
147

Thanks! but wouldn't this still allow for a combination of 1-2 as well as 2-1. Meaning one time where component with id 1 is `componenta` and the component with id 2 is `componentb`, and one time the other way around? This is what I want to prevent. The combination 1-2 should be unique, irrespective which one of the two is `componenta` and which is `componentb`. – Nick Dec 29 '15 at 12:21
Yep it will which is why I was going to delete the answer; I thought I'd leave it unless it got voted down :D – Richard Peck Dec 29 '15 at 12:23
Why not try the above? Not super clever, but might get the result you need – Richard Peck Dec 29 '15 at 12:23
Thanks, I've tried it. But unfortunately the test added to the post fails on line 9. – Nick Dec 29 '15 at 17:16

How to model this complex validation for uniqueness on combined fields

2 Answers2