Search code examples
sqlruby-on-railsactiverecord

Rails group/having/count query

Given a situation like: Company has_many Users

To get the Companies that have 3 Users, this works efficiently:

Company.joins(:users).group("companies.id").having("COUNT(users.id)=3")

But what's the most efficient way to get the Companies that have 0 Users (none)? Because, obviously, the same approach would not work (as joins by definition excludes Companies with 0 Users):

Company.joins(:users).group("companies.id").having("COUNT(users.id)=0")
Solution

  • Do a LEFT JOIN instead of INNER JOIN.

    Company.joins('LEFT OUTER JOIN users ON companies.id = users.company_id')