# Complex find **URL:** \ **Category:** Rails\ **Created:** [December 2, 2009, 2:29am UTC](https://www.ruby-forum.com/t/complex-find/179687 "2009-12-02T02:29:32Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![aftershock](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/8e7dd6/32.png) [@aftershock](https://www.ruby-forum.com/u/aftershock)\ **Post date:** [December 2, 2009, 2:29am UTC](https://www.ruby-forum.com/t/complex-find/179687/1 "2009-12-02T02:29:32Z")
I have the following models set up. Plugin has\_many :site\_plugins has\_many :sites, :through =\> :site\_plugins SitePlugin belongs\_to :plugin belongs\_to :site Site has\_many :site\_plugins has\_many :plugins, :through =\> :site\_plugins What I need to do is get a list of all plugins that are not installed on a site. Sounds simple enough but I’m tearing my hair out over this The closest I got to getting a find working is class Plugin \< … def self.available\_for\_site(site\_id) find :all, :include =\> :sites, :conditions =\> [‘plugins.available = ? and (sites.id IS NULL)’, true] end This gets me all plugins that have not been installed on any site which is not what I want at all I need all plugins that have not been installed on site\_id I think I need a sub query and a count to select site\_plugins where site\_plugins.site\_id = site\_id with a count but I just can’t get my head round this what should be very simple requirement. any help at all would be greatly appreciated ---
**Author:** ![aftershock](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/8e7dd6/32.png) [@aftershock](https://www.ruby-forum.com/u/aftershock)\ **Post date:** [December 3, 2009, 9:37am UTC](https://www.ruby-forum.com/t/complex-find/179687/2 "2009-12-03T09:37:26Z")
Hi James W. What I understood is suppose there is a site1 which has say 2 plugins You have to find out the remaing three from a total of 5.If that is site1 = Site.find 1 Plugin.all - site1.plugins This gives all plugins which site does not have Sijo ---
**Author:** ![aftershock](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/8e7dd6/32.png) [@aftershock](https://www.ruby-forum.com/u/aftershock)\ **Post date:** [December 3, 2009, 10:33am UTC](https://www.ruby-forum.com/t/complex-find/179687/3 "2009-12-03T10:33:12Z")
Sijo k g wrote: > Hi James W. > > What I understood is suppose there is a site1 which has say 2 plugins > You have to find out the remaing three from a total of 5.If that is > > site1 = Site.find 1 > Plugin.all - site1.plugins > > This gives all plugins which site does not have > > Sijo Awesome, Thankyou. Is there a way of doing that in 1 SQL statement? ---
**Author:** ![aftershock](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/8e7dd6/32.png) [@aftershock](https://www.ruby-forum.com/u/aftershock)\ **Post date:** [December 3, 2009, 3:09pm UTC](https://www.ruby-forum.com/t/complex-find/179687/4 "2009-12-03T15:09:37Z")
James W. wrote: > Sijo k g wrote: > > > Hi James W. > > > > What I understood is suppose there is a site1 which has say 2 plugins > > You have to find out the remaing three from a total of 5.If that is > > > > site1 = Site.find 1 > > Plugin.all - site1.plugins > > > > This gives all plugins which site does not have > > > > Sijo > > Awesome, > Thankyou. > Is there a way of doing that in 1 SQL statement? How about Plugin.find :all, :joins =\> ‘left join site\_plugins on site\_plugins.plugin\_id = plugins.id’, :conditions =\> {:plugin\_id =\> nil} ? Best, – Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid ---
**Author:** ![aftershock](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/8e7dd6/32.png) [@aftershock](https://www.ruby-forum.com/u/aftershock)\ **Post date:** [December 3, 2009, 9:33pm UTC](https://www.ruby-forum.com/t/complex-find/179687/5 "2009-12-03T21:33:57Z")
Marnen Laibow-Koser wrote: > James W. wrote: > > > Sijo k g wrote: > > > > > Hi James W. > > > > > > What I understood is suppose there is a site1 which has say 2 plugins > > > You have to find out the remaing three from a total of 5.If that is > > > > > > site1 = Site.find 1 > > > Plugin.all - site1.plugins > > > > > > This gives all plugins which site does not have > > > > > > Sijo > > > > Awesome, > > Thankyou. > > Is there a way of doing that in 1 SQL statement? > > How about Plugin.find :all, :joins =\> ‘left join site\_plugins on > site\_plugins.plugin\_id = plugins.id’, :conditions =\> {:plugin\_id =\> nil} > ? > > Best, > – > Marnen Laibow-Koser > [http://www.marnen.org](http://www.marnen.org) > removed\_email\_address@domain.invalid Marnen, Thank you for your reply, That’s nearly there but it’s not quite right. It doesn’t cater for a site\_plugin that has been installed on a different site. I’m guessing that a sub query might do the trick but then tht would be the same as and possibly less effecient than Sito’s solution. I’m using the solution supplied by Sito to great effect and there will never be a large result set for this particular query so performance is less of an issue but it’s still an issue all the same as the solution is an ActiveResource solution and the site that is returning the query is under quite a heavy load. I think I might hit some SQL forums on this one. Cheers, James ---
**Author:** ![aftershock](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/8e7dd6/32.png) [@aftershock](https://www.ruby-forum.com/u/aftershock)\ **Post date:** [December 3, 2009, 10:16pm UTC](https://www.ruby-forum.com/t/complex-find/179687/6 "2009-12-03T22:16:06Z")
James W. wrote: […] > Marnen, > Thank you for your reply, > That’s nearly there but it’s not quite right. It doesn’t cater for a > site\_plugin that has been installed on a different site. Sorry, I misunderstood your requirements. I think Robert’s idea is what you want. You should be able to use my example to turn it into find arguments. > I’m guessing that a sub query might do the trick but then tht would be > the same as and possibly less effecient than Sito’s solution. > I’m using the solution supplied by Sito to great effect and there will > never be a large result set for this particular query so performance is > less of an issue but it’s still an issue all the same as the solution is > an ActiveResource solution and the site that is returning the query is > under quite a heavy load. > > I think I might hit some SQL forums on this one. > > Cheers, > > James Best, – Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid ---
**Author:** ![aftershock](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/8e7dd6/32.png) [@aftershock](https://www.ruby-forum.com/u/aftershock)\ **Post date:** [December 3, 2009, 9:41pm UTC](https://www.ruby-forum.com/t/complex-find/179687/7 "2009-12-03T21:41:34Z")
James W. wrote: > I think I might hit some SQL forums on this one. If you’re looking for a raw SQL solution this will do the trick: select a.id from plugins a left join (select plugin\_id from site\_plugins where site\_id = 1) b on a.id = b.plugin\_id where b.plugin\_id is null; I suppose you could add a method to Site model to give you what you want. Something like: site # assume this exists site.plugins\_not\_installed ## Site.rb def plugins\_not\_installed Plugin.find\_by\_sql( … ) end