Geek Answers Handbook

How to get has_many through: through servers running

I have a JRuby application that I am trying to create relationships has_many through:for databases that live on two completely different servers. I understand that joins will not work across tables on different servers. I would like to simulate joins so that the developer using the model should not (how) know about the cross-server connection.

There are several additional challenges to this setup:

  • Remote Database Read Only
  • The table names and primary keys in the remote database do not comply with the rails naming conventions. (Remote Database Data Warehouse )
  • I would like to be able to use the model as if I were has_and_belongs_to_many.

I thought about writing my own association , but it's a little complicated, and I cannot find any manuals or really any starting points other than reading the Rails code.

Is there an easy way to do this that I am missing?

Does an ActiveRecord user association create a better way to do this? and if so, where to start?

Code similar to my setup:

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
+4
source share
2 answers

Just ran a quick script for you, here's the repo: https://github.com/beneggett/many_db_example

In the repo, I just made 2 different db on my local one, but that doesn't matter, the principle is the same:

It seemed to me that this worked well:

account_dim_users, has_many through/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

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 ​​ .

, !

: , , :

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
+3

More articles:


All Articles