CODEWITHSUNDEEP
ruby-on-railsactiverecord
brad
brad

Reputation: 9773

How do I select all records with more than n child records

For the situation where Employee has_many Clients, I am trying to write an ActiveRecord query that will return all of the Employees that have n or more clients. It's simple enough to write a join query to find all of the Employees with at least 1 client but extending my query to this more general case has left me stumped.

Edit - I should add that I'm trying to do this entirely at the database level. I want to avoid iterating over the collection in Ruby.

Upvotes: 11

Views: 4573

Answers (3)

vijikumar
vijikumar

Reputation: 1815

@employees=Employee.select{|e|e.clients.length > n }

Active record query for the same..

@employees=Employee.find(:all, :joins=>"as emp inner join clients as c on c.employee_id=emp.id", :select=>"emp.id", :group=>'emp.id having count(c) > n')

it will give the employee id's who are all having more than n clients.

Upvotes: 4

brad
brad

Reputation: 9773

Thanks to mu and vijikumar, this is what I've come up with

Employee.select("employees.*").joins(:clients).group("employees.id").having("count(clients.id) > ?", n)

Upvotes: 26

Narmadha
Narmadha

Reputation: 55

try this,

@emp_client = []
@emp = Employee.all
@emp.each do |e|
  if @emp.clients.present?
    @emp_clinet << @emp
  end
end

Upvotes: -2

Related Questions