Handling a three-way table relationship

Rails
1

Hi,

I have three tables that are related in a fourth. The “person”, “role”
and
“team” tables are connected in the table “person_role_team”, which
contains
the primary keys from each of the other tables and let’s me say things
like
“Mark plays the captain role on the red team” (or whatever).

I need to do finds, inserts, updates and deletes in this table. Does
ActiveRecord provides direct support for this kind of thing?

It would be very simple to do this with hand-written sql statements, but
I’m
not sure how to pass them to the database. Is there some kind of an
‘execute-sql’ statement that takes arbitrary raw sql.

2

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Nov 11, 2005, at 12:57 PM, Larry W. wrote:

I have three tables that are related in a fourth. The “person”,
“role” and “team” tables are connected in the table
“person_role_team”, which contains the primary keys from each of
the other tables and let’s me say things like “Mark plays the
captain role on the red team” (or whatever).

I would model this relation as Participant.

class Participant < ActiveRecord::Base

Easier to avoid id collision in joins.

self.primary_key = ‘participant_id’

belongs_to :person
belongs_to :role
belongs_to :team
end

class Person < ActiveRecord::Base
has_many :participants

For convenience:

has_and_belongs_to_many :teams, :join_table => ‘participants’
has_and_belongs_to_many :roles, :join_table => ‘participants’
end

class Team < ActiveRecord::Base
has_many :participants

For convenience:

has_and_belongs_to_many :people, :join_table => ‘participants’
has_and_belongs_to_many :roles, :join_table => ‘participants’
end

class Role < ActiveRecord::Base
has_many :participants

For convenience:

has_and_belongs_to_many :people, :join_table => ‘participants’
has_and_belongs_to_many :teams, :join_table => ‘participants’
end

Then traverse the associations as you like.

On a side note, I really wish that
redteam.roles.find_by_name(‘captain’).person
worked out of the box (the habtm be smart and see that its join table
is already represented by a model with associations of its own.)

jeremy
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (Darwin)

iD8DBQFDdQr+AQHALep9HFYRAnKpAJ9ESWENUQghlvPTPL5S9Uv7ZrA2bACgnnPd
tkMdaQBTwXoOGSjRKhbFmvI=
=ga6T
-----END PGP SIGNATURE-----