『エンタープライズRails』に学ぶ企業ユーザのためのRailsアーキテクチャ
•Download as KEY, PDF•
5 likes•3,368 views
This document discusses database modeling for a system with companies, departments, and employees. It describes setting up composite primary keys to link the three tables together and using associations and joins to efficiently query across the tables. Methods are defined on the Company model to count employees. A view is created to select large companies with over 1000 employees.
![Department
class Department < ActiveRecord::Base
set_primary_keys :company_id,
:department_id
has_many :employees,
:foreign_keys =>
[:company_id,
:department_id]](https://image.slidesharecdn.com/railsrails-090717094844-phpapp01/85/Rails-Rails-33-320.jpg)
![Company
class Company < ActiveRecord::Base
def employees_size
Employee.count(
:conditions =>
['company_id = ?', id])
end](https://image.slidesharecdn.com/railsrails-090717094844-phpapp01/85/Rails-Rails-34-320.jpg)
『エンタープライズRails』に学ぶ企業ユーザのためのRailsアーキテクチャ
- 3. 80 60 40 20 0 2007 2008 2009
- 11. Po w ere d by vs
- 17. class Employee < ActiveRecord::Base validates_presence_of :name CREATE TABLE employees ( name VARCHAR(40) NOT NULL,
- 18. class Employee < ActiveRecord::Base validates_length_of :login, :minimum => 5 CREATE TABLE employees ( login VARCHAR(10) check(length(login) > 4),
- 19. begin emp = Employee.new(:login => '') emp.save_with_validation(false) fail rescue ActveRecord::StatementInvalid end
- 20. Departments Employees
- 21. class Employee < ActiveRecord::Base belongs_to :department validates_presence_of :department CREATE TABLE employees ( department_id INTEGER NOT NULL REFERENCES departments(id),
- 24. Companies Departments Employees
- 25. has_many :though company.employees.size SELECT count(*) AS count_all FROM employees INNER JOIN departments ON employees.department_id = departments.id WHERE departments.company_id = 1
- 26. SELECT count(*) AS count_all FROM employees WHERE company_id = 1 Companies ? Departments Employees
- 27. company_id department_id Companies Departments Employees id id id company_id company_id department_id
- 28. Companies Departments Employees id company_id id department_id company_id department_id
- 29. departments CREATE TABLE departments( company_id INTEGER NOT NULL REFERENCES companies(id), department_id SERIAL NOT NULL, PRIMARY KEY (company_id, department_id)
- 30. employees CREATE TABLE employees( company_id INTEGER NOT NULL REFERENCES companies(id), department_id INTEGER NOT NULL, FOREIGN KEY (company_id, department_id) REFERENCES department (company_id, department_id)
- 31. departments company_id department_id employees Companies Departments Employees id company_id id department_id company_id department_id
- 32. # gem install composite_primary_keys
- 33. Department class Department < ActiveRecord::Base set_primary_keys :company_id, :department_id has_many :employees, :foreign_keys => [:company_id, :department_id]
- 34. Company class Company < ActiveRecord::Base def employees_size Employee.count( :conditions => ['company_id = ?', id]) end
- 38. CREATE VIEW big_campanies AS SELECT * FROM companies WHERE id IN (SELECT company_id FROM (SELECT compay_id, count(id) AS c FROM employees GROUP BY company_id) AS t WEHRE c > 1000)
- 39. BigCampany class BigCampany < ActiveRecord::Base has_many :department end
- 40. SELECT * FROM (SELCT * FROM ...)