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

Answer 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:

1. Eager load the site, network, and permissions
2. 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!