Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

HABTM - uniqueness constraint

I have two models with a HABTM relationship - User and Role.

  • user - has_and_belongs_to_many :roles
  • role - belongs_to :user

I want to add a uniqueness constraint in the join (users_roles table) that says the user_id and role_id must be unique. In Rails, would look like:

validates_uniqueness_of :user, :scope => [:role]

Of course, in Rails, we don't usually have a model to represent the join relationship in a HABTM association.

So my question is where is the best place to add the constraint?

like image 674
keruilin Avatar asked Feb 14 '11 03:02

keruilin


4 Answers

You can add uniqueness to join table

add_index :users_roles, [ :user_id, :role_id ], :unique => true, :name => 'by_user_and_role'

see In a join table, what's the best workaround for Rails' absence of a composite key?

Your database will raise an exception then, which you have to handle.
I don't know any ready to use rails validation for this case, but you can add your own validation like this:

class User < ActiveRecord::Base
has_and_belongs_to_many :roles, :before_add => :validates_role

I would just silently drop the database call and report success.

def validates_role(role)
  raise ActiveRecord::Rollback if self.roles.include? role
end

ActiveRecord::Rollback is internally captured but not reraised.

Edit

Don't use the part where I'm adding custom validation. It kinda works but there is better alternatives.

Use :uniq option on association as @Spyros suggested in another answer:

class Parts < ActiveRecord::Base
  has_and_belongs_to_many :assemblies, :uniq => true, :read_only => true
end  

(this code snippet is from Rails Guides v.3). Read up on Rails Guides v 3.2.13 look for 4.4.2.19 :uniq

Rails Guide v.4 specifically warns against using include? for checking for uniqueness because of possible race conditions.

The part about adding an index to join table stays.

like image 57
Art Shayderov Avatar answered Nov 15 '22 07:11

Art Shayderov


In Rails 5 you'll want to use distinct instead of uniq

Also, try this for ensuring uniqueness

has_and_belongs_to_many :foos, -> { distinct } do
  def << (value)
    super value rescue ActiveRecord::RecordNotUnique
  end
end
like image 21
Caleb Avatar answered Nov 15 '22 06:11

Caleb


I think that using :uniq => true would ensure that you get no duplicate objects. But, if you want to check on whether a duplicate exists before writing a second one to your db, i would probably use find_or_create_by_name_and_description(...).

(Of course name and description are your column values)

like image 5
Spyros Avatar answered Nov 15 '22 06:11

Spyros


I prefer

class User < ActiveRecord::Base
  has_and_belongs_to_many :roles, -> { uniq }
end

other options reference here

like image 5
ken Avatar answered Nov 15 '22 05:11

ken