How to create Rails models with multiple associations / complex joins?

advertisements

I am trying to figure out how to create ActiveRecord models with associations that can yield the same results as this SQL query:

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';

The table structure is as follows, and assumes each table has an id per normal ActiveRecord convention:

accounts
id : int
person_id : int
login : string

people
id : int
first_name : string
last_name : string

email_address_people
id : int
person_id : int
email_address_id : int
email_address_type_id : int

email_addresses
id : int
email_address : string

email_address_types
id : int
email_address_type: string

I need the models to be fully functional, and not limited by things like :find_by_sql.

How do I create the associated models that make this possible?

Thanks!
Chris Benson
[email protected]


Try this:

Your model classes:

  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

Your account model:

  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

Assuming you have the Account object in hand account.account_emails makes two database calls:

  • Get the person using a id

  • Get the account emails for the person

Going directly to the database(i.e. account.account_emails2) is the fastest option, but it is not the Rails way.