ruby-on-rails-4 - 如何通过 : across servers working 获取 has_many

Question

我有一个正在尝试创建的 JRuby 应用程序 has_many through:位于两个完全不同的服务器上的数据库的关系。我知道连接不能跨不同服务器上的表工作。我想要的是模拟连接，以便使用该模型的开发人员不必(作为)知道跨服务器连接。

此设置还有一些额外的复杂性:

远程数据库是只读的

远程数据库中的表名和主键不遵循 rails 命名约定。 (远程数据库是 Data Warehouse )

我希望能够像使用 has_and_belongs_to_many 一样使用该模型在里面。

我考虑过编写自己的自定义 association ，但这有点复杂，除了阅读 Rails 代码之外，我找不到任何指南或任何起点。

有没有一种简单的方法可以做到这一点，我错过了？

构建自定义 ActiveRecord 关联是实现此目的的最佳方法吗？如果是这样，我从哪里开始？

类似于我的设置的代码:
config/database.yml

development:
  adapter: postgresql
  encoding: unicode
  database: main
  username: username
  password: password
  host: localhost
  pool: 5

remote_development: # Read only
  adapter: jdbcmssql
  driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
  url: 'jdbc:sqlserver://foo.com;databaseName=main'
  username: username
  password: password

app/models/account.rb

class Portfolio < ActiveRecord::Base
  #has_and_belongs_to_many :dim_users, join_table: :accounts_dim_user
end

app/models/remote_model_base.rb

class RemoteModelBase
  require "#{Rails.root}/lib/sqljdbc4.jar"
  self.abstract_class = true
  establish_connection "remote_#{Rails.env}".to_sym
  after_initialize :readonly!
end

app/models/dim_user.rb

class DimUser < RemoteModelBase
  self.table_name = 'DimUser'
  self.primary_key = 'dwidDimUser'

  #has_and_belongs_to_many :accounts, join_table: :accounts_dim_user
end

config/schema.rb

ActiveRecord::Schema.define(version: 20140925200106) do

  create_table "accounts", force: true do |t|
    t.string   "name"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  create_table "accounts_dim_user", force: true, id: false do |t|
    t.integer  "dwidUser"
    t.integer  "account_id"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  # Defined in the remote database but it might look something like this
  # create_table "DimUser" do |t|
  #   t.integer dwidUser
  #   # ...
  # end

Answer 1

刚刚为您完成了一个快速场景，这里是 repo:https://github.com/beneggett/many_db_example

在 repo 中，我只是在本地做了 2 个不同的数据库，但没关系，主要是相同的:

这对我来说似乎很有效:

告诉账户account_dim_users连接表关联，但是手动映射has_many通过/habtm。

class Account < ActiveRecord::Base
  has_many :account_dim_users

  def dim_users
    account_dim_users.map {|account_dim_user| DimUser.find_by(dwidUser: account_dim_user.dwidUser) }
  end
end

这很重要，因为众所周知，标准连接不起作用；但是通过模型映射它工作正常。

AccountDimUser 连接表看起来很标准(我明确映射了键)

class AccountDimUser < ActiveRecord::Base
  has_many :accounts
  has_many :dim_users, primary_key: :dwidUser, foreign_key: :dwidUser

end

手动映射account_dim_users关联，手动映射accounts关联

class DimUser < ActiveRecord::Base
  establish_connection "other_db".to_sym
  after_initialize :readonly!
  self.table_name = 'DimUser'
  self.primary_key = 'dwidUser'

  def account_dim_users
    AccountDimUser.where(dwidUser: self.dwidUser)
  end

  def accounts
    account_dim_users.map {|account_dim_user| Account.find(account_dim_user.account_id) }
  end
end

这种方法允许您仍然以标准方式使用 Ruby 对象:

a = Account.first
  Account Load (0.6ms)  SELECT  "accounts".* FROM "accounts"   ORDER BY "accounts"."id" ASC LIMIT 1
=> #<Account:0x00000102d263d0> {
          :id => 1,
        :name => "New account",
  :created_at => Mon, 29 Sep 2014 15:07:07 UTC +00:00,
  :updated_at => Mon, 29 Sep 2014 15:07:07 UTC +00:00
}

——

a.account_dim_users
=> #<ActiveRecord::Associations::CollectionProxy [#<AccountDimUser id: 1, dwidUser: 1, account_id: 1, created_at: "2014-09-29 15:08:47", updated_at: "2014-09-29 15:08:47">, #<AccountDimUser id: 3, dwidUser: 5, account_id: 1, created_at: "2014-09-29 15:24:17", updated_at: "2014-09-29 15:25:06">]>

——

a.dim_users
  AccountDimUser Load (0.3ms)  SELECT "account_dim_users".* FROM "account_dim_users"  WHERE "account_dim_users"."account_id" = $1  [["account_id", 1]]
  DimUser Load (0.9ms)  SELECT  "DimUser".* FROM "DimUser"  WHERE "DimUser"."dwidUser" = 1 LIMIT 1
  DimUser Load (0.3ms)  SELECT  "DimUser".* FROM "DimUser"  WHERE "DimUser"."dwidUser" = 5 LIMIT 1
=> [
  [0] #<DimUser:0x0000010981af10> {
            :id => 1,
      :dwidUser => 1,
    :created_at => Mon, 29 Sep 2014 15:06:44 UTC +00:00,
    :updated_at => Mon, 29 Sep 2014 15:06:44 UTC +00:00
  },
  [1] #<DimUser:0x00000109838b00> {
            :id => 5,
      :dwidUser => 5,
    :created_at => Mon, 29 Sep 2014 15:23:01 UTC +00:00,
    :updated_at => Mon, 29 Sep 2014 15:23:01 UTC +00:00
  }
]

——

d = DimUser.first
  DimUser Load (0.5ms)  SELECT  "DimUser".* FROM "DimUser"   ORDER BY "DimUser"."dwidUser" ASC LIMIT 1
=> #<DimUser:0x0000010990aad8> {
          :id => 1,
    :dwidUser => 1,
  :created_at => Mon, 29 Sep 2014 15:06:44 UTC +00:00,
  :updated_at => Mon, 29 Sep 2014 15:06:44 UTC +00:00
}

——

d.account_dim_users
  AccountDimUser Load (0.5ms)  SELECT "account_dim_users".* FROM "account_dim_users"  WHERE "account_dim_users"."dwidUser" = 1
=> #<ActiveRecord::Relation [#<AccountDimUser id: 1, dwidUser: 1, account_id: 1, created_at: "2014-09-29 15:08:47", updated_at: "2014-09-29 15:08:47">]>

——

 d.accounts
  AccountDimUser Load (0.5ms)  SELECT "account_dim_users".* FROM "account_dim_users"  WHERE "account_dim_users"."dwidUser" = 1
  Account Load (0.4ms)  SELECT  "accounts".* FROM "accounts"  WHERE "accounts"."id" = $1 LIMIT 1  [["id", 1]]
=> [
  [0] #<Account:0x000001099788d0> {
            :id => 1,
          :name => "New account",
    :created_at => Mon, 29 Sep 2014 15:07:07 UTC +00:00,
    :updated_at => Mon, 29 Sep 2014 15:07:07 UTC +00:00
  }
]

在处理大量记录时，可能可以对此进行一些优化，但这是一个很好的基础。

另一种方法可能是在关联表本身上进行查找，例如:

  def find_dim_user
    DimUser.find_by(dwidUser: self.dwidUser)
  end

但我更喜欢我建议的第一种方式，因为它可以让您使用普通的 ruby​​ 方法链接关联方法。

任何其他问题，让我知道!

编辑:您可以更改 map 功能以使用 Active Record Relations 或类似功能，以启用更多功能:

class Account < ActiveRecord::Base
  has_many :account_dim_users

  def dim_users
    dim_user_ids = account_dim_users.map {|account_dim_user| account_dim_user.dwidUser }
    DimUser.where(dwidUser: dim_user_ids)
  end
end