Friday, 29 August 2008

Rails: 'Has_many through' association across databases

So recently I had the challenge of creating a 'has_many through' relationship across two databases.
"Why would you do this?" you may ask. Well quite simply I am in a team building a new a new data management system to sit on top of a legacy system with its legacy database. All the new code is new, shiny and streamlined and the old code is... well... crap but we have to keep both systems running concurrently so we have various tables in the legacy database we need to access from the new system. As it happens we need to access the legacy users table in a 'has_many through' from the new 'orders' table.

Set Up Your Secondary Database Connection

You can quite happily set up a model to connect do a database other then the default by setting the connection up in your 'config/database.yml' as follows:

 adapter: <adapter>
 database: <database>
 username: <username>
 password: <password>

And then in any model you want to use with your secondary database:

<model>.establish_connection configurations['legacy']

Create Your 'Through' Model

Now a normal 'has_many through' just plain won't work between models attached to two different databases but a normal has_many will. So we can create 'has_many through' functionality in the following way:

Set up your 'through' model on either database. It really doesn't matter which and set it to 'belong_to' your two main models.
Set both main models to 'has_many' of your 'through' model.

Create Your 'Through' Relationship

Use the following code in each of your main models to mimic the 'has_many through' association. In this example I'm using 'orders' and 'users' and my 'through' table is 'order_users':

In 'order'

def users
 user = []
 order_users.each do |ou|
  user << ou.user

In 'user'

def orders
 order = []
 order_users.each do |ou|
  order << ou.user

And you're done. Now the relationship will work just like any other 'has_many through'.