Model.find(:all) where Model.association has more then 0 ite

Hi everyone,

I have a Department model that has_many people. How do I go about
finding all departments with more than 0 people?

Department.find(:all, :conditions => Department.people.size > 0)

That, of course, doesn’t work, but it was as close as I could come.

Thanks!

Sean

Oh, also, is counter_cache a good alternative to this? I’m caching
the piece of the view that would use this query, and that cache won’t
be expired very often (hires, terminations, transfers), so I think I’m
ok with the associated ‘select count(*)’ that comes with people.size.

Sean

I have a Department model that has_many people. How do I go about
finding all departments with more than 0 people?

I’d try using the “find_by_sql” function with a raw SQL query. It
could look something like this (with MySQL):

Department.find_by_sql(“SELECT departments.*, COUNT(people.id) AS
people_count FROM departments LEFT JOIN people ON
department_id=departments.id GROUP BY departments.id HAVING
people_count > 0”)

Also, when retrieving the departments with this query, you can access
the people_count result just like any other column:

department.people_count

I’d imagine counter_cache would work fine too, and would probably be
faster, but I’ve never tried it.

Hope that helps,

Ryan

Sean H. wrote:

I have a Department model that has_many people. How do I go about
finding all departments with more than 0 people?

Department.find(:all, :conditions => Department.people.size > 0)

That, of course, doesn’t work, but it was as close as I could come.

Assuming that you actually need to display info about the people in each
department, the easiest way to do this is probably:

Department.find(:all, :include => :people,
:conditions => “people.department_id is not NULL”)

If you don’t need to return the data from the people association, but
still want
to find all the departments with people in it, you can do:

Department.find(:all, :conditions => “people.department_id is not NULL”,
:joins => “left join people on people.department_id =
departments.id”)

Haven’t tested that one, but it should do the same as the above, but not
bring
back any columns from the people table.

-Brian

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs