Associations

找寻数据库中的相关信息，原始的办法是找到一个record，读取它的foreign key，再用这个foreign key做另外一次query。但这样做比较麻烦。rails让我们直接在models里定义表格之间的关系。

Relational database associations

  • One-to-one
    • Classroom has_one :teacher
    • Teacher belongs_to :classroom
  • One-to_many
    • Teacher has_many :courses
    • Course belongs_to :teacher
  • Many-to-many
    • Course has_and_belongs_to_many :students
    • Student has_and_belongs_to_many :courses

When the join is simple, using only foreign keys

One-to-one

  • Unique items a person or thing can have only one of
  • Sometimes used to break up a single table
    • 把不常用的信息分离出来，提高数据库的performance

has_one会自动给你一些method，例如：

Subject has_one :page
Page belongs_to :subject

subject.page #returns the page
subject.page = page #assign a new page to this subject

Example

2.3.0 :001 > subject = Subject.find(1)
  Subject Load (0.6ms)  SELECT  `subjects`.* FROM `subjects` WHERE `subjects`.`id` = 1 LIMIT 1
 => #<Subject id: 1, name: "First Subject", position: 1, visible: true, created_at: "2016-03-02 14:40:33", updated_at: "2016-03-02 14:40:33"> 
2.3.0 :002 > subject.page
  Page Load (0.4ms)  SELECT  `pages`.* FROM `pages` WHERE `pages`.`subject_id` = 1 LIMIT 1
 => nil 
2.3.0 :003 > first_page = Page.new(:name => 'First Page', :permalink => 'first', :position => 1)
 => #<Page id: nil, subject_id: nil, name: "First Page", permalink: "first", position: 1, visible: false, created_at: nil, updated_at: nil> 
2.3.0 :004 > subject.page = first_page
   (0.4ms)  BEGIN
  SQL (0.5ms)  INSERT INTO `pages` (`name`, `permalink`, `position`, `subject_id`, `created_at`, `updated_at`) VALUES ('First Page', 'first', 1, 1, '2016-03-03 01:57:21', '2016-03-03 01:57:21')
   (7.0ms)  COMMIT
 => #<Page id: 1, subject_id: 1, name: "First Page", permalink: "first", position: 1, visible: false, created_at: "2016-03-03 01:57:21", updated_at: "2016-03-03 01:57:21">

subject.page = first_page会自动帮我们save page到数据库并建立关系，把page的subject_id填入1。

之后就可以用subject.page或者first_page.subject来获取对应的object了。

subject.page = nil # 解除关系，会同事把first_page的subject_id更新为nil
subject.page.destroy # 删除这个page
subject.page # 还是会返回这个frozen page，以便提示用户
subject.page(true) # 返回这个subject.page最新的状态 nil

One-to-many Associations

  • Differences from one-to-one associations
    • More commonly used
    • Plural relationship names
    • Returns an array of objects instead of a single object
  • Used when an object has many objects which belong to it exclusively

has_many会自动给你一些method，例如：

Subject has_many :pages
Page belongs_to :subject

subject.pages # return array
subject.pages << page #add page to an subject 如果page没有保存，这步也会把它保存到数据库
subject.pages = [page, page, page] # 并不常用因为这个subject的所有pages都会被重新assign
subject.pages.delete(page) # remove page from subject
subject.pages.destroy(page) # delete from database
subject.pages.clear # remove all the pages
subject.pages.empty? # check if any page belongs to this subject
subject.pages.size # return size of array

Many-to-many associations

Simple

  • Used when an object has many objects which belong to it but not exclusively
  • Compared to one-to-many associations
    • Requires a join table
      • Two foreign keys; index both keys together
      • No primary key column (:id => false)
    • Same instance methods get added to the class

Join Table Naming

  • first_table + _ + second_table
    • Both table names are plural
    • Alphabetical order
    • Default name, can be configured

例如：

  • Project - Collaborator
    • collaborators_projects（不能反过来）
  • BlogPost - Catagory
    • blog_posts_categories
  • AdminUser - Page
    • admin_users_pages
$ rails generate migration CreateAdminUsersPagesJoin

生成migration文件：

class CreateAdminUsersPagesJoin < ActiveRecord::Migration
  def change
    create_table :admin_users_pages, :id => false do |t|
      t.integer 'admin_user_id'
      t.integer 'page_id'
    end
  end
end

admim_user.rb:

class AdminUser < ActiveRecord::Base
    has_and_belongs_to_many :pages
end

page.rb

class Page < ActiveRecord::Base
    belongs_to :subject
    has_and_belongs_to_many :admin_users # :join_table => 'admin_users_pages'
    # if use a different table not by convention of rails 
    # give the table name by add :join_table parameter
end

还可以使用自定义的model名，只要给出rails需要查询的class名。这样我们可以说an admin_user has many pages, a page has many editors。代码更自然，更好懂。

class Page < ActiveRecord::Base
    belongs_to :subject
    has_and_belongs_to_many :editors, :class_name => 'AdminUser'
end

测试：

page = Page.find(2)
page.editors 
AdminUser.all
me = AdminUser.create(:first_name => 'Huang', :last_name => 'Qiang', :username => 'macworks')
page.editors << me
page.editors
me.pages

Rich

假设我们有两个table，一个students，一个courses。每个学生可以选多个课，每个课可以有多个学生。通过simple join，我们可以把单个学生选的课列出来，也可以把每个course的学生名单列出来。

但是，如果我们的course还要纪录他们的seat number, date they signed up, data last attended, how many times absent from the course。

这些信息也属于join table，因为join table是maintaining the presence of the student in the course的object。每个学生，例如Micheal’s participation of chemistry course should be stored in the join table。这是我们需要rich join的情况。

rich join不需要我们按照rails的convention来命名table，所以可以给出一个具有描述性的名词，例如，这里可以命名为course_enrollments。

我们还要建立一个新的model：

  • CourseEnrollment
    • belongs_to :course
    • belongs_to :student
  • Course
    • has_many :course_enrollments
  • Student
    • has_many :course_enrollments
  • Compared to many-to-many simple association
    • Still uses a join table, with two indexed foreign keys
    • Because it has a model, it requires a primary key column. So we can do CRUD operations on this table.
    • Names ending in “-ments” or “ship” work well such as assignments, engagements, authorships, memberships.

这里我们不仅仅纪录AdminUser编辑了哪些sections，每个section的编辑者都有谁。我们还要把每次编辑的summary纪录到join table里面，所以simple join无法满足要求，要使用rich join。

  • AdminUser - Section
    • AdminUser has_many :section_edits
    • Section has_many :section_edits
    • SectionEdit belongs to :admin_user
    • SectionEdit belongs_to :section
    • Everytime an adminuser updates a section, we’ll keep track the edit in a table called SectionEdits, we’ll have a model for it called SectionEdits

操作：

$ rails generate model SectionEdit

编辑生成的data_create_section_edits.rbmigration file:

class CreateSectionEdits < ActiveRecord::Migration
  def up
    create_table :section_edits do |t|
      t.references :admin_user # same as t.integer 'admin_user_id'
      t.references :section
      t.string :summary
      t.timestamps null: false
    end
    add_index :section_edits, ['admin_user_id', 'section_id']
  end
  
  def down
    drop_table :section_edits
  end
end

run migration:

$ rake db:migrate

调整models

class SectionEdit < ActiveRecord::Base
    belongs_to :editor, :class_name => 'AdminUser', :foreign_key => 'admin_user_id'
    belongs_to :section
end

class Section < ActiveRecord::Base
    has_many :section_edits
end

class SectionEdit < ActiveRecord::Base
    belongs_to :admin_user
    belongs_to :section
end

这里SectionEdit里同样把默认的admin_user改成了editor，除了要给出class_name便于rails寻找。还要指明foreign_key，如果foreign_key不是editor_id。

测试：

me = AdminUser.find(1)
me.section_edits
Section.all
section = Section.create(:name => 'Section One', :position => 1)
section.section_edits

要join我们要自己来

2.3.0 :006 > edit = SectionEdit.new
 => #<SectionEdit id: nil, admin_user_id: nil, section_id: nil, summary: nil, created_at: nil, updated_at: nil>

可以看到两个foreign_key, admin_user_id和section_id

编辑edit的summary，加到section里：

2.3.0 :007 > edit.summary = 'Test edit'
 => "Test edit"
2.3.0 :008 > section.section_edits << edit
   (0.3ms)  BEGIN
  SQL (0.5ms)  INSERT INTO `section_edits` (`summary`, `section_id`, `created_at`, `updated_at`) VALUES ('Test edit', 1, '2016-03-03 15:57:12', '2016-03-03 15:57:12')
   (6.9ms)  COMMIT
 => #<ActiveRecord::Associations::CollectionProxy [#<SectionEdit id: 1, admin_user_id: nil, section_id: 1, summary: "Test edit", created_at: "2016-03-03 15:57:12", updated_at: "2016-03-03 15:57:12">]>

这样，section_id以及有了，但是admin_user_id还是nil。

我们可以用

me.section_edits << edit

也可以

edit.editor = me
edit.save

2.3.0 :009 > edit.editor = me
 => #<AdminUser id: 1, first_name: "Huang", last_name: "Qiang", email: "", username: "macworks", hashed_password: nil, created_at: "2016-03-03 14:42:40", updated_at: "2016-03-03 14:42:40">
2.3.0 :010 > edit.save
   (0.2ms)  BEGIN
  SQL (0.6ms)  UPDATE `section_edits` SET `admin_user_id` = 1, `updated_at` = '2016-03-03 16:04:08' WHERE `section_edits`.`id` = 1
   (6.2ms)  COMMIT
 => true

查看，这里特别要注意：

section.section_edits
me.section_edits # 不返回结果，因为我们前面用了edit.editor = me
me.section_edits(true) # 返回更新的结果

还可以做mass assignment

2.3.0 :014 > SectionEdit.create(:editor => me, :section => section, :summary => 'Change')
   (0.3ms)  BEGIN
  SQL (0.4ms)  INSERT INTO `section_edits` (`admin_user_id`, `section_id`, `summary`, `created_at`, `updated_at`) VALUES (1, 1, 'Change', '2016-03-03 16:11:12', '2016-03-03 16:11:12')
   (7.3ms)  COMMIT
 => #<SectionEdit id: 2, admin_user_id: 1, section_id: 1, summary: "Change", created_at: "2016-03-03 16:11:12", updated_at: "2016-03-03 16:11:12">

这样admin_user_id和section_id同时被更新并写入数据库。但是要查看me.section_edits并不会更新，除非me.section_edits(true)

2.3.0 :015 > me.section_edits
 => #<ActiveRecord::Associations::CollectionProxy [#<SectionEdit id: 1, admin_user_id: 1, section_id: 1, summary: "Test edit", created_at: "2016-03-03 15:57:12", updated_at: "2016-03-03 16:04:08">]> 
2.3.0 :016 > me.section_edits(true)
  SectionEdit Load (0.5ms)  SELECT `section_edits`.* FROM `section_edits` WHERE `section_edits`.`admin_user_id` = 1
 => #<ActiveRecord::Associations::CollectionProxy [#<SectionEdit id: 1, admin_user_id: 1, section_id: 1, summary: "Test edit", created_at: "2016-03-03 15:57:12", updated_at: "2016-03-03 16:04:08">, #<SectionEdit id: 2, admin_user_id: 1, section_id: 1, summary: "Change", created_at: "2016-03-03 16:11:12", updated_at: "2016-03-03 16:11:12">]>

Traversing a Rich Association

尽管我们用rich join实现了纪录每次edit的summary。但是我们从editor到section却不能一步到达了。因为中间有一个model。

之前我们可以用

page.editors # [admin_user1, admin_user2]
section.editors # 无法返回editors
# 我们可以
section.section_edits.map {|se| se.editor }
# [admin_user1, admin_user2]，但是这样很麻烦

要使用section.editors是可能的。因为has_and_belongs_to_many做的就是这件事。我们只是要告诉ActiveRecord这层关系，以便它改写要执行的SQL语句。我们用：

  • has_many :through 来实现
    • Allows “reaching across” a rich join
      • Treats rich join like an HABTM join
    • Create a functional rich join first
class AdminUser < ActiveRecord::Base
    has_and_belongs_to_many :pages
    has_many :section_edits
    has_many :sections, :through => :section_edits
    # An admin_user has many sections 
    # if we go through the section_edits to get there
end

class Section < ActiveRecord::Base
    has_many :section_edits
    has_many :editors, :through => :section_edits, :class_name => 'AdminUser'
end

SectionEdit model不用做任何改变。我们只是为AdminUser和Section建立了关系。

测试：

2.3.0 :001 > me = AdminUser.find(1)
  AdminUser Load (0.3ms)  SELECT  `admin_users`.* FROM `admin_users` WHERE `admin_users`.`id` = 1 LIMIT 1
 => #<AdminUser id: 1, first_name: "Huang", last_name: "Qiang", email: "", username: "macworks", hashed_password: nil, created_at: "2016-03-03 14:42:40", updated_at: "2016-03-03 14:42:40"> 
2.3.0 :002 > me.sections
  Section Load (1.8ms)  SELECT `sections`.* FROM `sections` INNER JOIN `section_edits` ON `sections`.`id` = `section_edits`.`section_id` WHERE `section_edits`.`admin_user_id` = 1
 => #<ActiveRecord::Associations::CollectionProxy [#<Section id: 1, page_id: nil, name: "Section One", position: 1, visible: false, content_type: nil, content: nil, created_at: "2016-03-03 15:51:47", updated_at: "2016-03-03 15:51:47">, #<Section id: 1, page_id: nil, name: "Section One", position: 1, visible: false, content_type: nil, content: nil, created_at: "2016-03-03 15:51:47", updated_at: "2016-03-03 15:51:47">]>

你会看到这里me.sections执行的SQL语句用了INNER JOIN。

2.3.0 :003 > section = Section.find(1)  Section Load (0.6ms)  SELECT  `sections`.* FROM `sections` WHERE `sections`.`id` = 1 LIMIT 1
 => #<Section id: 1, page_id: nil, name: "Section One", position: 1, visible: false, content_type: nil, content: nil, created_at: "2016-03-03 15:51:47", updated_at: "2016-03-03 15:51:47"> 
2.3.0 :004 > section.editors
  AdminUser Load (0.7ms)  SELECT `admin_users`.* FROM `admin_users` INNER JOIN `section_edits` ON `admin_users`.`id` = `section_edits`.`admin_user_id` WHERE `section_edits`.`section_id` = 1 => #<ActiveRecord::Associations::CollectionProxy [#<AdminUser id: 1, first_name: "Huang", last_name: "Qiang", email: "", username: "macworks", hashed_password: nil, created_at: "2016-03-03 14:42:40", updated_at: "2016-03-03 14:42:40">, #<AdminUser id: 1, first_name: "Huang", last_name: "Qiang", email: "", username: "macworks", hashed_password: nil, created_at: "2016-03-03 14:42:40", updated_at: "2016-03-03 14:42:40">]>

这样执行的语句比用map来执行的SQL语句效率高。

2.3.0 :005 > section.section_edits.map { |se| se.editor }
  SectionEdit Load (6.2ms)  SELECT `section_edits`.* FROM `section_edits` WHERE `section_edits`.`section_id` = 1
  AdminUser Load (0.2ms)  SELECT  `admin_users`.* FROM `admin_users` WHERE `admin_users`.`id` = 1 LIMIT 1
  AdminUser Load (0.2ms)  SELECT  `admin_users`.* FROM `admin_users` WHERE `admin_users`.`id` = 1 LIMIT 1
 => [#<AdminUser id: 1, first_name: "Huang", last_name: "Qiang", email: "", username: "macworks", hashed_password: nil, created_at: "2016-03-03 14:42:40", updated_at: "2016-03-03 14:42:40">, #<AdminUser id: 1, first_name: "Huang", last_name: "Qiang", email: "", username: "macworks", hashed_password: nil, created_at: "2016-03-03 14:42:40", updated_at: "2016-03-03 14:42:40">]

这样做之后虽然查询起来跟has_and_belongs_to_many的行为很像，但是并不完全一样。例如，我们无法使用下面的语句：

section.editors << bob

rails接受这样做，但是我们无法同时给入join table中的summary的内容。如果summary的内容是required，这样做的问题更严重。所以新edit最好还是使用：

edit = SectionEdit.new
edit.summary = 'Test edit'
edit.editor = me
section.section_edits << edit

When we were just working with the existing information, and you want to traverse across that rich join, then has many through is going to be the best way to do it.