CODEWITHSUNDEEP
sqlruby-on-railsruby
Weston Ganger
Weston Ganger

Reputation: 6712

Rails - Find records with only one specific associated has_many or has_and_belongs_to_many record

I am wondering how to find records with only one specific has_and_belongs_to_many item.

The associations are as follows:

class User < ActiveRecord::Base
  has_and_belongs_to_many :roles
end

class Role < ActiveRecord::Base
  has_and_belongs_to_many :users
end

The following is what I normally use.

User.includes(:roles).where(roles: {name: 'guest'})

This is not sufficient because I want to find all users with only the role guest. It should not get records that have any other roles in addition to guest.

Can someone show me the way here? Ideally the solution would be compatible with all SQL database types (mysql, mariadb, postgres, sqlite)

Upvotes: 0

Views: 587

Answers (1)

CV-Gate
CV-Gate

Reputation: 1170

User.includes(:roles).where(roles: {name: 'guest'}).having("COUNT(roles.id)=1")

I also would recommend to use joinsinstead of includes if you don't need to eager load the query.

Upvotes: 1

Related Questions