# Has\_many, :finder\_sql, setting attributes

**URL:** <https://www.ruby-forum.com/t/has-many-finder-sql-setting-attributes/182297>\
**Category:** Rails\
**Created:** [January 26, 2010, 9:29am UTC](https://www.ruby-forum.com/t/has-many-finder-sql-setting-attributes/182297 "2010-01-26T09:29:31Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![David\_van\_Geest](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/43a26b/32.png) [@David\_van\_Geest](https://www.ruby-forum.com/u/David_van_Geest)\
**Post date:** [January 26, 2010, 9:29am UTC](https://www.ruby-forum.com/t/has-many-finder-sql-setting-attributes/182297/1 "2010-01-26T09:29:31Z")

</div>

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