find where association model count=0 ?

775 views
Skip to first unread message

Yanni Mac

unread,
Jul 6, 2009, 9:26:46 AM7/6/09
to rubyonra...@googlegroups.com
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/.

Yanni Mac

unread,
Jul 6, 2009, 9:35:09 AM7/6/09
to rubyonra...@googlegroups.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")

Frederick Cheung

unread,
Jul 6, 2009, 10:09:36 AM7/6/09
to Ruby on Rails: Talk


On Jul 6, 2:35 pm, Yanni Mac <rails-mailing-l...@andreas-s.net> 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
Reply all
Reply to author
Forward
0 new messages