establish_connection doesn't seem to support joins

Question

I'm trying to move my User account and session data into a separate database so that we can eventually share it across multiple applications.

I've seen plenty of people online saying to use establish_connection to tell a model to connect to a different db, but I am unable to get this to work.

config/database.yml

development:
  adapter: mysql2
  encoding: utf8
  reconnect: true
  pool: 5
  host: localhost
  database: project_name_development

authentication:
  adapter: mysql2
  encoding: utf8
  reconnect: true
  pool: 5
  host: localhost
  database: authentication

app/models/user.rb

class User < ActiveRecord::Base
  establish_connection :authentication
  has_one :person
end

app/models/person.rb

class Person < ActiveRecord::Base
  belongs_to :user
end

This much seems to be working:

> User.connection.instance_eval { @config[:database] }
=> "authentication"
> Person.connection.instance_eval { @config[:database] }
=> "project_name_development"

and I can query User in isolation:

> User.where(:admin => true)
=> [ ... lots of results .. ]

but as soon as I try to use join it breaks:

> User.joins(:person)
ActiveRecord::StatementInvalid: Mysql2::Error: Table 'authentication.people' doesn't exist: SELECT `users`.* FROM `users` INNER JOIN `people` ON `people`.`user_id` = `users`.`id`

ARel seems to be using the current database instead of getting the correct one through reflection.

I found this very old bug report from almost two years ago about this problem, but I am almost certain it was regarding the old ARel syntax, and I really doubt the code examples would work anymore.

Is this even possible?

Update: made a little headway by doing this:

User.joins("INNER JOIN project_name.people ON project_name.people.user_id = authentication.users.id")

but that is really tedious, and one of the tables I'd like to join is polymorphic.

I tried adding:

set_table_name 'project_name.people'

but that returns

NoMethodError: undefined method `eq' for nil:NilClass

It seems to me that Rails3 doesn't actually support multiple schemas. Am I wrong?

I've noticed the same issue when trying to port my 2.3 site to rails3. It seems Arel 2.0.6 maintains a connection to the original database and doesn't realise when the db is changed using establish_connection. Arel HEAD seems to be an improvement, but I'm noticing other errors. Bring on rails 3.0.4... — James Healy, Jan 06 '11 at 12:21
@karudzo the Rails API docs says that ActiveRecord supports using multiple DBs. If you can't use `join`, that doesn't qualify as 'support' in my book. — Adam Lassek, Jan 20 '11 at 22:35
@AdamLassek, you can't use join across multiple databases because you can't use join across multiple databases - at all, in any platform imaginable. — Alexei Averchenko, May 05 '12 at 08:06

score 1 · Answer 1 · answered Jan 20 '11 at 22:20

1

Have you thought about taking it out of the app layer and just replicating certain tables only with MySQL replication?

answered Jan 20 '11 at 22:20

jschorr

3,034
1
17
20

That may be necessary. Good suggestion. – Adam Lassek Jan 20 '11 at 22:30

establish_connection doesn't seem to support joins

1 Answers1