Doctrine 2 Many to Many with Three Entities and a Join Table

Question

I have 3 tables

People
 - id -pk
 - name

Roles
 - id -pk
 - roleName

Events
 - id -pk
 - title

and a join table

event_performers
 - event_id -pk
 - role_id -pk
 - people_id -pk

An Event has many Roles. A Role is performed by a Person. A Role is associated with many Events. A Person can perform many Roles.

So what I would like is that when I get an event I can access a collection of Roles related to that event, and from the Roles I can get the Person who performed the Role.

I'm not sure how I would go about mapping this in Doctrine 2 ?

Answer 1

In case someone is as newbie as I am, I will just add a few annotations to this great answer by @cantera:

In each of the three entities, you have to add the code he suggested, only take care that "ORM\" has to be included before "ManyToOne" and "JoinColumn". I also added "@var" annotations just to clarify as much as posible:

In your Entity name="eventsPeopleRoles", add the reference of each of the three entities:

/**
 * @var Events $event
 *
 * @ORM\ManyToOne(targetEntity="Events", inversedBy="eventsPeopleRoles")
 * @ORM\JoinColumn(name="event_id", referencedColumnName="id", nullable=false)
 */
private $event;

/**
 * @var Events $people
 *
 * @ORM\ManyToOne(targetEntity="Person", inversedBy="eventsPeopleRoles")
 * @ORM\JoinColumn(name="person_id", referencedColumnName="id", nullable=false)
 */
private $people;

/**
 * @var Role $role
 *
 * @ORM\ManyToOne(targetEntity="Role", inversedBy="eventsPeopleRoles")
 * @ORM\JoinColumn(name="role_id", referencedColumnName="id", nullable=false)
 */
private $role;

In your Entity name="Events"

/**
 * @var ArrayCollection $eventsPeopleRoles
 *
 * @ORM\OneToMany(targetEntity="EventsPeopleRoles", mappedBy="event")
 */
private $eventsPeopleRoles;

In your Entity name="Person"

/**
 * @var ArrayCollection $eventsPeopleRoles
 *
 * @ORM\OneToMany(targetEntity="EventsPeopleRoles", mappedBy="people")
 */
private $eventsPeopleRoles;

In your Entity name="Role"

/**
 * @var ArrayCollection $eventsPeopleRoles
 *
 * @ORM\OneToMany(targetEntity="EventsPeopleRoles", mappedBy="roles")
 */
private $eventsPeopleRoles;

Answer 2

@cantera25's solution is right.

I want to add a thought to it.

Usually, if your join entity will be joining more than two entities together, that is an indication that it has quite an important role in your information architecture and should be renamed.

For example, an application I am working on for a riding stables has a Booking entity.

Each Booking has at least one Rider who is riding one Horse for that booking.

I originally designed an entity called BookingRiderHorse, to join these three together.

Needless to say, that would have been quite messy and difficult to understand later on when I revisit the code.

I renamed the former Booking entity to Ride, and renamed the BookingRiderHorse entity to Booking.

Now in the business logic, Bookings are created and must have an existing Ride, Horse and Rider record. Each Booking has just one Horse and Rider, but each Ride can have many Bookings.

This is the same as using a join table with a funny name, but is far easier to understand and means I can work on business logic without needing to think about how the joins work.

Answer 3

I ran into this same issue about a week ago. I polled the Doctrine IRC channel users for the best solution (or at least the one that's most commonly practiced). Here's how it's done:

Create a new entity named something like EventsPeopleRoles with three properties mapped using @ManyToOne, $event, $person, and $role.

Each association should be mapped similar to this:

/**
 * @ManyToOne(targetEntity="Events", inversedBy="eventsPeopleRoles")
 * @JoinColumn(name="event_id", referencedColumnName="id", nullable=false)
 */
private $event;

Then in each of the three related entities, code the inverse side of the association like this:

/**
 * @OneToMany(targetEntity="EventsPeopleRoles", mappedBy="event")
 */
private $eventsPeopleRoles;

You then have the choice of either adding an $id property to your "join entity" or using a composite primary key as described here and adding a unique constraint annotation in the entity class definition. Note that composite foreign keys are only supported beginning in Doctrine 2.1.

I was skeptical about this solution because I don't like the idea of creating an entity only for the purposes of a join. It seems like cheating or at least in contrast to ORM design principles. But I am confident this is the accepted solution (for now at least) among Doctrine experts.