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.
With a Subscription, click any sentence in the script to jump to that part of the video!
Login SubscribeThe 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.
When I follow this approach, the created entity has an ID field. So when the migration is created, it includes SQL for creating an auto-incrementing ID column. These are generally not necessary for junction tables. So I remove it from the entity. But then when trying to make the migration an exception is thrown: "Every Entity must have an identifier/primary key".
Short of manually modifying the SQL (which I reeeeeally don't want to do), is there an accepted way around this problem?