Has anyone faced this problem before? I have 3 models, Project, ProjectRole and User. The tables are distributed in two databases. -------------------------------------------------------------------------------------------- Databases: -------------------------------------------------------------------------------------------- Database X: users Database Y: projects project_roles -------------------------------------------------------------------------------------------- Models: --------------------------------------------------------------------------------------------- class Project < ActiveRecord::Base has_many :project_roles has_many :users, :through => :project_roles end class ProjectRole < ActiveRecord::Base belongs_to :project belongs_to :user end class User < ActiveRecord::Base establish_connection :X has_many :project_roles has_many :projects, :through => :project_roles end ---------------------------------------------------------------------------------------------- Now when i try to access users from project:>> p = Project.first+----+-----------+-------+ | id | name | alias | +----+-----------+-------+ | 1 | Jerde L | PRO | +----+-----------+-------+>> p.usersHirb Error: Mysql::Error: Table ''X.project_roles'' doesn''t exist: SELECT `users`.* FROM `users` INNER JOIN `project_roles` ON `users`.id = `project_roles`.user_id WHERE ((`project_roles`.project_id = 1)) C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ active_record/connection_adapters/abstract_adapter.rb:219:in `log'' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ active_record/connection_adapters/mysql_adapter.rb:323:in `execute'' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ active_record/connection_adapters/mysql_adapter.rb:608:in `select'' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ active_record/connection_adapters/abstract/database_statements.rb:7:in `select_all_without_query_cache'' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ active_record/connection_adapters/abstract/query_cache.rb:62:in `select_all'' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ active_record/base.rb:661:in `find_by_sql'' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ active_record/base.rb:1548:in `find_every'' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ active_record/base.rb:615:in `find'' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ active_record/associations/has_many_through_association.rb:83:in `find_target'' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ active_record/associations/association_collection.rb:354:in `load_target'' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ active_record/associations/association_proxy.rb:212:in `method_missing'' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ active_record/associations/association_collection.rb:371:in `method_missing_without_paginate'' c:/rubyonrails/rails_apps/test_project/vendor/gems/ will_paginate-2.3.12/lib/will_paginate/finder.rb:170:in `method_missing'' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/hirb-0.3.1/lib/hirb/ formatter.rb:78:in `determine_output_class'' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/hirb-0.3.1/lib/hirb/ formatter.rb:48:in `format_output'' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/hirb-0.3.1/lib/hirb/ view.rb:213:in `render_output'' C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/hirb-0.3.1/lib/hirb/ view.rb:126:in `view_output''>>BUT: Getting projects from users works just fine.>> u = User.first+----+--------------+ | id | username | +----+--------------+ | 1 | dummy | +----+---------------+>> u.projects+----+---------------------+-------+ | id | name | alias | +----+---------------------+--------+ | 1 | Jerde L | PRO | | 2 | Spinka Group | PRO | | 3 | Mosciski LLC | PRO | +---+-----------------------+-------+ Any idea what could be wrong? -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Apr 13, 2010, at 10:23 PM, tispratik wrote:> Has anyone faced this problem before? > > I have 3 models, Project, ProjectRole and User. The tables are > distributed in two databases. > > -------------------------------------------------------------------------------------------- > Databases: > -------------------------------------------------------------------------------------------- > Database X: > users > > Database Y: > projects > project_roles > > -------------------------------------------------------------------------------------------- > Models: > --------------------------------------------------------------------------------------------- > class Project < ActiveRecord::Base > has_many :project_roles > has_many :users, :through => :project_roles > end > > class ProjectRole < ActiveRecord::Base > belongs_to :project > belongs_to :user > end > > class User < ActiveRecord::Base > establish_connection :X > has_many :project_roles > has_many :projects, :through => :project_roles > end > ---------------------------------------------------------------------------------------------- > > Now when i try to access users from project: > >>> p = Project.first > +----+-----------+-------+ > | id | name | alias | > +----+-----------+-------+ > | 1 | Jerde L | PRO | > +----+-----------+-------+ > >>> p.users > Hirb Error: Mysql::Error: Table ''X.project_roles'' doesn''t exist: > SELECT `users`.* FROM `users` INNER JOIN `project_roles` ON > `users`.id = `project_roles`.user_id WHERE > ((`project_roles`.project_id = 1)) > C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ > active_record/connection_adapters/abstract_adapter.rb:219:in `log'' > C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ > active_record/connection_adapters/mysql_adapter.rb:323:in `execute'' > C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ > active_record/connection_adapters/mysql_adapter.rb:608:in `select'' > C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ > active_record/connection_adapters/abstract/database_statements.rb:7:in > `select_all_without_query_cache'' > C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ > active_record/connection_adapters/abstract/query_cache.rb:62:in > `select_all'' > C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ > active_record/base.rb:661:in `find_by_sql'' > C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ > active_record/base.rb:1548:in `find_every'' > C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ > active_record/base.rb:615:in `find'' > C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ > active_record/associations/has_many_through_association.rb:83:in > `find_target'' > C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ > active_record/associations/association_collection.rb:354:in > `load_target'' > C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ > active_record/associations/association_proxy.rb:212:in > `method_missing'' > C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/ > active_record/associations/association_collection.rb:371:in > `method_missing_without_paginate'' > c:/rubyonrails/rails_apps/test_project/vendor/gems/ > will_paginate-2.3.12/lib/will_paginate/finder.rb:170:in > `method_missing'' > C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/hirb-0.3.1/lib/hirb/ > formatter.rb:78:in `determine_output_class'' > C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/hirb-0.3.1/lib/hirb/ > formatter.rb:48:in `format_output'' > C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/hirb-0.3.1/lib/hirb/ > view.rb:213:in `render_output'' > C:/rubyonrails/ruby/lib/ruby/gems/1.8/gems/hirb-0.3.1/lib/hirb/ > view.rb:126:in `view_output'' >>> > > > BUT: Getting projects from users works just fine. > >>> u = User.first > +----+--------------+ > | id | username | > +----+--------------+ > | 1 | dummy | > +----+---------------+ > >>> u.projects > +----+---------------------+-------+ > | id | name | alias | > +----+---------------------+--------+ > | 1 | Jerde L | PRO | > | 2 | Spinka Group | PRO | > | 3 | Mosciski LLC | PRO | > +---+-----------------------+-------+ > > > Any idea what could be wrong?You''re trying to run a single SQL query that references tables in two different databases. That isn''t going to work. Set things up so that you can see the SQL being run and you''ll see why the first isn''t working and the second is. The second isn''t trying to join any tables. It''s running a query on the second database using a *value* retrieved from the first. -philip -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Thanks for the quick reply Philip.
Unfortunately i am working on a windows machine and i am not aware of
any tool which shows the query being run.
An interesting workaround i found is by explicitly stating the
database.table_name in the intermediate model.
class ProjectRole < ActiveRecord::Base
set_table_name ''Y.project_roles''
end
Seems like when i do user.projects, rails assumes that the
project_roles table is in the latter model''s database (which is true)
so it works.
But when i do, project.users, rails looks for project_roles table in
the database where users table is located (which is incorrect), so it
dosent.
But this dosent seem to be a good workaround as i have to hardcode the
database.table_name in the model class.
-Regards,
Pratik
--
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
anton effendi
2010-Apr-14 07:00 UTC
Re: Re: Connecting multiple databases - has_many fails
hmmmm u read about establish_connection on active record.... may be it will help u.... Thank you... On Wed, Apr 14, 2010 at 12:43 PM, tispratik <tispratik-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Thanks for the quick reply Philip. > Unfortunately i am working on a windows machine and i am not aware of > any tool which shows the query being run. > An interesting workaround i found is by explicitly stating the > database.table_name in the intermediate model. > > class ProjectRole < ActiveRecord::Base > set_table_name ''Y.project_roles'' > end > > Seems like when i do user.projects, rails assumes that the > project_roles table is in the latter model''s database (which is true) > so it works. > But when i do, project.users, rails looks for project_roles table in > the database where users table is located (which is incorrect), so it > dosent. > But this dosent seem to be a good workaround as i have to hardcode the > database.table_name in the model class. > > -Regards, > Pratik > > -- > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > For more options, visit this group at > http://groups.google.com/group/rubyonrails-talk?hl=en. > >-- Senior Rails Developer Anton Effendi - Wu You Duan -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
In database.yml: your_environment: adapter: your_adapter database: your_database user: your_user ... a_name_for_your_other_database_connection: # your connection information here: adapter, etc. In your models for the other database: class YourClassName < YourOtherDatabaseConnectionName # what you named your other DB connection in database.yml With the above you wouldn''t need to specify the DB and table names in your models but the class definition would contain the connection the model belongs to. On Apr 14, 3:00 am, anton effendi <wuyoud...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> hmmmm > u read about establish_connection on active record.... > > may be it will help u.... > > Thank you... > > > > On Wed, Apr 14, 2010 at 12:43 PM, tispratik <tispra...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > Thanks for the quick reply Philip. > > Unfortunately i am working on a windows machine and i am not aware of > > any tool which shows the query being run. > > An interesting workaround i found is by explicitly stating the > > database.table_name in the intermediate model. > > > class ProjectRole < ActiveRecord::Base > > set_table_name ''Y.project_roles'' > > end > > > Seems like when i do user.projects, rails assumes that the > > project_roles table is in the latter model''s database (which is true) > > so it works. > > But when i do, project.users, rails looks for project_roles table in > > the database where users table is located (which is incorrect), so it > > dosent. > > But this dosent seem to be a good workaround as i have to hardcode the > > database.table_name in the model class. > > > -Regards, > > Pratik > > > -- > > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > > To unsubscribe from this group, send email to > > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > > . > > For more options, visit this group at > >http://groups.google.com/group/rubyonrails-talk?hl=en. > > -- > > Senior Rails Developer > Anton Effendi - Wu You Duan-- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Philip Hallstrom
2010-Apr-14 15:56 UTC
Re: Re: Connecting multiple databases - has_many fails
On Apr 13, 2010, at 10:43 PM, tispratik wrote:> Thanks for the quick reply Philip. > Unfortunately i am working on a windows machine and i am not aware of > any tool which shows the query being run.In whatever windows uses as ".irbrc" put the following: # Log to STDOUT if in Rails if ENV.include?(''RAILS_ENV'') && !Object.const_defined? (''RAILS_DEFAULT_LOGGER'') require ''logger'' RAILS_DEFAULT_LOGGER = Logger.new(STDOUT) end Then run your queries in ./script/console and you should see the SQL.> An interesting workaround i found is by explicitly stating the > database.table_name in the intermediate model. > > class ProjectRole < ActiveRecord::Base > set_table_name ''Y.project_roles'' > end > > Seems like when i do user.projects, rails assumes that the > project_roles table is in the latter model''s database (which is true) > so it works. > But when i do, project.users, rails looks for project_roles table in > the database where users table is located (which is incorrect), so it > dosent. > But this dosent seem to be a good workaround as i have to hardcode the > database.table_name in the model class.You''re getting lucky again. Move one of the databases to another server and the above will break. Setup the logging so you can see the queries. That will make it clear why one works and one doesn''t.> > -Regards, > Pratik > > -- > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@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 > . >-- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.