Multiple layers of has_many :through

Either this is not supported or I'm just not seeing the forest for the
trees. Here's the model relationship I currently have that works:

class Officer < AR::Base
  has_many :clients, :through => :officer_relationships
end

class Clients < AR::Base
  has_many :officers, :through => :officer_relationships
  has_many :accounts
end

class OfficerRelationships < AR::Base
  belongs_to :client
  belongs_to :officer
end

My problem is that I'm trying to associate the Account's model through
the OfficerRelationship model and the Client model by doing the
following:

class Officer < AR::Base
  has_many :clients, :through => :officer_relationships
  has_many :accounts, :through => :clients
end

or the following:

class Officer < AR::Base
  has_many :clients, :through => :officer_relationships
  has_many :accounts, :through => :clients
end

class OfficerRelationships < AR::Base
  belongs_to :client
  belongs_to :officer
  has_many :accounts, :through => :client
end

None of these work. Am I just trying to get more out of HasManyThrough
than it was designed to? If so, can someone point me in the right
direction as to the best DRY way to do this without replicating the
OfficerRelationship model for every one of the client's accounts?

For what you propose, I don't think the tables could possibly be
configured properly. (Repeating things you already know) has_many
:through for class A, and class B, with relationship AB expects three
tables like so:

table "as"
table "bs"
table "as_bs"
  a_id
  b_id

Which you seem to have for your starting configuration. Now, if you
add has_many :accounts :through => :clients to the Officer then that
means that rails expects the a clients table with:

table "clients"
  officer_id
  account_id
  << whatever other columns are already here >>

and clearly that's not what you want in that table.

I see three options for you:

1. Leave everything as it was when you started. You can select an
officer's accounts by

  (officer.clients.collect { |c| c.accounts }).flatten

If the number of records aren't too large and you :include the accounts
in the officers clients has_many like so

  has_many :clients, :through => officer_relationships, :include =>
:accounts

the performance should be fine

2. If an account belongs to one and only one officer, add those
associations and table column

Officer
  has_many :accounts

Account
  belongs_to :officer

accounts
  officer_id

and you could use before_create, before_update or other callbacks to
ensure that the proper officer for the client is associated with the
account.

3. If an account can belong to more than one officer, you could do some
sort of crazy three-way has_many :through association with a new
OfficerAccounts relationship table. I'm having a hard time visualizing
this one in my head, but I suppose if you were careful you could do
it.... :slight_smile:

Best,

-r