WestonGanger WestonGanger - 25 days ago 13
Ruby Question

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)

Answer
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.

Comments