Sometimes you need to use multiple databases in your Rails projects. Usually when some data must be shared between different applications. When this happens you usually have some models in a shared database, and some other models in the specific application database. This can be easily done using the establish_connection method in the shared models to tell them they have to connect to a different database.

However, when you need some interaction between those shared models and the models of your specific application, like a has_many, :through association, some problems arise. The typical Many To Many association uses an intermediate database table that links the relation between two models, and allows you to add some extra information on that relation. When navigating through the association, Rails tries to make an SQL query that joins the model with this intermediate table. For example, imagine you have a Team model, which has many Players, but a player can also be on more than one team. We use an intermediate model TeamPlayers (and we can also use it to save the role of that player into that team, for example). You would have those three tables:

  • teams
  • players
  • teams_players
When asking for the players of a given Team, Rails would do something similar to this: 
SELECT "players".* FROM "players" INNER JOIN teams_players" ON "players".id = "teams_players".player_id WHERE "players".team_id = 1
Where 1 is the id of the team you asked for. This [obviously] works perfectly fine when everything is in the same database, and it's as efficient as the SQL database manager you're using. What happens, however, when we have the Player model in another database? It will miserably fail because Rails will try to join with a table that doesn't exist.

Unfortunately, there's no efficient way to solve this problem, that is, using SQL, as you can't work with tables from different databases. However, there's a rather elegant solution that Brian Doll cared to implement as a gem a while ago. As indicated in the GitHub readme, you just have to use a has_many_elsewhere relation instead of the usual one, and make sure that the model referenced has the connection established to the shared database. And that's all.

The magic donde behind the scenes is pretty simple: this gem just replicates the same methods that the ActiveRecord::Base class does in the has_many method call, changing the failing unique SQL calls to double SQL calls, one for each database, fetching the intermediate models first, and then fetching the remote models using those ids.

This method is not perfect, as probably not all the goodness of the original association can be done with it, but for simple scenarios is more than enough.