SymfonyCasts stands united with the people of Ukraine
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.
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.
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 | |
| } |
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.
Found it. Remove the ID column from the entity. Add the #[ORM\Id]annotations to both of the columns you want to make up the new primary key.
Hey Jim-Smm,
Glad you found the solution yourself! And thanks for sharing it with others :)
Cheers!
Given the introduction of an intermediary entity does this necessarily move at least part of the logic of adding a Tag into the controller or other service?
Also the generated code for removeQuestionTag() seems like it's going to fail hard by trying to setQuestion() to null. In fact I'm seeing this behavior. Would it be better to use a soft delete/removal field and use a data transformer to handle the messy details?
Hey @Aaron Kincer!
A few good questions here :).
> Given the introduction of an intermediary entity does this necessarily move at least part of the logic of adding a Tag into the controller or other service?
Probably, yes. That's probably how I would do it. Somewhere - either controller or a service - would probably have extra logic to help create this intermediate entity. Wrapping that up in a service is a nice way of doing that. However, in theory, you could still have a Question::addTag() method. Inside that method you would create the intermediate entity and set everything. To get this to save, you would probably need to do a "cascade persist" so that when you persist Question, that persists the QuestionTag... an that persists the Tag. This stuff usually makes me uncomfortable.
> Also the generated code for removeQuestionTag() seems like it's going to fail hard by trying to setQuestion() to null. In fact I'm seeing this behavior. Would it be better to use a soft delete/removal field and use a data transformer to handle the messy details?
Hmm, good catch. I would probably use orphan removal for this. This would go on the Question.questionTags property (if I'm thinking straight at the moment). It basically says "if a QuestionTag gets orphaned from a Question (which would happen when setting QuestionTag.question = null) delete it". In general, soft delete... is something I try to avoid - I did it a lot earlier in my career and it can cause weird things to happen.
Let me know if this... make sense :).
Cheers!
Strike that first paragraph. Turned out to be a bad idea that I got from some random post that seemed like a good idea at the time.
Hey @Aaron!
Sorry for the slow reply - complicated week over here ;).
About "being able to rollback changes/deletions". I'm not sure to be honest. The reason is that "real life" turns out to be a lot more complex. So, yes, you could implement a soft delete type of thing. But then, you are really only able to revert deletes, not updates. I have seen things where you setup a database that actually versions all of your changes, but that adds a LOT of complexity. And then, what if changing and Article from "Unpublish to published" actually triggers 3 other non-database changes (e.g. it changes something in a CRM... and also dispatches a Messenger message that tweaks a file in some way). Even if you could revert the data in the database, these types of things would not automatically be reversed.
So, in theory having a system where you can roll back any changes is possible, but it's a complex problem. This is something... I "think" event sourcing kind of addresses. This is a programming practice (that I don't have a lot of experience with) where every change you make in your system is done with an event. And so, you could then "replay" every event in history if you wanted to, to arrive at today's "state" in your app. I don't think event sourcing would automatically give you a "rollback" functionality, but in theory, it would make it easier to do. Think of the "undo" on any app you use: that app is keeping track of all of the "events" that have happened. And so, when you "undo", it knows what the last "action" was and has some logic for figuring how to "invert" that action.
Btw, if your goal is more "to be able to undo and get back old data IF something ever went wrong", but not necessarily make this a simple, automatic process, then doing a lot more logging or event sourcing (which is, in some ways, a very fancy logging system), would be the way to go. The critical question to ask is: "Am I planning for a routine occurrence in my app? Like, where a normal admin user might be able to rollback any number of states in the database at any time?" Or is it more "In case something goes wrong in an unforeseen way, we want to have the data we need to be able to have some programmers fix things". Let that guide you. The answer might be mostly the latter... but with a few areas where you DO want an admin user to be able to "undo" things (e.g. unsend this invoice). In that case, log a lot to cover the 2nd case... then write custom code to handle being able to unsend an invoice.
Cheers!
Thanks! I'll spend some time thinking it through thoroughly. I _think_ that what I'm trying to accomplish could in fact be achieved through hefty logging. And the changes should be pretty light on the data.
Yes it makes sense. I moved the logic to my DTO DataTransformer where all the data action takes place. So far my controllers on this app don't even use the EM. Well except for the registration controller at least for now.
What have you found as the generally better solution in deletes when the nature of the data is such that full historical fidelity with the ability to roll back changes/deletions if needed is desired? Loggable? If so does that scale? That's what I'm trying to solve and why I asked about soft deletes.
Thank you so much for the insight.
Great video! How common is it to add extra columns to a pivot table? What are some real world scenarios where you would reach for this?
Yo Kevin!
Thanks :). I hope it was useful!
> How common is it to add extra columns to a pivot table? What are some real world scenarios where you would reach for this
I think it IS quite common... but I would also bet that you might have projects where this rarely happens and projects where this *constantly* happens. I think the example we gave here is a pretty good one, but let me use a different (but similar) example. Suppose you have a way for two Users to become "friends" or "connected". If you wanted to store when they were connected, that would be an extra field. Or, if you wanted to store who *initiated* the connection (e.g. originalRequestedBy), that would be another situation.
And there are even MORE situations where you are technically doing this, but you never even think about it! Imagine an Order entity (as in "product orders"). Each "Order" can have many Products and each Product can be included in many Orders. But actually, when you model this, you likely have Order OneToMany to OrderItem ManyToOne to Product, where OrderItem includes (at the very least) a "quantity" column. Even though it may not feel like it, this is yet another example of a "pivot table" with extra columns... even if you don't think of it that way :).
Cheers!
Hey wLcDesigns,
We're happy to hear it was helpful for you! ManyToMany complex relations might be tricky sometimes. Thank you for your feedback ;)
Cheers!
// composer.json
{
"require": {
"php": ">=8.1",
"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.2.1
"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.17.5
"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/validator": "5.3.*", // v5.3.14
"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
}
}
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?