Double join with habtm in ActiveRecord

Question

I have a weird situation involving the need of a double inner join. I have tried the query I need, I just don't know how to make rails do it.

The Data

• Account (has_many :sites)
• Site (habtm :users, belongs_to :account)
• User (habtm :sites)

Ignore that they are habtm or whatever, I can make them habtm or has_many :through.

I want to be able to do

@user.accounts

or

@account.users

Then of course I should be able to do

@user.accounts < @some_other_account

And then have @user.sites include all the sites from @some_other_account.

I've fiddled with habtm and has_many :through but can't get it to do what I want.

Basically I need to end up with a query like this (copied from phpmyadmin. Tested and works):

SELECT accounts.* 
FROM accounts
INNER JOIN sites ON sites.account_id = accounts.id
INNER JOIN user_sites ON sites.id = user_sites.site_id
WHERE user_sites.user_id = 2

Can I do this? Is it even a good idea to have this double join? I am assuming it would work better if users had the association with accounts to begin with, and then worry about getting @user.sites instead, but it works better for many other things if it is kept the way it is (users <-> sites).

Answer 1

I think it's best to create custom methods for this rather than trying to shoe-horn it into an association. For example.

# in user.rb
def accounts
  Account.all(:include => {:sites => :users}, :conditions => ["users.id=?", self])
end

def add_account(other_account)
  other_account.sites.each do |site|
    self.sites << site
  end
end

# in account.rb
def users
  User.all(:include => {:sites => :account}, :conditions => ["accounts.id=?", self])
end

Untested, but that should work for either a HABTM or has_many :through association. There are some optimizations you can make with the query depending on which approach you go with.

Someday we might get support for deeply nested has_many :through which would handle some of this.

Answer 2

This might be useful to you

http://github.com/ianwhite/nested_has_many_through