Hi,
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
establish_connection(old)
end
class NewModel < ActiveRecord::Base
has_one :old_model
end
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 R.
If you must do it like this (and my must I mean you can’t run the “new”
database on the “old” database by using new tables), I would create
faux-ActiveRecord methods in the model classes.
class OldModel < ActiveRecord::Base
establish_connection(old)
end
class NewModel < ActiveRecord::Base
def old_model
OldModel.find(:first, :conditions => [“new_model_id = ?”, self.id])
end
def old_model=
if self.old.nil?
OldModel.create({:new_model_id => self.id}.join(params))
else
OldModel.update(self.id, params)
end
end
end
Andrew R. wrote:
Hi,
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
establish_connection(old)
end
class NewModel < ActiveRecord::Base
has_one :old_model
end
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 R.
This may not be as easy as it looks. Rails has no way to know that a
cross data source connection can be handled in a single query. (Imagine
if in ‘establish_connection(old)’, old referred to a database on another
server). I think there might be a way to use a :through and :source to
cajole the correct query. I’m sure you can use :finder_sql in your
has_many statement, but MySQL, which doesn’t always play well with
compound queries, may instead execute this as 1+n queries.
John M.
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
end
def self.db_name
“legacy_db”
end
def self.table_name_prefix
self.db_name + “.”
end
establish_connection(self.db_name)
end
class OldModel < LegacyBase
…
end
-Andrew R.
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?
-Andrew
On Apr 4, 3:48 pm, John M. [email protected]