How to create Rails models with several complex associations / unions?

Question

I am trying to figure out how to create ActiveRecord models with associations that can produce 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 structure of the table is as follows and assumes that each table has an id for the usual ActiveRecord convention:

bills
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 models to be fully functional and not limited to things like : find_by_sql .

How to create related models that make this possible?

Thanks!
Chris Benson
[e-mail]

Answer 1

Try the following:

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 an Account in hand account.account_emails object makes two database calls:

• Get person using id

• Get accounts for a person

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

Answer 2

I think the best thing here is to give you the documentation: http://railsbrain.com/api/rails-2.3.2/doc/index.html
Look at "has_many" (paying attention to: through) and "belongs", as well as "has_one", although I do not think that you will use it later.
This blog post will help you with the has_many idea: through the concept - and I think that after that you will be installed. Let us know if there is something incomprehensible!

 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 start with this. It was not tested, but if we see what interruptions, then we can fix it .. :)