CODEWITHSUNDEEP
ruby-on-rails
Stuart C
Stuart C

Reputation: 237

Rails Query Associations

I am fairly new to Rails and understand the basics of joins and associations but I am having a challenge with how to best use rails for some general queries. There is a lot of documentation I have reviewed but still not clear on best approach for this query. The models are as follows

User:

has_many :accounts

Account:

belongs_to :user
belongs_to :address, :class_name => "Location", :foreign_key => 'address_id'

Address:

belongs_to :account  (This is really a many to one relationship where an address,
                      can belong to more than one account)

The query is to find all account addresses for the user. A standard sql query would look something like this.

SELECT Users.ID AS Users_ID, Users.Username, Account.acct_name, Address.*
FROM Address INNER JOIN (Users INNER JOIN Account ON Users.ID = Account.user_id) 
ON Address.ID = Account.address_id
WHERE (((Users.ID)=2));

There seems to be a lot of power in rails to do this without a direct sql query. What is the best approach to doing this and are the models correct? Is the foreign key representation in the correct model.

Thank you in advance for your assistance.

Upvotes: 1

Views: 478

Answers (2)

Thomas Klemm
Thomas Klemm

Reputation: 10856

You might be looking for a has_many :through association.

class User < AR::B
  has_many :accounts
  has_many :addresses, through: :accounts
end

You can then query all addresses of the user with @user.addresses.

If you want to take a look at the SQL query that ActiveRecord generates for your query, you may call .to_sql on a query: @user.addresses.to_sql.

Edit: Regarding the rest of your associations setup, you'll need to rework how Address and Account are joined. One approach might be this one.

class Account < AR::B
   belongs_to :user
   belongs_to :address # the accounts table should have an address_id integer column
end

class Address < AR::B
  has_many :accounts
end

For more info on the options you can pass to the associations, see the Rails API Docs.

Upvotes: 1

sockmonk
sockmonk

Reputation: 4255

First, based on what you've described, the Address class should probably have the relationship has_many :accounts rather than belongs_to :account. The general rule of thumb is to use belongs_to when there's a foreign key in that model's table for the belongs_to table. But for this query that really doesn't matter, as you're starting from users.

Something like this should be close to what you want:

User.where(:id => 2).includes(:accounts => :addresses)

See the Rails guide for more information. The squeel gem is also useful for even more advanced querying capabilities.

Upvotes: 1

Related Questions