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

I don't think ActiveRecord can handle doing a 2 step join in a has_many relationship. In order for this to work you'll have to join users to team_users to teams to coaches. The through option only allows for one extra join.

Instead you'll have to use the :finder_sql option and write out the full join clause yourself. Not the prettiest thing in the world, but that's how it goes with ActiveRecord when you try to do something out of the ordinary.

Answer 2

It's more of a many-to-many-to-even-more-relationship. I'd just write some sql:

has_many :coaches, :finder_sql => 'SELECT * from coaches, teams_users WHERE 
               coaches.team_id=teams_users.team_id 
               AND teams_users.user_id=#{id}'

Answer 3

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

Answer 4

You could drop the "has_many :coaches, :through => :teams" line in users & then hand-write a coaches method in your User model like so:

def coaches
  ret = []
  teams.each do |t|
    t.coaches.each do |c|
      ret << c
    end
  end
  ret.uniq
end

Answer 5

While I love to write SQL, I don't think it's the ideal solution in this instance. Here's what I ended up doing in the User model:

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

  def canCoach(coachee)
    u = User.find(coachee)

    coaches = u.coaches
    c = []
    coaches.collect do |coach|
      c.push(coach.user_id)
    end

    return c.include?(self.id)
  end

I thought about just doing it all in one fell swoop, but I liked the ability to return an array of coach objects from within the user object. If there's a better way to do it, I'm very interested in seeing the improved code.