I have a problem while updating a 3 keys join table. I've been on this for quite a while now and have no idea how to fix it. In short, to be able to get the new project working, I had to set these two habtm declaration (in /models/project.rb):
has_and_belongs_to_many :users, :join_table => "projects_roles_users", :foreign_key => 'property_id', :association_foreign_key => 'user_id', :insert_sql => 'INSERT INTO projects_roles_users(project_id,role_id,user_id) VALUES(#{id}, #{role_ids}, #{user_ids})', :delete_sql => 'DELETE FROM projects_roles_users WHERE projects_roles_users.property_id = #{id}'
has_and_belongs_to_many :roles, :join_table => "projects_roles_users", :foreign_key => 'project_id', :association_foreign_key => 'role_id', :insert_sql => 'INSERT INTO projects_roles_users(project_id,role_id,user_id) VALUES(#{id}, #{role_ids}, #{user_ids})', :delete_sql => 'DELETE FROM projects_roles_users WHERE projects_roles_users.project_id = #{id}'
So the new project works just fine (although I get duplicates in project_roles_users). However, when I want to update the role a user has in a project, I get this error:
Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 1414)' at line 3: INSERT INTO projects_roles_users(project_id,role_id,user_id) VALUES(29, , )
Here's the controller:
def update @project = Project.find(params[:id])
respond_to do |format| if @project.update_attributes(params[:project]) flash[:notice] = 'Project was successfully updated.' format.html { redirect_to user_path(current_user.id) } format.xml { head :ok } else format.html { render :action => "edit" } format.xml { render :xml => @project.errors, :status => :unprocessable_entity } end end end
And here's the view:
<% form_for(@project,:html => { :multipart => true }) do |f| %> <%= f.error_messages %>
<p> <%= f.label :name %><br /> <%= f.text_field :name %> </p>
<p> <% f.label :role, 'Role' %><br /> <%= f.collection_select :role_ids, Role.find(:all, :order => 'position'), :id, :role, {}, :multiple => false %> </p>
<%= f.hidden_field :user_ids, :value => current_user.id %>
<p> <%= f.submit 'Update' %> </p> <% end %>
<%= link_to 'Show', @project %> | <%= link_to 'Back', projects_path %> <%= javascript_tag "$('address').focus()" %>
Finally, here's the log (maybe it'll help you figure it out...):
Processing ProjectsController#update (for 127.0.0.1 at 2011-02-23 08:43:29) [PUT] Parameters: {"project"=>{"price"=>"12$", "city"=>"City", "address"=>"452 Street st.", "user_ids"=>"14", "role_ids"=>["2"], "postalcode"=>"12346", "description"=>"Description", "state"=>"State"}, "commit"=>"Update", "authenticity_token"=>"OKQaW4LPb3Xye6Kbh/W2EgxPWFe4aj26etIEVlCTKTg=", "id"=>"29"} User Columns (0.9ms) SHOW FIELDS FROM `users` User Load (0.1ms) SELECT * FROM `users` WHERE (`users`.`id` = 14) LIMIT 1 Page Load (0.1ms) SELECT * FROM `pages` WHERE (parent_id IS NULL) ORDER BY position Project Columns (0.9ms) SHOW FIELDS FROM `projects` Project Load (0.1ms) SELECT * FROM `projects` WHERE (`properties`.`id` = 29) SQL (0.1ms) BEGIN Role Columns (0.8ms) SHOW FIELDS FROM `roles` Role Load (0.1ms) SELECT * FROM `roles` WHERE (`roles`.`id` = 2) projects_roles_users Columns (0.5ms) SHOW FIELDS FROM `projects_roles_users` Role Load (0.3ms) SELECT * FROM `roles` INNER JOIN `projects_roles_users` ON `roles`.id = `projects_roles_users`.role_id WHERE (`projects_roles_users`.project_id = 29 ) User Load (0.3ms) SELECT `users`.id FROM `users` INNER JOIN `projects_roles_users` ON `users`.id = `projects_roles_users`.user_id WHERE (`projects_roles_users`.project_id = 29 ) SQL (0.1ms) DELETE FROM projects_roles_users WHERE projects_roles_users.project_id = 29 User Load (0.2ms) SELECT `users`.id FROM `users` INNER JOIN `projects_roles_users` ON `users`.id = `properties_roles_users`.user_id WHERE (`projects_roles_users`.project_id = 29 ) SQL (0.1ms) DELETE FROM projects_roles_users WHERE projects_roles_users.project_id = 29 SQL (0.5ms) describe `projects_roles_users` User Load (0.3ms) SELECT `users`.id FROM `users` INNER JOIN `projects_roles_users` ON `users`.id = `projects_roles_users`.user_id WHERE (`projects_roles_users`.project_id = 29 ) SQL (0.0ms) Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 1414)' at line 3: INSERT INTO projects_roles_users(project_id,role_id,user_id) VALUES(29, , ) SQL (0.1ms) ROLLBACK
ActiveRecord::StatementInvalid (Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 1414)' at line 3: INSERT INTO projects_roles_users(project_id,role_id,user_id) VALUES(29, , )): app/controllers/properties_controller.rb:71:in `update' app/controllers/properties_controller.rb:70:in `update'
Rendered rescues/_trace (136.5ms) Rendered rescues/_request_and_response (0.6ms) Rendering rescues/layout (internal_server_error)