Hi all,
My question is somewhat complicated, but bear with me. My project has a
number of models: User, Program, and Team. Users belong to multiple
Programs. Programs have multiple teams, but Teams belong to one
program. For each Program a User belongs to, he can belong to multiple
Teams. (Think of this in an athletic context where users are athletes,
programs are universities). So my models look something like this:
class User < ActiveRecord::Base
has_many :user_programs
has_many :programs, :through => :user_programs
has_many :user_teams, :finder_sql => ‘SELECT ut.* FROM user_teams AS
ut
JOIN user_programs AS up ON
up.user_program_id = ut.user_program_id
JOIN users AS u ON up.user_id =
u.user_id
WHERE u.user_id = #{id}’
has_many :teams, :finder_sql => ‘SELECT t.* FROM teams AS t
JOIN user_teams AS ut ON ut.team_id =
t.team_id
JOIN user_programs AS up ON
up.user_program_id = ut.user_program_id
JOIN users AS u ON up.user_id =
u.user_id
WHERE u.user_id = #{id}’
end
class UserProgram < ActiveRecord::Base
belongs_to :user
belongs_to :program
has_many :user_teams
has_many :teams, :through => :user_teams
end
class Program < ActiveRecord::Base
has_many :teams
has_many :user_programs
has_many :users, :through => :user_programs
end
class UserTeam < ActiveRecord::Base
belongs_to :user_program
belongs_to :team
end
class Team < ActiveRecord::Base
belongs_to :program
has_many :user_teams
has_many :users, :finder_sql => ‘SELECT u.* FROM users AS u
JOIN user_programs AS up ON u.user_id
= up.user_id
JOIN user_teams AS ut ON
up.user_program_id = ut.user_program_id
JOIN teams as t ON ut.team_id =
t.team_id
WHERE t.team_id = #{id}’
has_many :user_programs, :through => :user_teams
end
Now what I would like to do is be able to edit the users on a team from
the Edit Team view using checkboxes, that is, include something like
this in my edit form:
<% for user in @program_users %>
<%= check_box_tag “team[user_ids][]”, user.id,
@team.users.include?(user) %>
<%= user.name %>
<% end %>
And then in my TeamsController, do the standard :
def update
# in case all users are deselected, create empty array as it won’t
exist
params[:team][:user_ids] ||= []
@team = Team.find(params[:id])
if @team.update_attributes(params[:team])
…
end
When I try this however, Rails (not unsurprisingly) produces some pretty
strange SQL:
When removing users 1237 and 1238 from team 1:
Mysql::Error: Unknown column ‘team_id’ in ‘where clause’: UPDATE users
SET team_id = NULL WHERE (team_id = 1 AND user_id IN (1237,1238))
When adding user 1242 to team 1:
UPDATE users SET perishable_token = ‘z7imelr6kqnwiD1dQtTt’ WHERE
user_id = 1242
(which is even more crazy… maybe something else is going on there…)
Clearly that approach is not going to work without some changes…
Note that everything worked fine when I didn’t include any mention of
program in UserTeam, and it was just a simple has_many :through join
table for users and teams (I want to change the relationship so that
when a user leaves a program and the UserProgram record is deleted, the
MySQL foreign key constraints will cause the UserTeam records to be
deleted as well).
What’s the best way of editing these team members? Let me know if I’m
completely off-base here. Thanks!
-David