active record query depending on count of associated objects

Question

I have two models Company and Role related by has_many and belongs_to association respectively. I need to fetch a company that has exactly 'n' number of roles.

I came up with

Company.joins(:roles).having("'count(roles.id) = ?', 3")

but this does not work. Is there an active record solution to this ?

Thanks.

Answer 1

Associations -

role belongs_to :company

company has_many :roles

So, selection for criteria would be -

Company.joins(:roles).group(:company_id).having('count(company_id)= ?', 3)

OR

Company.joins(:roles).group(:company_id).having('count(*)= ?', 3)

Answer 2

I know this is not the answer you're looking for, but the best (in terms of performance) solution is to add a simple roles_count column to the companies table, and then add :counter_cache => true onto the belongs_to association declaration. Rails takes care of updating that column for you.

Then,

Company.where("roles_count = ?", 3)

More information: http://guides.rubyonrails.org/association_basics.html#belongs_to-counter_cache