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 ?
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.