3

This should be a simple query, but I'm having problems getting the Rails syntax right. I'm using Rails 4.1.1 and Postgresql(9.3). I have a model User, and model Company. User has one company, and Company has many users. I'm trying to find all companies that have more than 5 users. 

class Company < ActiveRecord::Base
   has_many :users, dependent: :destroy
   ...

class User < ActiveRecord::Base
   belongs_to :company
   ...

Question is similar to this: Find all records which have a count of an association greater than zero

If I try similar solution as mentioned above:

Company.joins(:users).group("company.id").having("count(users.id)>5")

It gives me an error:

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "company"
LINE 1: ... "users"."company_id" = "companies"."id" GROUP BY company.id...

I've tried several different queries for getting the result, but I've failed to do so. I could use SQL, but it seems dumb as this should be doable easily with ActiveRecord.

Thanks for all the replies :)

Community
  • 1
  • 1
mpartan
  • 1,296
  • 1
  • 14
  • 30

2 Answers2

7

Should use "companies.id" instead of "company.id".

Company.joins(:users).group("companies.id").having("count(users.id)>5")

And if you want to get company with 0 users, you have to use LEFT JOIN:

Company.joins('LEFT JOIN users ON companies.id = users.company_id').group("companies.id").having("count(users.id) = 0")
xdazz
  • 158,678
  • 38
  • 247
  • 274
  • I swear I tried with that as well, I guess not... Thanks a lot :) Somehow it's not working if there I'm trying a query with 0 users, with the query being like: Company.joins(:users).group("companies.id").having("count(users.id)=0") There's a company with 0 users, but it is still returning empty => # Is there a different way it's supposed to be done, if trying to search with 0 users? – mpartan Sep 04 '14 at 12:22
  • I wonder why this query returns a company with 8 users? 2.1.2p95 :115 > Company.joins('LEFT JOIN users ON companies.id = users.id').group("companies.id").having("count(users.id) = 0").first.users.count => 8 – mpartan Sep 04 '14 at 12:39
  • instead of `LEFT JOIN` which I think looks ugly in query syntax why not just use `includes(:users)` this will create a `LEFT JOIN` for you and looks much cleaner. – engineersmnky Sep 04 '14 at 13:14
  • @MatiasP Sorry, have miss in join condition, now it should work. – xdazz Sep 04 '14 at 14:00
1

The query xdazz provided works well for when I'm trying to look for companies that have more than 0 users (basicly what I asked in initial post). I found two ways to do the search 0 users. One is the way noted above: 

Company.joins('LEFT JOIN users ON companies.id = users.company_id')
.group("companies.id").having("count(users.id) = 0")

However with help of Want to find records with no associated records in Rails 3 this is another way to do it:

Company.includes(:users).where(:users => {company_id=>nil})
Community
  • 1
  • 1
mpartan
  • 1,296
  • 1
  • 14
  • 30
  • `Company.joins('LEFT JOIN users ON companies.id = users.id')` should be `Company.joins('LEFT JOIN users ON companies.id = users.company_id') ` – xdazz Sep 04 '14 at 14:00
  • Right, I didn't pay proper attention there to notice it. I'll edit the post to show both ways. – mpartan Sep 04 '14 at 15:17