# Find where association model count=0?

**URL:** <https://www.ruby-forum.com/t/find-where-association-model-count-0/171602>\
**Category:** Rails\
**Created:** [July 6, 2009, 3:26pm UTC](https://www.ruby-forum.com/t/find-where-association-model-count-0/171602 "2009-07-06T15:26:46Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![yannimac](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/y/7993a0/32.png) [@yannimac](https://www.ruby-forum.com/u/yannimac)\
**Post date:** [July 6, 2009, 3:26pm UTC](https://www.ruby-forum.com/t/find-where-association-model-count-0/171602/1 "2009-07-06T15:26:46Z")

</div>

I have a widget model that has\_many foos. I want to find a list of  
widgets that have no foos. Can I do this with a “find” method? Right  
now I am selecting all widgets and iterating through them to look at  
.count. I know there is a better way to do this, but I haven’t been  
able to figure it out with a single SQL query. Any ideas?

Thanks!

---

<div class="post-metadata">

**Author:** ![yannimac](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/y/7993a0/32.png) [@yannimac](https://www.ruby-forum.com/u/yannimac)\
**Post date:** [July 6, 2009, 3:35pm UTC](https://www.ruby-forum.com/t/find-where-association-model-count-0/171602/2 "2009-07-06T15:35:09Z")

</div>

I figured it out right after I posted 😉 Always happens like that…  
Here is the solution (or one of them)

@widgets = Widget.find(:all,:select=\>“widgets.\*,count(foos.id) as  
foos\_count”,:joins=\>“LEFT JOIN foos ON widgets.id =  
foos.widget\_id”,:group=\>“widgets.id”,:having=\>“foos\_count=0”)

Yanni M. wrote:

> I have a widget model that has\_many foos. I want to find a list of  
> widgets that have no foos. Can I do this with a “find” method? Right  
> now I am selecting all widgets and iterating through them to look at  
> .count. I know there is a better way to do this, but I haven’t been  
> able to figure it out with a single SQL query. Any ideas?
> 
> Thanks!

---

<div class="post-metadata">

**Author:** ![yannimac](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/y/7993a0/32.png) [@yannimac](https://www.ruby-forum.com/u/yannimac)\
**Post date:** [July 6, 2009, 4:10pm UTC](https://www.ruby-forum.com/t/find-where-association-model-count-0/171602/3 "2009-07-06T16:10:32Z")

</div>

On Jul 6, 2:35 pm, Yanni M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> I figured it out right after I posted 😉 Always happens like that…  
> Here is the solution (or one of them)
> 
> @widgets = Widget.find(:all,:select=\>“widgets.\*,count(foos.id) as  
> foos\_count”,:joins=\>“LEFT JOIN foos ON widgets.id =  
> foos.widget\_id”,:group=\>“widgets.id”,:having=\>“foos\_count=0”)

I think that

Widget.find(:all,:select=\>“widgets.\*”,:joins=\>"LEFT OUTER JOIN foos ON  
widgets.id =foos.widget\_id ",  
:conditions =\> “foos.id is NULL”)

might be faster (since the database can infer from that that you want  
widgets with no foos which is a bit less work than doing all the  
counting and then filtering that.

Fred