ManyToMany... with Extra Fields on the Join Table?

Keep on Learning!

If you liked what you've learned so far, dive in!
Subscribe to get access to this tutorial plus
video, code and script downloads.

Start your All-Access Pass
Buy just this tutorial for $12.00

With a Subscription, click any sentence in the script to jump to that part of the video!

Login Subscribe

The ManyToMany relationship is unique in Doctrine because Doctrine actually creates & manages a table - the join table - for us. This is the only time in Doctrine where we have a table without a corresponding entity class.

But what if we needed to add more columns to this table? Like a tagged_at DateTime column? Excellent question! And the answer is... that's not possible! I'm serious! But, it's by design. As soon as you need even one extra column on this join table, you need to stop using a ManyToMany relationship. Instead, you need to create an entity for the join table and manually relate that entity to Question and Tag.

Let's see what this looks like. But, it's actually easier to do this from the beginning than to try to refactor an existing ManyToMany relationship. So before you create a ManyToMany, try to think if you might need extra columns in the future. And if you will need them, start with the solution that we're about to see.

Undoing the ManyToMany

Ok, step 1: I'm actually going to hit the rewind button on our code and remove the ManyToMany. In Question, delete everything related to tags. So, the property, the constructor and the getter and setter methods.

Inside of Tag, do the same thing for questions: delete the methods and, on top, the property and the entire constructor.

So we still have a Question entity and a Tag entity... but they're no longer related.

Generating the Join Entity

Now we're going to put this relationship back, but with a new entity that represents the join table. Find your terminal and run:

symfony console make:entity

Let's call this entity QuestionTag, but if there's a more descriptive name for your situation, use that. This entity will have at least two properties: one for the relation to Question and another for the relation to Tag.

Start with the question property... and use the relation type to trigger the wizard. This will relate to the Question entity... and it's going to be a ManyToOne: each QuestionTag relates to one Question and each Question could have many QuestionTag objects.

Is the property allowed to be nullable? No... and then do we want to add a new property to Question so we can say $question->getQuestionTags()? That probably will be handy, so say yes. Call that property $questionTags. Finally, say "no" to orphan removal.

Cool! The other property - the tag property - will be exactly the same: a ManyToOne, related to Tag, say "no" for nullable and, in this case, I'm going to say "no" to generating the other side of the relationship. I'm doing this mostly so we can see an example of a relationship where only one side is mapped. But we also aren't going to need this shortcut method for what we're building. So say "no".

And... perfect! That is the minimum needed in the new QuestionTag entity: a ManyToOne relationship to Question and a ManyToOne relationship to Tag. So now we can start adding whatever other fields we want. I'll add taggedAt... and make this a datetime_immutable property that is not nullable in the database. Hit enter a one more time to finish the command.

Ok! Let's go check out the new class: src/Entity/QuestionTag.php. It looks... beautifully boring! It has a question property that's a ManyToOne to Question, a tag property that's a ManyToOne to Tag and a taggedAt date property.

... lines 1 - 2
namespace App\Entity;
use App\Repository\QuestionTagRepository;
use Doctrine\ORM\Mapping as ORM;
/**
* @ORM\Entity(repositoryClass=QuestionTagRepository::class)
*/
class QuestionTag
{
/**
* @ORM\Id
* @ORM\GeneratedValue
* @ORM\Column(type="integer")
*/
private $id;
/**
* @ORM\ManyToOne(targetEntity=Question::class, inversedBy="questionTags")
* @ORM\JoinColumn(nullable=false)
*/
private $question;
/**
* @ORM\ManyToOne(targetEntity=Tag::class)
* @ORM\JoinColumn(nullable=false)
*/
private $tag;
/**
* @ORM\Column(type="datetime_immutable")
*/
private $taggedAt;
public function getId(): ?int
{
return $this->id;
}
public function getQuestion(): ?Question
{
return $this->question;
}
public function setQuestion(?Question $question): self
{
$this->question = $question;
return $this;
}
public function getTag(): ?Tag
{
return $this->tag;
}
public function setTag(?Tag $tag): self
{
$this->tag = $tag;
return $this;
}
public function getTaggedAt(): ?\DateTimeImmutable
{
return $this->taggedAt;
}
public function setTaggedAt(\DateTimeImmutable $taggedAt): self
{
$this->taggedAt = $taggedAt;
return $this;
}
}

Inside Question... scroll all the way up. Because we also decided to map this side of the relationships, this has a OneToMany relationship to the join entity.

... lines 1 - 16
class Question
{
... lines 19 - 59
/**
* @ORM\OneToMany(targetEntity=QuestionTag::class, mappedBy="question")
*/
private $questionTags;
public function __construct()
{
... line 67
$this->questionTags = new ArrayCollection();
}
... lines 70 - 191
/**
* @return Collection|QuestionTag[]
*/
public function getQuestionTags(): Collection
{
return $this->questionTags;
}
public function addQuestionTag(QuestionTag $questionTag): self
{
if (!$this->questionTags->contains($questionTag)) {
$this->questionTags[] = $questionTag;
$questionTag->setQuestion($this);
}
return $this;
}
public function removeQuestionTag(QuestionTag $questionTag): self
{
if ($this->questionTags->removeElement($questionTag)) {
// set the owning side to null (unless already changed)
if ($questionTag->getQuestion() === $this) {
$questionTag->setQuestion(null);
}
}
return $this;
}
}

But there were no changes to the Tag entity, since we decided not to map the other side of that relationship.

Back in QuestionTag, before we generate the migration, let's give our $taggedAt a default value. Create a public function __construct() and, inside, say $this->taggedAt = new \DateTimeImmutable() which will default to "now".

... lines 1 - 10
class QuestionTag
{
... lines 13 - 36
public function __construct()
{
$this->taggedAt = new \DateTimeImmutable();
}
... lines 41 - 81
}

How this Looks Different / the Same in the Database

Ok - migration time! At your terminal, make it:

symfony console make:migration

And then go open up the new file... cause this is really cool! It looks like there are a lot of queries to change from the old ManyToMany structure to our new structure.

... lines 1 - 9
/**
* Auto-generated Migration: Please modify to your needs!
*/
final class Version20210907192236 extends AbstractMigration
{
... lines 15 - 19
public function up(Schema $schema): void
{
// this up() migration is auto-generated, please modify it to your needs
$this->addSql('ALTER TABLE question_tag DROP FOREIGN KEY FK_339D56FB1E27F6BF');
$this->addSql('ALTER TABLE question_tag DROP FOREIGN KEY FK_339D56FBBAD26311');
$this->addSql('ALTER TABLE question_tag ADD id INT AUTO_INCREMENT NOT NULL, ADD tagged_at DATETIME NOT NULL COMMENT \'(DC2Type:datetime_immutable)\', DROP PRIMARY KEY, ADD PRIMARY KEY (id)');
$this->addSql('ALTER TABLE question_tag ADD CONSTRAINT FK_339D56FB1E27F6BF FOREIGN KEY (question_id) REFERENCES question (id)');
$this->addSql('ALTER TABLE question_tag ADD CONSTRAINT FK_339D56FBBAD26311 FOREIGN KEY (tag_id) REFERENCES tag (id)');
}
... lines 29 - 41
}

But look closer. We already had a question_tag table thanks to the ManyToMany relationship. So we don't need to drop that table and create a new one: all the migration needs to do is tweak it. It drops the question_id and tag_id foreign key constraint from the table... but then adds them back down here. So the first two lines and last two lines cancel each other out.

This means that the only real change is ALTER TABLE question_tag to add a true id auto-increment column and the tagged_at column. Yup, we just did a massive refactoring of our entity code - replacing the ManyToMany with a new entity and two new relationships - but in the database... we have almost the exact same structure! In reality, a ManyToMany relationship is just a shortcut that allows you to have the join table without needing to create an entity for it.

So now that we understand that, from the database's perspective not much is changing, let's run the migration to make those tweaks:

symfony console doctrine:migrations:migrate

And... it fails! Rut roo. Next: let's find out why this migration failed. And, more importantly, how we can fix it and safely test it so that we confidently know that it will not fail when we deploy to production.

Leave a comment!

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": "^7.4.1 || ^8.0.0",
        "ext-ctype": "*",
        "ext-iconv": "*",
        "babdev/pagerfanta-bundle": "^3.3", // v3.3.0
        "composer/package-versions-deprecated": "^1.11", // 1.11.99.3
        "doctrine/doctrine-bundle": "^2.1", // 2.4.2
        "doctrine/doctrine-migrations-bundle": "^3.0", // 3.1.1
        "doctrine/orm": "^2.7", // 2.9.5
        "knplabs/knp-markdown-bundle": "^1.8", // 1.9.0
        "knplabs/knp-time-bundle": "^1.11", // v1.16.1
        "pagerfanta/doctrine-orm-adapter": "^3.3", // v3.3.0
        "pagerfanta/twig": "^3.3", // v3.3.0
        "sensio/framework-extra-bundle": "^6.0", // v6.1.5
        "stof/doctrine-extensions-bundle": "^1.4", // v1.6.0
        "symfony/asset": "5.3.*", // v5.3.4
        "symfony/console": "5.3.*", // v5.3.7
        "symfony/dotenv": "5.3.*", // v5.3.7
        "symfony/flex": "^1.3.1", // v1.15.1
        "symfony/framework-bundle": "5.3.*", // v5.3.7
        "symfony/monolog-bundle": "^3.0", // v3.7.0
        "symfony/runtime": "5.3.*", // v5.3.4
        "symfony/stopwatch": "5.3.*", // v5.3.4
        "symfony/twig-bundle": "5.3.*", // v5.3.4
        "symfony/webpack-encore-bundle": "^1.7", // v1.12.0
        "symfony/yaml": "5.3.*", // v5.3.6
        "twig/extra-bundle": "^2.12|^3.0", // v3.3.1
        "twig/string-extra": "^3.3", // v3.3.1
        "twig/twig": "^2.12|^3.0" // v3.3.2
    },
    "require-dev": {
        "doctrine/doctrine-fixtures-bundle": "^3.3", // 3.4.0
        "symfony/debug-bundle": "5.3.*", // v5.3.4
        "symfony/maker-bundle": "^1.15", // v1.33.0
        "symfony/var-dumper": "5.3.*", // v5.3.7
        "symfony/web-profiler-bundle": "5.3.*", // v5.3.5
        "zenstruck/foundry": "^1.1" // v1.13.1
    }
}