Join three tables with a common join table

Rails
1

I have the following three tables: user, role and project.

User
has_and_belongs_to_many :roles, :join_table => “projects_roles_users”
has_and_belongs_to_many :projects, :join_table =>
“projects_roles_users”

Role
has_and_belongs_to_many :users, :join_table => “projects_roles_users”
has_and_belongs_to_many :projects, :join_table =>
“projects_roles_users”

Projects
has_and_belongs_to_many :roles, :join_table => “projects_roles_users”
has_and_belongs_to_many :users, :join_table => “projects_roles_users”
attr_accessible :name, :other_stuff, :user_ids, :role_ids

I would like to be able to create a project so that the current user
(and eventually other users) can be linked to it and have a role. I
set up a join table with three columns: project_id, role_id and
user_id; so that a project is linked to a user with a particular role.
However, when I try to create a new project (from the view), the
create does the save in the join table in two times: once with the
project_id and the role_id and a second one with the project_id and
the user_id as follow:

INSERT INTO projects_roles_users (project_id, employee_id) VALUES (11,
14)
INSERT INTO projects_roles_users (role_id, project_id) VALUES (5, 11)

How can I get it to do it so I would get only one INSERT INTO with the
three ids in it?
Thanks in advance for your insights.

Here’s what my view looks like (view/projects/new):

New project

<% form_for(@project,:html => { :multipart => true }) do |f| %>
<%= f.error_messages %>

<p>
<%= f.label :name %><br />
<%= f.text_field :name %>
</p>
<p>
( ... other fields related to the project ... )
</p>
     <%= f.label :role, 'Role' %><br />
     <%= f.collection_select :role_ids,
    Role.find(:all, :order => 'role'),
    :id, :role, {}, :multiple => true %>
      </p>

      <%= f.hidden_field :user_ids, :value => current_user.id %>

<p>
<%= f.submit 'Create' %>
</p>

<% end %>

2

Should I simply do it some other way?

3

I did something similar using an Observer to create the join object.
The reason for this was that I needed to create a join when either one
side was created or the other side was. So in one Observer, I could
watch both models, and respond to either one.

Walter

4

Thanks.
I just got it working by doing this:

has_and_belongs_to_many :users,
:join_table => “projects_roles_users”,
:foreign_key => ‘role_id’,
:association_foreign_key => ‘project_id’,
:insert_sql => ‘INSERT INTO
projects_roles_users(project_id,role_id,user_id)
VALUES(#{id}, #{role_ids}, #{user_ids})’

5

Thanks.
I just got it working by doing this:

has_and_belongs_to_many :users,
:join_table => “projects_roles_users”,
:foreign_key => ‘role_id’,
:association_foreign_key => ‘project_id’,
:insert_sql => ‘INSERT INTO
projects_roles_users(project_id,role_id,user_id)
VALUES(#{id}, #{role_ids}, #{user_ids})’