Complex joins with legacy tables in Rails

Question

Hello, I'm writing fairly simple web interface for invoice/customer/contract database. I would like to have the following structure for my models:

class Invoice < ActiveRecord::Base
   set_table_name 't10_invoices'
   set_primary_key 'id_invoice'
   has_one :client
end

class Client < ActiveRecord::Base
   set_table_name 't20_clients'
   set_primary_key 'id_client'
   has_many :invoices
end

The problem is that I have the following database structure:

Tables:
t10_invoices:
   id_invoice - primary_key
   id_contract - foreign_key


t12_contracts:
   id_contract - primary_key      

t15_contracts_clients
   id_client - foreign_key
   id_contract - foreign_key

t20_clients
   id_client - primary_key

There is 1-1 relation between t20_clients and t12_contracts through t15_contracts_clients and there is 1-n relation between t20_clients and t10_invoices.

What is the difference between :foreign_key and :association_foreign_key?

The problem is that I cannot modify the structure of the database and I'm quite lost in the redefining the table names, foreign keys, join_foreign_keys etc... I would like a simple way to find clients of invoice in normal Rails way Invoice.find(:first).client

I would appreciate any suggestions and help.

Answer 1

Have you considered creating a database view?

Answer 2

Create a ContractClient Join Model to correspond to the t15_contracts_clients table. This specifies the table name and uses belongs_to associations for the 2 foreign keys:

class ContractClient < ActiveRecord::Base
  set_table_name 't15_contracts_clients'
  belongs_to :client, :foreign_key => :id_client
  belongs_to :contract, :foreign_key => :id_contract
end

Specify the table and primary key for Client:

class Client < ActiveRecord::Base
  set_table_name 't20_clients'
  set_primary_key 'id_client'
end

Use a belongs_to :contract_client on Invoice and then a has_one :through to associate the client through the ContractClient:

class Invoice < ActiveRecord::Base
  set_table_name 't10_invoices'
  set_primary_key 'id_invoice'
  belongs_to :contract_client, :foreign_key => :id_contract
  has_one :client, :through => :contract_client
end

Invoice.find(:first).client should then behave as expected.

Answer 3

You have a couple of oddities in the model. It is not set up in such a way that client has_one contract. It has a join table in the middle which allows for multiple contracts per client. Since you can't change the tables, it's best to just enforce whatever relationships there are in code. Since that table apparently does not have a primary key, you probably need to use has_and_belongs_to_many. In that relationship type, :association_foreign_key is for the "other" class, and :foreign_key is for the class you are in.

class Invoice < ActiveRecord::Base
   set_table_name 't10_invoices'
   set_primary_key 'id_invoice'
   belongs_to :contract, :foreign_key => 'id_contract'
   has_one :client, :through => :contract
end

class Contract
  set_table_name 't12_contracts'
  set_primary_key 'id_contract'
  has_and_belongs_to_many :clients, 
                           :join_table => 't15_contracts_clients' 
                           :foreign_key => 'id_contract',
                           :association_foreign_key => 'id_client'
  has_many :invoices, :foreign_key => 'id_contract'
end

class Client < ActiveRecord::Base
   set_table_name 't20_clients'
   set_primary_key 'id_client'
   has_and_belongs_to_many :clients, 
                           :join_table => 't15_contracts_clients' 
                           :foreign_key => 'id_client',
                           :association_foreign_key => 'id_contract'
   has_many :invoices, :through => :contracts
end

You then need to invoke

Invoice.find(:first).client