I am building an rails 5 app that connects to 2 different databases (dbA & dbB). My databases are on the same database host.
I want to make a wishlist. Pretty easy when using the same DB, but I am stuck with an "interesting" error.
This is what the databases look like:
the models are as follow:
user.rb
class User < ApplicationRecord has_one :wishlist, dependent: :destroy end wishlist.rb
class Wishlist < ApplicationRecord belongs_to :user # has_and_belongs_to_many :wines # The above did not work # so I had to revert to has_many through has_many :wines_wishlists has_many :wines, through: :wines_wishlists end wines_wishlist.rb
class WinesWishlist < ApplicationRecord belongs_to :wine belongs_to :wishlist def self.table_name_prefix "dbA_#{Rails.env}." end # I added the above to prevent ActiveRecord from # looking for the table in the wrong database end wine.rb (legacy model)
class Wine < ApplicationRecord self.abstract_class = true establish_connection LEGACY_DB # LEGACY_DB is the legacy database connection info from a yaml file # located in config. def self.table_name_prefix "dbB_#{Rails.env}." end end This is quite straigth forward IMHO. Now the interresting error:
When I try the following :
user = User.last user.wishlist.wines It works on my local machine in development. It doesn't work on my staging server! When I try in the rails console, I get this:
ActiveRecord::StatementInvalid: Mysql2::Error: Table 'dbA_staging.wines_wishlists' doesn't exist: SELECT `dbB_staging`.`wines`.* FROM `dbB_staging`.`wines` INNER JOIN `dbA_staging`.`wines_wishlists` ON `dbB_staging`.`wines`.`id` = `dbA_staging`.`wines_wishlists`.`wine_id` WHERE `dbA_staging`.`wines_wishlists`.`wishlist_id` = 1 This is the expected SQL.
user.wishlist.wines.to_sql => "SELECT `dbB_staging`.`wines`.* FROM `dbB_staging`.`wines` INNER JOIN `dbA_staging`.`wines_wishlists` ON `dbB_staging`.`wines`.`id` = `dbA_staging`.`wines_wishlists`.`wine_id` WHERE `dbA_staging`.`wines_wishlists`.`wishlist_id` = 1" Even better, when I try the same SQL in rails db on my staging machine, it works!! It doesn't work in rails even though the SQL is correct, but it works in mysql command line.
I based my code on the following article and made some research, but I can't seem to figure out how to go around this problem.
I am using (same for development and staging):
- Rails 5.1.1
- ruby 2.4.0p0
- mysql 5.6.34 (staging)
- mysql 5.7.17 (development)
Any help would be greatly appreciated!
1 Answers
Answers 1
Taking a look at the article you linked to, it seems to be using a gem st-elsewhere, i.e.
has_many_elsewhere :wines, :through => :wines_wishlist Also, as stated in the article, you can't make JOIN queries across database connections. The gem circumvents this using some less efficient queries, the details of which I did not look up.
0 comments:
Post a Comment