Rails - Mar 2006 - Model.find(:all) where Model.association has more then 0 items?

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

On 3/30/06, Sean Hussey <seanhussey@gmail.com>
wrote:
> 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
>

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

On Mar 30, 2006, at 9:38 AM, Sean Hussey wrote:
> 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
>
> On 3/30/06, Sean Hussey <seanhussey@gmail.com> wrote:
>> 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
>>

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