Forum: Ruby on Rails handling a three-way table relationship

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
ljw1001 (Guest)
on 2005-11-12 12:50
(Received via mailing list)
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.
jeremy (Guest)
on 2005-11-12 12:50
(Received via mailing list)
-----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-----
This topic is locked and can not be replied to.