中間テーブルを用いた多対多の関係(その 4)
次に、中間テーブルが外部キー以外のデータを持つ場合について考えます。使用するアプリは
- 顧客(customer)
- 商品(product)
- 従業員(employee)
- 支店(branch)
- 売上(sale)
のデータを持っているとします。
モデルの作成から始めましょう。
rails g model customer name:string phone:string --timestamps=false rails g model product name:string group_name:string purchase_price:integer wholesale_price:integer --timestamps=false rails g model employee name:string phonetic:string mgr_id:integer birthday:date sex:integer --timestamps=false rails g model sale slip_no:integer line_no:integer processing_date:date product_id:integer employee_id:integer customer_id:integer quantity:integer --timestamps=false rails g model branch name:string --timestamps=false rails g migration CreateBranchesEmployees employee_id:integer branch_id:integer
最後は従業員(employee)と支店(branch)を関連付ける中間テーブルの作成のためのマイグレーションファイルを作成しています。主キーが作成されないように編集をしておきましょう。
class CreateBranchesEmployees < ActiveRecord::Migration def change create_table :branches_employees, :id => false do |t| t.integer :employee_id, :null => false t.integer :branch_id, :null => false end end end
データを投入します。
db/seeds.rb
# coding: utf-8 # (略) Branch.create( :name => '本店' ) Branch.create( :name => '支店' ) Customer.create( :name => '(株)ワイキキソフト', :phone => '090-AAAA-AAAA' ) Customer.create( :name => '鈴木商事', :phone => '090-BBBB-BBBB' ) Customer.create( :name => '斎藤模型店', :phone => '090-CCCC-CCCC' ) Customer.create( :name => 'マクロハード', :phone => '090-DDDD-DDDD' ) Customer.create( :name => '(株)ランヌ', :phone => '090-EEEE-EEEE' ) Product.create( :name => 'デスクトップ型パソコン', :group_name => 'パソコン本体', :purchase_price => 150000, :wholesale_price => 180000 ) Product.create( :name => 'ノート型パソコン', :group_name => 'パソコン本体', :purchase_price => 230000, :wholesale_price => 270000 ) Product.create( :name => '17型_ディスプレイ', :group_name => '周辺機器', :purchase_price => 40000, :wholesale_price => 50000 ) Product.create( :name => '19型_ディスプレイ', :group_name => '周辺機器', :purchase_price => 80000, :wholesale_price => 95000 ) Product.create( :name => '15型_液晶ディスプレイ', :group_name => '周辺機器', :purchase_price => 100000, :wholesale_price => 120000 ) Product.create( :name => 'デジタルカメラ', :group_name => '周辺機器' ) Product.create( :name => 'プリンタ', :group_name => '周辺機器', :purchase_price => 20000, :wholesale_price => 25000 ) Product.create( :name => 'スキャナ', :group_name => '周辺機器', :purchase_price => 25000, :wholesale_price => 30000 ) Product.create( :name => 'HUB', :group_name => 'ネットワーク機器', :purchase_price => 5000, :wholesale_price => 7000 ) Product.create( :name => 'ターミナルアダプタ', :group_name => 'ネットワーク機器', :purchase_price => 15000, :wholesale_price => 20000 ) Employee.create( :name => '鈴木', :phonetic => 'すずき', :birthday => '1960-01-23', :sex => 1 ) Employee.create( :name => '小野', :phonetic => 'おの', :birthday => '1960-08-02', :sex => 1 ) Employee.create( :name => '斎藤', :phonetic => 'さいとう', :birthday => '1963-10-15', :sex => 1 ) Employee.create( :name => '藤本', :phonetic => 'ふじもと', :birthday => '1972-07-18', :sex => 1, :mgr_id => 3 ) Employee.create( :name => '小林', :phonetic => 'こばやし', :birthday => '1971-02-11', :sex => 0, :mgr_id => 3 ) Employee.create( :name => '伊藤', :phonetic => 'いとう', :birthday => '1972-04-01', :sex => 0, :mgr_id => 2 ) Employee.create( :name => '佐瀬', :phonetic => 'させ', :birthday => '1975-02-21', :sex => 1, :mgr_id => 2 ) Employee.create( :name => '宇賀神', :phonetic => 'うがじん', :birthday => '1975-12-22', :sex => 1, :mgr_id => 1 ) Employee.create( :name => '岡田', :phonetic => 'おかだ', :birthday => '1972-03-18', :sex => 1, :mgr_id => 4 ) Employee.create( :name => '田中', :phonetic => 'たなか', :birthday => '1975-05-23', :sex => 1, :mgr_id => 9 ) Employee.create( :name => '井上', :phonetic => 'いのうえ', :birthday => '1980-02-18', :sex => 1, :mgr_id => 9 ) Employee.create( :name => '佐々木', :phonetic => 'ささき', :birthday => '1968-10-10', :sex => 1, :mgr_id => 9 ) Sale.create( :slip_no => 1, :line_no => 1, :processing_date => '2006-04-06', :customer_id => 2, :employee_id => 1, :product_id => 1, :quantity => 3 ) Sale.create( :slip_no => 1, :line_no => 2, :processing_date => '2006-04-06', :customer_id => 2, :employee_id => 1, :product_id => 4, :quantity => 3 ) Sale.create( :slip_no => 2, :line_no => 1, :processing_date => '2006-04-12', :customer_id => 1, :employee_id => 2, :product_id => 1, :quantity => 1 ) Sale.create( :slip_no => 3, :line_no => 1, :processing_date => '2006-04-18', :customer_id => 2, :employee_id => 2, :product_id => 1, :quantity => 1 ) Sale.create( :slip_no => 4, :line_no => 1, :processing_date => '2006-04-26', :customer_id => 4, :employee_id => 3, :product_id => 2, :quantity => 1 ) Sale.create( :slip_no => 4, :line_no => 2, :processing_date => '2006-04-26', :customer_id => 4, :employee_id => 3, :product_id => 7, :quantity => 1 ) Sale.create( :slip_no => 4, :line_no => 3, :processing_date => '2006-04-26', :customer_id => 4, :employee_id => 3, :product_id => 8, :quantity => 1 ) Sale.create( :slip_no => 5, :line_no => 1, :processing_date => '2006-05-08', :customer_id => 1, :employee_id => 6, :product_id => 3, :quantity => 3 ) Sale.create( :slip_no => 6, :line_no => 1, :processing_date => '2006-05-12', :customer_id => 5, :employee_id => 2, :product_id => 1, :quantity => 1 ) Sale.create( :slip_no => 6, :line_no => 2, :processing_date => '2006-05-12', :customer_id => 5, :employee_id => 2, :product_id => 3, :quantity => 2 ) Sale.create( :slip_no => 7, :line_no => 1, :processing_date => '2006-05-19', :customer_id => 4, :employee_id => 5, :product_id => 2, :quantity => 1 ) Sale.create( :slip_no => 8, :line_no => 1, :processing_date => '2006-05-22', :customer_id => 1, :employee_id => 6, :product_id => 2, :quantity => 1 ) Sale.create( :slip_no => 9, :line_no => 1, :processing_date => '2006-05-25', :customer_id => 2, :employee_id => 8, :product_id => 5, :quantity => 5 ) Sale.create( :slip_no => 10, :line_no => 1, :processing_date => '2006-06-02', :customer_id => 1, :employee_id => 2, :product_id => 5, :quantity => 1 ) Sale.create( :slip_no => 11, :line_no => 1, :processing_date => '2006-06-06', :customer_id => 3, :employee_id => 3, :product_id => 2, :quantity => 2 ) Sale.create( :slip_no => 11, :line_no => 2, :processing_date => '2006-06-06', :customer_id => 3, :employee_id => 3, :product_id => 10, :quantity => 1 ) Sale.create( :slip_no => 12, :line_no => 1, :processing_date => '2006-06-12', :customer_id => 2, :employee_id => 6, :product_id => 2, :quantity => 1 ) Sale.create( :slip_no => 13, :line_no => 1, :processing_date => '2006-06-15', :customer_id => 5, :employee_id => 7, :product_id => 9, :quantity => 5 ) Sale.create( :slip_no => 13, :line_no => 2, :processing_date => '2006-06-15', :customer_id => 5, :employee_id => 7, :product_id => 2, :quantity => 2 ) Sale.create( :slip_no => 13, :line_no => 3, :processing_date => '2006-06-15', :customer_id => 5, :employee_id => 7, :product_id => 10, :quantity => 1 )
中間テーブルのデータは SQL で。
INSERT INTO branches_employees (employee_id, branch_id) VALUES (1, 1); INSERT INTO branches_employees (employee_id, branch_id) VALUES (2, 1); INSERT INTO branches_employees (employee_id, branch_id) VALUES (3, 1); INSERT INTO branches_employees (employee_id, branch_id) VALUES (4, 1); INSERT INTO branches_employees (employee_id, branch_id) VALUES (4, 2); INSERT INTO branches_employees (employee_id, branch_id) VALUES (5, 1); INSERT INTO branches_employees (employee_id, branch_id) VALUES (6, 1); INSERT INTO branches_employees (employee_id, branch_id) VALUES (7, 1); INSERT INTO branches_employees (employee_id, branch_id) VALUES (8, 1); INSERT INTO branches_employees (employee_id, branch_id) VALUES (9, 1); INSERT INTO branches_employees (employee_id, branch_id) VALUES (9, 2); INSERT INTO branches_employees (employee_id, branch_id) VALUES (10, 2); INSERT INTO branches_employees (employee_id, branch_id) VALUES (11, 2); INSERT INTO branches_employees (employee_id, branch_id) VALUES (12, 2);
モデル同士の関連付けの定義は次回以降に。
