Rails - Nov 2005 - handling a three-way table relationship

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.


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

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

On Nov 11, 2005, at 12:57 PM, Larry White 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-----