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
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
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?
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
[email protected]
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
[email protected]
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
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
[email protected]
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