Rails 4 "where" - query with relation "has_many belongs to_to", search by specific account
This should be a simple request, but I'm having trouble getting the Rails syntax correct. I am using Rails 4.1.1 and Postgresql (9.3). I have a User model and a Company model. The user has one company and the Company has many users. I am 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
...
The question is similar to this: Find all records that have a counter greater than zero
If I try a similar solution as above:
Company.joins(:users).group("company.id").having("count(users.id)>5")
This 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 tried several different queries to get the result, but I didn't. I could use SQL, but it seems silly as it is easy to do with ActiveRecord.
Thanks for all the answers :)
source to share
Should use "companies.id"
instead "company.id"
.
Company.joins(:users).group("companies.id").having("count(users.id)>5")
And if you want to get a company with 0 users, you should use LEFT JOIN
:
Company.joins('LEFT JOIN users ON companies.id = users.company_id').group("companies.id").having("count(users.id) = 0")
source to share
The xdazz query has provided a good job when I am trying to search for companies with more than 0 users (basically what I asked in the initial post). I found two ways to make the search by users 0. One of them is noted above:
Company.joins('LEFT JOIN users ON companies.id = users.company_id')
.group("companies.id").having("count(users.id) = 0")
However, using Want to find records with no related records in Rails 3 , this is another way to do it:
Company.includes(:users).where(:users => {company_id=>nil})
source to share