Rails group/having/count query

Question

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

Raj · Accepted Answer · 2014-05-06 11:27:17Z

13

Do a LEFT JOIN instead of INNER JOIN.

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

answered May 6, 2014 at 11:27

Raj

23.1k14 gold badges105 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

miguelfg Over a year ago

I think this could be simplified by using 'includes' instead of 'joins'. Like: Company.includes(:users).group("companies.id").having("COUNT(users.id)=0")

TomDogg Over a year ago

@miguelfg : That was my initial thought as well. But no, this does not work. The error you get with this is: Unknown column 'users.id' in 'having clause'

Martin M · Accepted Answer · 2023-12-13 13:39:24Z

1

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

answered Dec 13, 2023 at 13:39

Martin M

8,6883 gold badges40 silver badges57 bronze badges

Collectives™ on Stack Overflow

Rails group/having/count query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related