Rails - combining scopes from different tables

Question

I have a couple of scopes on different (and somewhat linked) models

class Alarm
   has_one :site #use mac_address field in Alarm and Site as key / foreign key

   scope :any_network, lambda{ joins(:network) } #more complex stuff can be done :-)
   #etc
end

class Network
   has many :permissions
   has_many :sites

   scope :has_permission, lambda{|level, user| #etc - returns a list of networks that a                  
                                               # user has the `level` permission for }

   #etc
end

class Permission
    has_one :network, :user
    #etc -uses a flags field to hold permision levels
end

So I can do

Alarm.any_network

which generates

SELECT `alarm`.* FROM `alarm` INNER JOIN `site` ON 
`site`.`mac_address` = `alarm`.`mac_address` INNER JOIN `network` ON 
`network`.`id` = `site`.`network_id`

and I can do

Network.has_permission('manager',1)

which generates

SELECT `network`.* FROM `network` INNER JOIN `permission` ON 
`permission`.`network_id` = `network`.`id` 
WHERE (permission.user_id = 1 and permission.flags && 8)

and this is all as it should be.

What I can't work out is how to join these two scopes to generate the set of all alarms originating from any network which the user has the appropriate permission for. Something along the lines of

SELECT `alarm`.* FROM `alarm` INNER JOIN `site` 
ON `site`.`mac_address` = `alarm`.`mac_address` 
INNER JOIN `network` ON `network`.`id` = `site`.`network_id` 
INNER JOIN `permission` ON `permission`.`network_id` = `network`.`id` 
WHERE (permission.user_id = 1 and permission.flags && 8)

I've tried chaining the scopes (can't get it to work) and I have tried merging the scopes (clearly the wrong thing to do!)

Any ideas - I've been tearing my hair out on this one for a couple of days.

Thanks,

Steve

score 1 · Accepted Answer · answered Aug 16 '12 at 19:37

1

At a glance, I imagine something like this, but its hard to tell because you didn't give us the Site model:

Alarm.includes(:site => {:network => :permissions}).where(['permissions.flags' && ?', 8], 'permissions.user_id' => 1)

This is obviously untested and hard to say exactly, but here's the gist of what's going on:

Eager load the site, network, and permissions
Enforce the conditions you outlines in your theoretical SQL statement.

FYI, I don't recall if this kind of multiple condition wheres work like I used above, but maybe it will get your wheels turning!

answered Aug 16 '12 at 19:37

Iamvery

274
2
11

Thanks lamvery. Site is pretty simple: `class Site < ActiveRecord::Base; belongs_to :network; has_many :alarms, :primary_key => :mac_address, :foreign_key => :mac_address; end` I see what you are trying to do, but it is all the kind of hardwired code I am looking to try and get away from - I would like to be able to use scopes to try and add a degree of flexibility to my code (so for example the `Alarm#any_network` scope is a very simple example - we have a variety of more complex queries we want to do on the set of alarms. Thxs Steve – Steve Gooberman-Hill Aug 16 '12 at 21:32
Perhaps the confusion is in the lack of details provided in your example scopes. You could easily turn my example above into a scope with something like: `scope :with_permissions, lambda{ |user,mask| includes(:site => {:network => permissions}).where(['permissions.flags' && ?', mask], 'permissions.user_id' => user) }` and use that to chain it into more complicated queries. – Iamvery Aug 17 '12 at 17:55

Rails - combining scopes from different tables

1 Answers1