Activerecord association question: getting has_many: through work

Question

I am building an application in Ruby on Rails and I am including 3 of my models (and their migration scripts) to show what I am trying to do and what is not working. Here's a summary: I have users in my application that belong to teams, and each team can have several coaches. I want to get a list of trainers that are applicable to the user.

For example, User A may belong to T1 and T2. Teams T1 and T2 can have four different coaches, and one coach together. I would like to get a list of trainers simply by saying:

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

Here are my migration scenarios and associations in my models. Am I doing something wrong 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 whole 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 

Here's what happens when I try to pull the trainers out:

 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))

I missed something in my: through the proposal? Is my design completely off? Can someone point me in the right direction?

Answer 1

You cannot do has_many: two times in a row. This will tell you that this is an invalid connection. If you do not want to add finder_sql as described above, you can add a method that mimics what you are trying to do.

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

Answer 2

This is more of a many-to-many-to-yet-relationship. I would 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

I do not think that ActiveRecord can handle the two-stage connection in the has_many relationship. For this to work, you will have to join the team_users team members for the coaches. The pass-through option allows only one additional connection.

Instead, you have to use the parameter: finder_sql and write out a complete join proposal yourself. Not the prettiest thing in the world, but how it happens with ActiveRecord when you try to do something unusual.

Answer 4

You can remove the line "has_many: trainers ,: through =>: teams" from users, and then manually write the trainers method in your user model as follows:

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

Answer 5

Although I like to write SQL, I do not think that this is the ideal solution in this case. Here is what I did 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 doing it all in one fell swoop, but I liked the opportunity to return an array of trainer objects from a user object. If there is a better way to do this, I am very interested in improving the code.