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

Question

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 1

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.

Sign up