Rails - Jul 2009 - find where association model count=0 ?

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!
-- 
Posted via http://www.ruby-forum.com/.

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 Mac 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!
-- 
Posted via http://www.ruby-forum.com/.

On Jul 6, 2:35 pm, Yanni Mac
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
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
>
> Yanni Mac 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!
>
> --
> Posted viahttp://www.ruby-forum.com/.