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!

I figured it out right after I posted :wink: 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!

On Jul 6, 2:35 pm, Yanni M. [email protected] wrote:

I figured it out right after I posted :wink: 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

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