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

**URL:** <https://www.ruby-forum.com/t/model-find-all-where-model-association-has-more-then-0-ite/56383>\
**Category:** Rails\
**Created:** [March 30, 2006, 7:26pm UTC](https://www.ruby-forum.com/t/model-find-all-where-model-association-has-more-then-0-ite/56383 "2006-03-30T19:26:31Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![seanhussey](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/a88e57/32.png) [@seanhussey](https://www.ruby-forum.com/u/seanhussey)\
**Post date:** [March 30, 2006, 7:26pm UTC](https://www.ruby-forum.com/t/model-find-all-where-model-association-has-more-then-0-ite/56383/1 "2006-03-30T19:26:31Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![seanhussey](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/a88e57/32.png) [@seanhussey](https://www.ruby-forum.com/u/seanhussey)\
**Post date:** [March 30, 2006, 7:38pm UTC](https://www.ruby-forum.com/t/model-find-all-where-model-association-has-more-then-0-ite/56383/2 "2006-03-30T19:38:46Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![seanhussey](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/a88e57/32.png) [@seanhussey](https://www.ruby-forum.com/u/seanhussey)\
**Post date:** [March 31, 2006, 2:52am UTC](https://www.ruby-forum.com/t/model-find-all-where-model-association-has-more-then-0-ite/56383/3 "2006-03-31T02:52:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![seanhussey](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/a88e57/32.png) [@seanhussey](https://www.ruby-forum.com/u/seanhussey)\
**Post date:** [March 31, 2006, 4:50am UTC](https://www.ruby-forum.com/t/model-find-all-where-model-association-has-more-then-0-ite/56383/4 "2006-03-31T04:50:44Z")

</div>

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