joining across databases in a find statement with include (legacy db)


I have a legacy database 'old' and a new database 'new' which rails
uses. I set up models using old with establish_connection(old), and
this works well, except for include:

class OldModel < ActiveRecord::Base

class NewModel < ActiveRecord::Base
  has_one :old_model

a = OldModel.find :first
NewModel.find(:all, :include => :old_model)

will give an error like

Mysql::Error: Table 'new_db.old_models' doesn't exist: SELECT ... FROM
new_models LEFT OUTER JOIN old_models ON ... WHERE ...

Obviously a performance hit without the join. MySQL does support
joins you specify the database first (ex old_db.old_models). I can
hack it by doing the join manually but that doesn't sound like the
rails way. Any ideas?

-Andrew Roth

Good point. But at least it could try putting "database." when it
detects different connections on the chance they will join. A
parameter to has_many, :join_across_dbs => true could work too, but
I'm not sure I'm ready to start hacking Active Record!

Do people really put lots of tables in one database? Don't tables
start to get prefixed, like we have accountadmin_viewer,
accountadmin_access, etc. plus form_elements, form_questions, ... and
a bunch more. Isn't it DRY - or at least good practice - to start
making different databases after a few hundred of these tables?


Just wanted to follow up on this. I actually got it working by simply
pretending the table name was legacy_db.table! Easily done with
set_table_name, or if you have a bunch of models for the legacy db,
something like

class LegacyBase < ActiveRecord::Base
  def self.pluralize_table_names
    false # our legacy db uses singular table names

  def self.db_name

  def self.table_name_prefix
    self.db_name + "."


class OldModel < LegacyBase

-Andrew Roth