Rails :scope across databases

Question

I have three models, Alarms, Sites, Networks. They are connected by belongs_to relationships, but they live in diferent databases

class Alarm < ActiveRecord::Base
   establish_connection :remote
   belongs_to :site, primary_key: :mac_address, foreign_key: :mac_address
end

class Site < ActiveRecord::Base
   establish_connection :remote
   belongs_to :network
end

class Network < ActiveRecord::Base
   establish_connection :local
end

I wish to select all alarms belonging to a particular network. I can do this usng raw sql within a scope as follows:

scope :network_alarms, lambda { |net|
    #need to ensure we are interrogating the right databases - network and site are local, alarm is remote
    remote=Alarm.connection.current_database
    local=Network.connection.current_database
    Alarm.find_by_sql("SELECT network.* FROM #{local}.network  INNER JOIN #{local}.site ON network.id = site.network_id  INNER JOIN #{remote}.alarm on #{remote}.alarm.mac_address = site.mac_address WHERE site.network_id=#{net.id}")
  }

and this works fine. However, this scope returns an array, so I can't chain it (for example, to use with_paginate's #page method). So

Is there a more intelligent way of doing this join. I have tried using join and where statements, for example (this is one of many variations I have tried):

scope :network_alarms, lambda { |net| joins(:site).where("alarm.mac_address = site.mac_address").where('site.network_id=?', net) }

but the problem seems to be that the rails #join is assuming that both tables are in the same database, without checking the connections that each table is using.

Answer 1

So the answer is simple when you know how...

ActiveRecord::Base has a table_name_prefix method which will add a specific prefix onto the table name whenever it is used in a query. If you redefine this method to add the database name onto the front of the table name, the SQL generated will be forced to use the correct database

so, in my original question we add the following method definition into tables Alarm, Site and Network (and anywhere else it was required)

def self.table_name_prefix
   self.connection.current_database+'.'
end

we can then build the joins easily using scopes

scope :network_alarms, lambda { |net| joins(:site => :network).where('site.network_id=?', net) }

Thanks to srosenhammer for the original answer (here : Rails 3 - Multiple database with joins condition)

Steve