Doctrine 2: How to handle join tables with extra columns

September 6th, 2013

The myth

First off, let me explain that this does not exist:

A join table table (also know as a junction table or cross-reference table) is a table that links 2 (or more) other tables together within the same database by primary key. This means that a join table will only contain foreign keys, there is no place for these extra columns.

But what if I need those other columns in there?

Lets look at the following case:

We have people and companies, and those people can work at one or more companies. We want to know when a person started working at a company and what their salary is.

Many developers imagine a many-to-many relation between people and companies, resulting in the following tables:

• people
• companies
• people_companies (or companies_people) A Doctrine 2 association would look like this:

Person ← many-to-many → Company

So information like started_on and salary must go into people_companies. But this goes against the definition of a join table, and Doctrine 2 won't allow it.

Let me introduce a different way of thinking

What does people_companies actually mean? We can derive little from this name, other than that people and companies are somehow related. We want to have something that represents people working at a company, so lets say they have a "job". And it's that job that people started and get salary for, so it's clear that information belongs there.

The relation between people and companies changes a bit. People are no longer directly related to companies, but that relation is established through jobs. In Doctrine 2 the associations will look like this:

Person ← one-to-many → Job ← many-to-one → Company

As you can see there is no many-to-many association anymore, and a job has become an actual entity.

And this is the key point! When extra columns are introduced to a join table, it ceases to be a join table. It gets meaning and becomes a real object. So treat it as a real object and give it an appropriate name.

So how do I work with this?

Let's setup the entities. I'll give them completely and then go over some things.

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * @ORM\Entity
 * @ORM\Table(name="people")
 */
class Person
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue
     */
    protected $id;

    /**
     * @ORM\Column(type="string")
     */
    protected $name;

    /**
     * @ORM\OneToMany(targetEntity="Job", mappedBy="person", cascade={"persist", "remove"}, orphanRemoval=TRUE)
     */
    protected $jobs;

    public function __construct()
    {
        $this->jobs = new ArrayCollection();
    }

    public function getId()
    {
        return $this->id;
    }

    public function getName()
    {
        return $this->name;
    }

    public function setName($name)
    {
        $this->name = $name;
        return $this;
    }

    public function getJobs()
    {
        return $this->jobs->toArray();
    }

    public function addJob(Job $job)
    {
        if (!$this->jobs->contains($job)) {
            $this->jobs->add($job);
            $job->setPerson($this);
        }

        return $this;
    }

    public function removeJob(Job $job)
    {
        if ($this->jobs->contains($job)) {
            $this->jobs->removeElement($job);
            $job->setPerson(null);
        }

        return $this;
    }

    public function getCompanies()
    {
        return array_map(
            function ($job) {
                return $job->getCompany();
            },
            $this->jobs->toArray()
        );
    }
}

/**
 * @ORM\Entity
 * @ORM\Table(name="jobs")
 */
class Job
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue
     */
    protected $id;

    /**
     * @ORM\ManyToOne(targetEntity="Person", inversedBy="jobs")
     * @ORM\JoinColumn(name="person_id", referencedColumnName="id", nullable=FALSE)
     */
    protected $person;

    /**
     * @ORM\ManyToOne(targetEntity="Company", inversedBy="jobs")
     * @ORM\JoinColumn(name="company_id", referencedColumnName="id", nullable=FALSE)
     */
    protected $company;

    /**
     * @ORM\Column(type="date", name="started_on")
     */
    protected $startedOn;

    /**
     * @ORM\Column(type="integer", name="monthly_salary")
     */
    protected $monthlySalary;

    public function getId()
    {
        return $this->id;
    }

    public function getPerson()
    {
        return $this->person;
    }

    public function setPerson(Person $person = null)
    {
        $this->person = $person;
        return $this;
    }

    public function getCompany()
    {
        return $this->company;
    }

    public function setCompany(Company $company = null)
    {
        $this->company = $company;
        return $this;
    }

    public function getStartedOn()
    {
        return $this->startedOn;
    }

    public function setStartedOn(\DateTime $startedOn)
    {
        $this->startedOn = $startedOn;
        return $this;
    }

    public function getMonthlySalary()
    {
        return $this->monthlySalary;
    }

    public function setMonthlySalary($monthlySalary)
    {
        $this->monthlySalary = $monthlySalary;
        return $this;
    }
}

/**
 * @ORM\Entity
 * @ORM\Table(name="companies")
 */
class Company
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue
     */
    protected $id;

    /**
     * @ORM\Column(type="string")
     */
    protected $name;

    /**
     * @ORM\OneToMany(targetEntity="Job", mappedBy="company", cascade={"persist", "remove"}, orphanRemoval=TRUE)
     */
    protected $jobs;

    public function __construct()
    {
        $this->jobs = new ArrayCollection();
    }

    public function getId()
    {
        return $this->id;
    }

    public function getName()
    {
        return $this->name;
    }

    public function setName($name)
    {
        $this->name = $name;
        return $this;
    }

    public function getJobs()
    {
        return $this->jobs->toArray();
    }

    public function addJob(Job $job)
    {
        if (!$this->jobs->contains($job)) {
            $this->jobs->add($job);
            $job->setCompany($this);
        }

        return $this;
    }

    public function removeJob(Job $job)
    {
        if ($this->jobs->contains($job)) {
            $this->jobs->removeElement($job);
            $job->setCompany(null);
        }

        return $this;
    }

    public function getPeople()
    {
        return array_map(
            function ($job) {
                return $job->getPerson();
            },
            $this->jobs->toArray()
        );
    }
}

Here are some things to note:

• Person::addJob(), Person::removeJob(), Company::addJob() and Company::removeJob() have an extra line that will keep both sides of the associations in sync. So when you add a job to a person, that person will also be set on that job.
• We have the convenience methods Person::getCompanies() and Company::getPeople() for if we need to know (for example) which companies a person works for.
• The associations Job::$person and Job::$company have nullable=FALSE, because we don't want any jobs without a person or company (that makes no sense).
• The associations Person::$jobs and Company::$jobs have cascade={"persist", "remove"}. This will make sure that when you create/delete a job or company, Doctrine 2 will automatically create/delete the associated jobs as well.
• The associations Person::$jobs and Company::$jobs have orphanRemoval=TRUE. When a job is removed from a person or company, the job entity will get deleted from the database.

Now for some work...

First we create a person and company:

$person = new Person();
$person->setName('Jasper N. Brouwer');
$em->persist($person);

$company = new Company();
$company->setName('Future500 B.V.');
$em->persist($company);

$em->flush();

Nothing special here :)

We can now add a new job:

$person = $em->find('Person', 1);
$company = $em->find('Company', 1);

$job = new Job();
$job->setStartedOn(new DateTime('01-10-2009'))
    ->setMonthlySalary(10000);  // I wish!

$person->addJob($job);
$company->addJob($job);

$em->flush();

As you can see there's no need to call $em->persist($job), because the cascade={"persist"} on the associations will take care of persisting the jobs.

We can also create a new person and add a job (or even several) in one go:

$company = $em->find('Company', 1);

$person = new Person();
$person->setName('Ramon de la Fuente');
$em->persist($person);

$job = new Job();
$job->setStartedOn(new DateTime('16-02-2006'))
    ->setMonthlySalary(10000); // He wishes!

$person->addJob($job);
$company->addJob($job);

$em->flush();

We need to call $em->persist($person) off course, but again no need to do it for the job. This will also work for creating new companies.

Now lets remove a person from a company:

$person = $em->find('Person', 1);
$job = $person->getJobs()[0];  // get the (only) job this person has

$person->removeJob($job);

$em->flush();

The job is now deleted from the database, because of the orphanRemoval=TRUE on the associations. So no need to call $em->remove($job) here. This will also work for removing a job from a company.

And finally we delete a company:

$company = $em->find('Company', 1);
$em->remove($company);

$em->flush();

Not only the company is now gone from the database, but all its associated jobs as well. Doctrine 2 took care of this because of the cascade={"remove"} on the associations.

Conclusion

Hopefully this demonstrates that working with these one-to-many/many-to-one setups is not that hard. The trick is to shift you way of thinking. It's not a join table with extra columns, it's a real object with its own data linking 2 other objects.

This is a very basic example, there's room for lots of improvements. But I hope this will get you started on you way to a decent object-graph!

PS: I've also posted this on Stack Overflow.

Jasper N. Brouwer