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

Question

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]

Answer 1

I think the best thing to do here is to give you the documentation first: http://railsbrain.com/api/rails-2.3.2/doc/index.html
Look up "has_many" (paying attention to :through) and "belongs_to", as well as "has_one", although I don't think you'll use the later.
This blog post will help you with the has_many :through concept -- and I think after that, you'll be set. Let us know if there's anything that's not clear!

class Account < ActiveRecord::Base
  belongs_to :person
end

class Person < ActiveRecord::Base
  has_many :accounts
  has_many :email_addresses :through => :email_address_people
end

class EmailAddress < ActiveRecord::Base
  belongs_to :email_address_type
  belongs_to :person
  has_one :email_address_type
end

class EmailAddressType < ActiveRecord::Base
  has_many :email_addresses :through => :email_address_people
end

I would get started with that. It's not tested, but if we see what breaks, then we can fix it.. :)

Answer 2

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.