sql – has_many:通过多个has_one关系?

前端之家收集整理的这篇文章主要介绍了sql – has_many:通过多个has_one关系?前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我正在为我们的铁路教堂写一个导师计划(我仍然很喜欢铁路).

我需要对此进行建模..

contact
has_one :father,:class_name => "Contact"
has_one :mother,:class_name => "Contact"
has_many :children,:class_name => "Contact"
has_many :siblings,:through <Mother and Father>,:source => :children

所以基本上一个对象“兄弟姐妹”需要映射父亲和母亲的所有孩子,不包括对象本身.

这可能吗?

谢谢

丹尼尔

解决方法

有趣的是,看似简单的问题可以得到复杂的答案.在这种情况下,实现反身父/子关系相当简单,但添加父/母和兄弟关系会产生一些曲折.

首先,我们创建表来保存父子关系.关系有两个外键,都指向联系人:

create_table :contacts do |t|
  t.string :name
end

create_table :relationships do |t|
  t.integer :contact_id
  t.integer :relation_id
  t.string :relation_type
end

在关系模型中,我们将父亲和母亲指回联系人:

class Relationship < ActiveRecord::Base
  belongs_to :contact
  belongs_to :father,:foreign_key => :relation_id,:class_name => "Contact",:conditions => { :relationships => { :relation_type => 'father'}}
  belongs_to :mother,:conditions => { :relationships => { :relation_type => 'mother'}}
end

并在Contact中定义反向关联:

class Contact < ActiveRecord::Base
  has_many :relationships,:dependent => :destroy
  has_one :father,:through => :relationships
  has_one :mother,:through => :relationships
end

现在可以创建一个关系:

@bart = Contact.create(:name=>"Bart")
@homer = Contact.create(:name=>"Homer")
@bart.relationships.build(:relation_type=>"father",:father=>@homer)
@bart.save!
@bart.father.should == @homer

这不是很好,我们真正想要的是在一次通话中建立关系:

class Contact < ActiveRecord::Base
  def build_father(father)
    relationships.build(:father=>father,:relation_type=>'father')
  end
end

所以我们可以这样做:

@bart.build_father(@homer)
@bart.save!

要查找联系人的子项,请向Contact和(为方便起见)实例方法添加范围:

scope :children,lambda { |contact| joins(:relationships).\
  where(:relationships => { :relation_type => ['father','mother']}) }

def children
  self.class.children(self)
end

Contact.children(@homer) # => [Contact name: "Bart")]
@homer.children # => [Contact name: "Bart")]

兄弟姐妹是棘手的部分.我们可以利用Contact.children方法并操纵结果:

def siblings
  ((self.father ? self.father.children : []) +
   (self.mother ? self.mother.children : [])
   ).uniq - [self]
end

这是非最佳的,因为father.children和mother.children将重叠(因此需要uniq),并且可以通过编写必要的sql(左侧作为练习:)来更有效地完成,但要记住,self.father.children和self.mother.children在半兄弟姐妹(同一个父亲,不同的母亲)的情况下不会重叠,并且联系人可能没有父亲或母亲.

以下是完整的型号和一些规格:

# app/models/contact.rb
class Contact < ActiveRecord::Base
  has_many :relationships,:through => :relationships

  scope :children,lambda { |contact| joins(:relationships).\
    where(:relationships => { :relation_type => ['father','mother']}) }

  def build_father(father)
    # TODO figure out how to get ActiveRecord to create this method for us
    # TODO failing that,figure out how to build father without passing in relation_type
    relationships.build(:father=>father,:relation_type=>'father')
  end

  def build_mother(mother)
    relationships.build(:mother=>mother,:relation_type=>'mother')
  end

  def children
    self.class.children(self)
  end

  def siblings
    ((self.father ? self.father.children : []) +
     (self.mother ? self.mother.children : [])
     ).uniq - [self]
  end
end

# app/models/relationship.rb
class Relationship < ActiveRecord::Base
  belongs_to :contact
  belongs_to :father,:conditions => { :relationships => { :relation_type => 'mother'}}
end

# spec/models/contact.rb
require 'spec_helper'

describe Contact do
  before(:each) do
    @bart = Contact.create(:name=>"Bart")
    @homer = Contact.create(:name=>"Homer")
    @marge = Contact.create(:name=>"Marge")
    @lisa = Contact.create(:name=>"Lisa")
  end

  it "has a father" do
    @bart.relationships.build(:relation_type=>"father",:father=>@homer)
    @bart.save!
    @bart.father.should == @homer
    @bart.mother.should be_nil
  end

  it "can build_father" do
    @bart.build_father(@homer)
    @bart.save!
    @bart.father.should == @homer
  end

  it "has a mother" do
    @bart.relationships.build(:relation_type=>"mother",:father=>@marge)
    @bart.save!
    @bart.mother.should == @marge
    @bart.father.should be_nil
  end

  it "can build_mother" do
    @bart.build_mother(@marge)
    @bart.save!
    @bart.mother.should == @marge
  end

  it "has children" do
    @bart.build_father(@homer)
    @bart.build_mother(@marge)
    @bart.save!
    Contact.children(@homer).should include(@bart)
    Contact.children(@marge).should include(@bart)
    @homer.children.should include(@bart)
    @marge.children.should include(@bart)
  end

  it "has siblings" do
    @bart.build_father(@homer)
    @bart.build_mother(@marge)
    @bart.save!
    @lisa.build_father(@homer)
    @lisa.build_mother(@marge)
    @lisa.save!
    @bart.siblings.should == [@lisa]
    @lisa.siblings.should == [@bart]
    @bart.siblings.should_not include(@bart)
    @lisa.siblings.should_not include(@lisa)
  end

  it "doesn't choke on nil father/mother" do
    @bart.siblings.should be_empty
  end
end
原文链接:https://www.f2er.com/mssql/79032.html

猜你在找的MsSQL相关文章