multiple database connections with has_many through

Question

How can I make a has_many through work with multiple database connections?

I have a database named "master" that holds the location information. That is updated from a separate application. Users can have access to many locations, but all the other models are located in another database named "budget". Here are how the models are setup.

# place.rb
class Place < ActiveRecord::Base
  belongs_to :user
  belongs_to :location
end

# user.rb
class User < ActiveRecord::Base
  has_many :locations, :through => :places
  has_many :places
end

# location.rb
class Location < ActiveRecord::Base
  establish_connection "master"
  has_many :places
  has_many :users, :through => :places
end

When I run commands through irb, I get the following

> Location.first.places.create(:user_id => 1)
> #<Place id: 1, user_id: 1, location_id: 1, created_at: "2011-11-28 20:58:43",  updated_at: "2011-11-28 20:58:43">

> Location.first.places
> [#<Place id: 1, user_id: 1, location_id: 1, created_at: "2011-11-28 20:58:43", updated_at: "2011-11-28 20:58:43">]

> Location.first.users
> [#<User id: 1, username: "toby", role: "guest", created_at: "2011-11-28 17:45:40", updated_at: "2011-11-28 17:45:40">

> User.first.locations
> Mysql2::Error: Table 'master.places' doesn't exist: SELECT `locations`.* FROM `locations` INNER JOIN `places` ON `locations`.`id` = `places`.`location_id` WHERE `places`.`user_id` = 1 ActiveRecord::StatementInvalid: Mysql2::Error: Table 'master.places' doesn't exist: SELECT `locations`.* FROM `locations` INNER JOIN `places` ON `locations`.`id` = `places`.`location_id` WHERE `places`.`user_id` = 1

I tried adding the current rails env to Place to try and override the default database for place, like this: # place.rb class Place < ActiveRecord::Base establish_connection Rails.env belongs_to :user belongs_to :location end

#database.yml

master:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: master
  pool: 5
  username: root
  password:
  socket: /var/run/mysqld/mysqld.sock
development:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: budget_development
  pool: 5
  username: root
  password:
  socket: /var/run/mysqld/mysqld.sock

That didn't help. Any ideas?

Serbocroatian

you should also post the snippet from your config/database.yml file where you define your databases – Auten

I updated it, thank you for the suggestion. – Serbocroatian

Answer 1

A friend answered this for me, and I figured it might be of some use to others.

class Location < ActiveRecord::Base
  #establish_connection "master"
  def self.table_name() "master.locations" end
  has_many :places
  has_many :users, :through => :places
end

Serbocroatian

in my case, database is on external server and this solution doesnt work.. – Benitobenjamen

Answer 2

The answer works for me, but I use this version in my relation table:

self.table_name = "master.locations"

Coupe

Answer 3

Anyone reading this a few years later, please be advised Rails 7 has introduced an explicit disable_joins: true option to has_one and has_many.

ref: https://edgeguides.rubyonrails.org/active_record_multiple_databases.html#handling-associations-with-joins-across-databases

Stentorian