Many-to-Many but with Extra Data
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 SubscribeManyToMany relations are the one place in Doctrine where we have a table in our database - starship_droid
- but no corresponding entity in our app.
But there's a catch: we can't add extra columns to that join table. Like, what if we wanted to track when a droid was assigned to a starship? To do that our join table would need an assignedAt
column. But we can't add that!
When Many-to-Many isn't Enough
The solution is to roll up our sleeves and start handling things more manually.
We'll stop using the many-to-many relationship entirely. Instead, we're going to generate a new entity that represents the join table. First, undo the many-to-many relationship (but only worry about the properties, not the methods). In Starship
, wave goodbye to the droids
property:
// ... lines 1 - 15 | |
class Starship | |
{ | |
// ... lines 18 - 50 | |
/** | |
* @var Collection<int, Droid> | |
*/ | |
#[ORM\ManyToMany(targetEntity: Droid::class, inversedBy: 'starships')] | |
private Collection $droids; | |
// ... lines 56 - 227 | |
} |
And over in Droid
, do the same for the starships
many-to-many property:
// ... lines 1 - 10 | |
class Droid | |
{ | |
// ... lines 13 - 23 | |
/** | |
* @var Collection<int, Starship> | |
*/ | |
#[ORM\ManyToMany(targetEntity: Starship::class, mappedBy: 'droids')] | |
private Collection $starships; | |
// ... lines 29 - 89 | |
} |
Clear out the constructor in both.
Find your terminal and run:
symfony console doctrine:schema:update --dump-sql
This shows you what your migration would look like if you generated it right now. It's what we expect: no more starship_droid
table.
Creating a New Join Entity
But don't generate that migration just yet! We do want the join table, but now we need to create an entity to represent it. Run:
symfony console make:entity StarshipDroid
DroidAssignment
might be a more fitting name, but StarshipDroid
helps us visualize what we're doing: recreating the same exact database relationship via two ManyToOne
s
Add assignedAt
along with two more properties to create relationships from this join table to Starship
and Droid
.
These are going to be ManyToOne
relationships, and they'll connect StarshipDroid
to Starship
and Droid
.
The Migration that Does Nothing
Now, generate that migration:
symfony console make:migration
And check it out. It might seem like there are a lot of changes, but look closely: it's just dropping the foreign key constraints, adding a primary key, and recreating the foreign key:
// ... lines 1 - 12 | |
final class Version20250315183623 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 starship_droid DROP CONSTRAINT FK_1C7FBE889B24DF5'); | |
$this->addSql('ALTER TABLE starship_droid DROP CONSTRAINT FK_1C7FBE88AB064EF'); | |
$this->addSql('ALTER TABLE starship_droid DROP CONSTRAINT starship_droid_pkey'); | |
$this->addSql('ALTER TABLE starship_droid ADD id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL'); | |
$this->addSql('ALTER TABLE starship_droid ADD assigned_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL'); | |
$this->addSql('ALTER TABLE starship_droid ADD CONSTRAINT FK_1C7FBE889B24DF5 FOREIGN KEY (starship_id) REFERENCES starship (id) NOT DEFERRABLE INITIALLY IMMEDIATE'); | |
$this->addSql('ALTER TABLE starship_droid ADD CONSTRAINT FK_1C7FBE88AB064EF FOREIGN KEY (droid_id) REFERENCES droid (id) NOT DEFERRABLE INITIALLY IMMEDIATE'); | |
$this->addSql('ALTER TABLE starship_droid ADD PRIMARY KEY (id)'); | |
} | |
// ... lines 32 - 44 | |
} |
So, in the end, this migration doesn't change anything real in the database.
Run it with:
symfony console doctrine:migrations:migrate
And boom!
Column
assignedAt
cannot containnull
values.
Doctrine is throwing a tantrum because of the existing rows in the starship_droid
table. We can pacify it with a default value. Update the migration manually to say DEFAULT NOW() NOT NULL
:
// ... lines 1 - 12 | |
final class Version20250315183623 extends AbstractMigration | |
{ | |
// ... lines 15 - 19 | |
public function up(Schema $schema): void | |
{ | |
// ... lines 22 - 26 | |
$this->addSql('ALTER TABLE starship_droid ADD assigned_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NOW() NOT NULL'); | |
// ... lines 28 - 30 | |
} | |
// ... lines 32 - 44 | |
} |
The Finishing Touches
Let's add a final touch to StarshipDroid
:
// ... lines 1 - 7 | |
#[ORM\Entity(repositoryClass: StarshipDroidRepository::class)] | |
class StarshipDroid | |
{ | |
#[ORM\Id] | |
#[ORM\GeneratedValue] | |
#[ORM\Column] | |
private ?int $id = null; | |
#[ORM\Column] | |
private ?\DateTimeImmutable $assignedAt = null; | |
#[ORM\ManyToOne(inversedBy: 'starshipDroids')] | |
#[ORM\JoinColumn(nullable: false)] | |
private ?Droid $droid = null; | |
#[ORM\ManyToOne(inversedBy: 'starshipDroids')] | |
#[ORM\JoinColumn(nullable: false)] | |
private ?Starship $starship = null; | |
// ... lines 26 - 66 | |
} |
This assignedAt
isn't really something we should have to worry about. Create a constructor and set it automatically: $this->assignedAt = new \DateTimeImmutable();
:
// ... lines 1 - 8 | |
class StarshipDroid | |
{ | |
// ... lines 11 - 26 | |
public function __construct() | |
{ | |
$this->assignedAt = new \DateTimeImmutable(); | |
} | |
// ... lines 31 - 71 | |
} |
Hold up, because this is huge! We now have the exact same relationship in the database as before. But since we've taken control of the join entity, we can add new fields to it. Next, we'll see how to assign droids to Starships with this new entity setup. And eventually, we'll get fancy and hide this implementation detail entirely!
Hello
When I try to migrate by running symfony console make:migration at the end, I get an error:
In TableAlreadyExists.php line 16:
The table named ".starship_droid" already exists.
However, the table has already been deleted from the database.