Ruby on Rails Many To Many associations with multiple databases

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.

Easily select random records in rails

If you ever wondered how to easily retrieve a random record in an ActiveRecord model, here’s en easy way to do that: use the sample method.

sample is a class method from the Array class that retrieves one or more random items from an array instance. It conveniently returns nil or an array lesser than the items requested if the original array has not enough items.

Since all ActiveRecord finds return an array, you can easily add the sample call at the end of a find call to get the random items you need from complex queries.

For example, imagine you have a Book model which has a published_at datetime attribute and you want to show, in your library home page, a random selection of 5 books that have been published. You can easily get those using this snippet:

def get_random_books_for_home_page
  find(:all).sample(5)
end