CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
TomDogg
TomDogg

Reputation: 3940

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")

Upvotes: 13

Views: 11259

Answers (2)

Martin M
Martin M

Reputation: 8668

As Rails continues to develop, this can now also be formulated without explicit SQL.
Also, searching companies without users is easier with an outer join and a where on null user.id:

Company.left_outer_joins(:users).where(users: {id: nil})

Upvotes: 1

Raj
Raj

Reputation: 22956

Do a LEFT JOIN instead of INNER JOIN.

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

Upvotes: 13

Related Questions