WestonGanger WestonGanger - 10 months ago 80
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


The associations are as follows:

class User < ActiveRecord::Base
has_and_belongs_to_many :roles

class Role < ActiveRecord::Base
has_and_belongs_to_many :users

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
. It should not get records that have any other roles in addition to

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

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