Error on update for a 3 columns join table

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

<%= 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:inupdate’

Rendered rescues/_trace (136.5ms)
Rendered rescues/_request_and_response (0.6ms)
Rendering rescues/layout (internal_server_error)

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs