CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresqlactiverecord
mpartan
mpartan

Reputation: 1316

Rails 4 "where"-query with "has_many belongs_to"-relationship, searching with specific count

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

Upvotes: 3

Views: 1943

Answers (2)

mpartan
mpartan

Reputation: 1316

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

Upvotes: 1

xdazz
xdazz

Reputation: 160833

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

Upvotes: 7

Related Questions