Rails - Apr 2007 - joining across databases in a find statement with include (legacy db)

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 Roth


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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


-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Andrew Roth 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 Roth
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 Miller

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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 Miller
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
> Andrew Roth 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 Roth
>
> 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 Miller
>
> --
> Posted viahttp://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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 Roth

On Apr 4, 11:16 pm, "Andrew Roth"
<andrewr...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> Good point.  But at least it could try putting "database." when
it
> detects different connections on the chance they willjoin.  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 Miller
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
> wrote:
>
> > Andrew Roth wrote:
> > > Hi,
>
> > > I have alegacydatabase ''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 OUTERJOINold_models ON ... WHERE ...
>
> > > Obviously a performance hit without thejoin.  MySQL does support
> > > joins you specify the database first (ex old_db.old_models).  I
can
> > > hack it by doing thejoinmanually but that doesn''t sound
like the
> > > rails way.  Any ideas?
>
> > > -Andrew Roth
>
> > 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 Miller
>
> > --
> > Posted viahttp://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---