Activerecord association question: getting has_many :through to work

Question

I'm building an app in Ruby on Rails, and I'm including 3 of my models (and their migration scripts) to show what I'm trying to do, and what isn't working. Here's the rundown: I have users in my application that belong to teams, and each team can have multiple coaches. I want to be able to pull a list of the coaches that are applicable to a user.

For instance, User A could belong to teams T1 and T2. Teams T1 and T2 could have four different coaches each, and one coach in common. I'd like to be able to pull the list of coaches by simply saying:

u = User.find(1)
coaches = u.coaches

Here are my migration scripts, and the associations in my models. Am I doing something incorrectly in my design? Are my associations correct?

class CreateUsers < ActiveRecord::Migration
  def self.up
    create_table :users do |t|
      t.column :login, :string, :default => nil
      t.column :firstname, :string, :default => nil
      t.column :lastname, :string, :default => nil
      t.column :password, :string, :default => nil
      t.column :security_token, :string, :default => nil
      t.column :token_expires, :datetime, :default => nil
      t.column :legacy_password, :string, :default => nil
    end
  end

  def self.down
    drop_table :users
  end
end

class CreateTeams < ActiveRecord::Migration
  def self.up
    create_table :teams do |t|
      t.column :name, :string
    end
  end

  def self.down
    drop_table :teams
  end
end

class TeamsUsers < ActiveRecord::Migration
  def self.up
    create_table :teams_users, :id => false do |t|
      t.column :team_id, :integer
      t.column :user_id, :integer
      t.column :joined_date, :datetime
    end
  end

  def self.down
    drop_table :teams_users
  end
end

Here are the models (not the entire file):

class User < ActiveRecord::Base

  has_and_belongs_to_many :teams
  has_many :coaches, :through => :teams

class Team < ActiveRecord::Base
  has_many :coaches
  has_and_belongs_to_many :users

class Coach < ActiveRecord::Base
  belongs_to :teams
end

This is what happens when I try to pull the coaches:

u = User.find(1)
=> #<User id: 1, firstname: "Dan", lastname: "Wolchonok">
>> u.coaches
ActiveRecord::StatementInvalid: Mysql::Error: #42S22Unknown column 'teams.user_id' in 'where clause': SELECT `coaches`.* FROM `coaches`    INNER JOIN teams ON coaches.team_id = teams.id    WHERE ((`teams`.user_id = 1)) 

Here's the error in sql:

Mysql::Error: #42S22Unknown column 'teams.user_id' in 'where clause': SELECT coaches.* FROM coaches INNER JOIN teams ON coaches.team_id = teams.id WHERE ((teams.user_id = 1))

Am I missing something in my :through clause? Is my design totally off? Can someone point me in the right direction?

Answer 1

You can't do a has_many :through twice in a row. It'll tell you that its an invalid association. If you don't want to add finder_sql like above, you can add a method that mimics what you're trying to do.

  def coaches
    self.teams.collect do |team|
      team.coaches
    end.flatten.uniq
  end