ruby-on-rails - 如何创建具有多个复杂关联/连接的 Rails 模型？

Question

我想弄清楚如何创建具有关联的 ActiveRecord 模型，这些模型可以产生与此 SQL 查询相同的结果:

SELECT login, first_name, last_name, email_address 
FROM accounts 
INNER JOIN people ON person.id = accounts.person_id
INNER JOIN email_address_people ON person.id = email_address_people.person_id 
INNER JOIN email_addresses ON email_address.id = email_address_people.email_address_id
INNER JOIN email_address_types ON email_address_types.id = email_address_people.email_address_type_id 
WHERE email_address_types.email_address_type = 'account';

表结构如下，假设每张表都有一个 id按照正常的 ActiveRecord 约定:

帐号
编号:整数
person_id : 整数
登录:字符串

人
编号:整数
名字:字符串
姓氏:字符串

email_address_people
编号:整数
person_id : 整数
email_address_id : int
email_address_type_id : int

电子邮件地址
编号:整数
电子邮件地址:字符串

email_address_types
编号:整数
email_address_type:字符串

我需要模型功能齐全，并且不受诸如 之类的限制:find_by_sql .

如何创建使这成为可能的关联模型？

谢谢!
克里斯·本森
chris@chrisbenson.com

Answer 1

尝试这个:

您的模型类:

  class EmailAddress < ActiveRecord::Base
  end

  class EmailAddressType < ActiveRecord::Base
  end

  class People < ActiveRecord::Base
    has_many :accounts
    has_many :email_address_people
    has_many :email_addresses, :through => :email_address_people

    has_many :account_email_address_people, 
                   :class_name => "EmailAddressPeople", 
                   :conditions => "email_address_type = 'account'"

    has_many :account_email_addresses, 
                   :through => :account_email_address_people

  end

  class EmailAddressPeople < ActiveRecord::Base
    belongs_to :person
    belongs_to :email_address
    belongs_to :email_address_type
  end

您的账户模式:

  class Account < ActiveRecord::Base
    belongs_to :person

    # now to the actual method
    def account_emails
      person.account_email_addresses.map do |email|
        [login, person.first_name, person.last_name, email.email_address]
      end
    end

    # Brute force SQL if you prefer
    def account_emails2
       sql = "YOUR SQL HERE"
       self.connection.select_values(sql)
    end

  end

假设您手头有 Account 对象 account.account_emails进行两次数据库调用:

获取使用 id 的人

获取此人的帐户电子邮件

直接进入数据库(即 account.account_emails2 )是最快的选择，但这不是 Rails 的方式。