Article

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

Want to learn more? Join us at Future500

We are always looking for new colleagues that want to learn with us.
Check out our vacancies or write us at count-me-in@future500.nl