Flag of Ukraine
SymfonyCasts stands united with the people of Ukraine

ManyToMany Relationship

Video not working?

It looks like your browser may not support the H264 codec. If you're using Linux, try a different browser or try installing the gstreamer0.10-ffmpeg gstreamer0.10-plugins-good packages.

Thanks! This saves us from needing to use Flash or encode videos in multiple formats. And that let's us get back to making more videos :). But as always, please feel free to message us.

Let's talk about the famous, ManyToMany relationship. We already have a Genus entity and also a User entity. Before this tutorial, I updated the fixtures file. It still loads genuses, but it now loads two groups of users:

... lines 1 - 22
AppBundle\Entity\User:
user_{1..10}:
email: weaverryan+<current()>@gmail.com
plainPassword: iliketurtles
roles: ['ROLE_ADMIN']
avatarUri: <imageUrl(100, 100, 'abstract')>
user.aquanaut_{1..10}:
email: aquanaut<current()>@example.org
plainPassword: aquanote
isScientist: true
firstName: <firstName()>
lastName: <lastName()>
universityName: <company()> University
avatarUri: <imageUrl(100, 100, 'abstract')>

The first group consists of normal users, but the second group has an isScientist boolean field set to true. In other words, our site will have many users, and some of those users happen to be scientists.

That's not really important for the relationship we're about to setup, the point is just that many users are scientists. And on the site, we want to keep track of which genuses are being studied by which scientists, or really, users. So, each User may study many genuses. And each Genus, may be studied by many Users.

This is a ManyToMany relationship. In a database, to link the genus table and user table, we'll need to add a new, middle, or join table, with genus_id and user_id foreign keys. That isn't a Doctrine thing, that's just how it's done.

Mapping a ManyToMany in Doctrine

So how do we setup this relationship in Doctrine? It's really nice! First, choose either entity: Genus or User, I don't care. I'll tell you soon why you might choose one over the other, but for now, it doesn't matter. Let's open Genus. Then, add a new private property: let's call it $genusScientists:

This could also be called users or anything else. The important thing is that it will hold the array of User objects that are linked to this Genus:

... lines 1 - 14
class Genus
{
... lines 17 - 74
private $genusScientists;
... lines 76 - 172
}

Above, add the annotation: @ORM\ManyToMany with targetEntity="User".

... lines 1 - 14
class Genus
{
... lines 17 - 71
/**
* @ORM\ManyToMany(targetEntity="User")
*/
private $genusScientists;
... lines 76 - 172
}

Doctrine ArrayCollection

Finally, whenever you have a Doctrine relationship where your property is an array of items, so, ManyToMany and OneToMany, you need to initialize that property in the __construct() method. Set $this->genusScientists to a new ArrayCollection():

... lines 1 - 14
class Genus
{
... lines 17 - 76
public function __construct()
{
... line 79
$this->genusScientists = new ArrayCollection();
}
... lines 82 - 172
}

Creating the Join Table

Next... do nothing! Or maybe, high-five a stranger in celebration... because that is all you need. This is enough for Doctrine to create that middle, join table and start inserting and removing records for you.

It can be a bit confusing, because until now, every table in the database has needed a corresponding entity class. But the ManyToMany relationship is special. Doctrine says:

You know what? I'm not going to require you to create an entity for that join table. Just map a ManyToMany relationship and I will create and manage that table for you.

That's freaking awesome! To prove it, go to your terminal, and run:

./bin/console doctrine:schema:update --dump-sql

Boom! Thanks to that one little ManyToMany annotation, Doctrine now wants to create a genus_user table with genus_id and user_id foreign keys. Pretty dang cool.

JoinTable to control the... join table

But before we generate the migration for this, you can also control the name of that join table. Instead of genus_user, let's call ours genus_scientists - it's a bit more descriptive. To do that, add another annotation: @ORM\JoinTable. This optional annotation has just one job: to let you control how things are named in the database for this relationship. The most important is name="genus_scientist":

... lines 1 - 14
class Genus
{
... lines 17 - 71
/**
* @ORM\ManyToMany(targetEntity="User")
* @ORM\JoinTable(name="genus_scientist")
*/
private $genusScientists;
... lines 77 - 173
}

With that, find your terminal again and run:

./bin/console doctrine:migrations:diff

Ok, go find and open that file!

... lines 1 - 10
class Version20160921164430 extends AbstractMigration
{
... lines 13 - 15
public function up(Schema $schema)
{
// this up() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.');
$this->addSql('CREATE TABLE genus_scientist (genus_id INT NOT NULL, user_id INT NOT NULL, INDEX IDX_66CF3FA885C4074C (genus_id), INDEX IDX_66CF3FA8A76ED395 (user_id), PRIMARY KEY(genus_id, user_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
$this->addSql('ALTER TABLE genus_scientist ADD CONSTRAINT FK_66CF3FA885C4074C FOREIGN KEY (genus_id) REFERENCES genus (id) ON DELETE CASCADE');
$this->addSql('ALTER TABLE genus_scientist ADD CONSTRAINT FK_66CF3FA8A76ED395 FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE');
}
... lines 25 - 28
public function down(Schema $schema)
{
// this down() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.');
$this->addSql('DROP TABLE genus_scientist');
}
}

Woohoo!

Now it creates a genus_scientist table with those foreign keys. Execute the migration:

./bin/console doctrine:migrations:migrate

Guys: with about 5 lines of code, we just setup a ManyToMany relationship. Next question: how do we add stuff to it? Or, read from it?

Leave a comment!

29
Login or Register to join the conversation
Default user avatar
Default user avatar Мони Мони | posted 4 years ago

How to define in witch entity User or Genus we must use @ManyToMany annotation. Why do you use It in User instead of Genus class?

1 Reply

Hey Moni,

That's a good question! The questions is which side is inverse one. There're a few subtle difference between inverse and owning side. We explain the difference here: https://knpuniversity.com/s... . You can also take a look at explanation in Doctrine docs: http://docs.doctrine-projec... . So it's just depends on you as a developer to choose which side is inverse and which one is owning, i.e. depends on your further application logic.

Also, here's a screencast about how to synchronize owning and inverse sides which can be useful for you:
https://knpuniversity.com/s... .

Cheers!

1 Reply
Farshad Avatar
Farshad Avatar Farshad | posted 1 year ago

Whats the difference between make:migration and doctrine:migrations:diff ?

Reply

Hey Farry7,

Both doing the same thing - create a Doctrine migration. But the "make:migration" command is from Symfony's MakerBundle, and "doctrine:migrations:diff" is from the DoctrineMigrationBundle. So, you don't have to install the Maker in order to generate a migration, but in case you already have it installed - you can use a shorter command name, i.e. "make:migration" :)

Cheers!

Reply
Bhagwandas P. Avatar
Bhagwandas P. Avatar Bhagwandas P. | posted 3 years ago

There are two entities: User and Project
Now there are two types of user's differentiated by "type" member in User entity (let us say: usertype1 and usertype2).

And both can have Projects associated with them (ManyToMany relationship).

So there would be two members required to store projects list for both type of users:
usertype1projects and usertype2projects

Both can have many to many relationships with Projects.

When I try to run migration, I get following error:
"Table already exist"

So my question is how do I store multiple ManyToMany relationships in same entity with same fields.

Reply

Hey Rahul,

Hm, if you have an error like "Table already exist" - the problem is NOT in "how you store multiple ManyToMany relationships in same entity with same fields". Please, double check your migrations, looks like a few of them is trying to create the same table, probably you generated a migration incorrectly. Or you executed one of these commands instead of using migration: "doctrine:schema:create" or "doctrine:schema:update --force" - you should carefully mixing those command with migrations, otherwise you can generate an invalid migration

Cheers!

Reply
Bhagwandas P. Avatar
Bhagwandas P. Avatar Bhagwandas P. | victor | posted 3 years ago

Hi Victor Bocharsky , Thanks for response.

It was a new entity. No mixing of create/force update.


namespace App\Entity;

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;

/**
* @ORM\Entity(repositoryClass="App\Repository\ProjectTRepository")
*/
class ProjectT
{
/**
* @ORM\Id()
* @ORM\GeneratedValue()
* @ORM\Column(type="integer")
*/
private $id;

/**
* @ORM\Column(type="string", length=255)
*/
private $name;

/**
* @ORM\ManyToMany(targetEntity="App\Entity\UserT", mappedBy="usertype1Projects")
*/
private $userT1s;

/**
* @ORM\ManyToMany(targetEntity="App\Entity\UserT", mappedBy="usertype2Projects")
*/
private $userT2s;

public function __construct()
{
$this->userT1s = new ArrayCollection();
$this->userT2s = new ArrayCollection();
}

public function getId(): ?int
{
return $this->id;
}

public function getName(): ?string
{
return $this->name;
}

public function setName(string $name): self
{
$this->name = $name;

return $this;
}

/**
* @return Collection|UserT[]
*/
public function getUserT1s(): Collection
{
return $this->userT1s;
}

public function addUserT1(UserT $userT1): self
{
if (!$this->userT1s->contains($userT1)) {
$this->userT1s[] = $userT1;
$userT1->addUsertype1Project($this);
}

return $this;
}

public function removeUserT1(UserT $userT1): self
{
if ($this->userT1s->contains($userT1)) {
$this->userT1s->removeElement($userT1);
$userT1->removeUsertype1Project($this);
}

return $this;
}

/**
* @return Collection|UserT[]
*/
public function getUserT2s(): Collection
{
return $this->userT2s;
}

public function addUserT2(UserT $userT2): self
{
if (!$this->userT2s->contains($userT2)) {
$this->userT2s[] = $userT2;
$userT2->addUsertype2Project($this);
}

return $this;
}

public function removeUserT2(UserT $userT2): self
{
if ($this->userT2s->contains($userT2)) {
$this->userT2s->removeElement($userT2);
$userT2->removeUsertype2Project($this);
}

return $this;
}
}


namespace App\Entity;

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;

/**
* @ORM\Entity(repositoryClass="App\Repository\UserTRepository")
*/
class UserT
{
/**
* @ORM\Id()
* @ORM\GeneratedValue()
* @ORM\Column(type="integer")
*/
private $id;

/**
* @ORM\ManyToMany(targetEntity="App\Entity\ProjectT", inversedBy="userT1s")
*/
private $usertype1Projects;

/**
* @ORM\ManyToMany(targetEntity="App\Entity\ProjectT", inversedBy="userT2s")
*/
private $usertype2Projects;

public function __construct()
{
$this->usertype1Projects = new ArrayCollection();
$this->usertype2Projects = new ArrayCollection();
}

public function getId(): ?int
{
return $this->id;
}

/**
* @return Collection|ProjectT[]
*/
public function getUsertype1Projects(): Collection
{
return $this->usertype1Projects;
}

public function addUsertype1Project(ProjectT $usertype1Project): self
{
if (!$this->usertype1Projects->contains($usertype1Project)) {
$this->usertype1Projects[] = $usertype1Project;
}

return $this;
}

public function removeUsertype1Project(ProjectT $usertype1Project): self
{
if ($this->usertype1Projects->contains($usertype1Project)) {
$this->usertype1Projects->removeElement($usertype1Project);
}

return $this;
}

/**
* @return Collection|ProjectT[]
*/
public function getUsertype2Projects(): Collection
{
return $this->usertype2Projects;
}

public function addUsertype2Project(ProjectT $usertype2Project): self
{
if (!$this->usertype2Projects->contains($usertype2Project)) {
$this->usertype2Projects[] = $usertype2Project;
}

return $this;
}

public function removeUsertype2Project(ProjectT $usertype2Project): self
{
if ($this->usertype2Projects->contains($usertype2Project)) {
$this->usertype2Projects->removeElement($usertype2Project);
}

return $this;
}
}

If you try running diff on this, it will give following error:

> The table with name 'cpp.user_t_project_t' already exists.

This is the issue.

Reply

Hey Rahul,

Hm, could you show your migration. Sometimes Doctrine generate migrations not perfect, so it's a good idea to check if everything is good in just generated migration and tweak it if needed.

Os, it sounds like you already have "user_t_project_t" table, that means if those entities new and migration is valid - most probably on your first run of this migration something went wrong and migration was executed partially only. If this is the case, just manually remove that table, fix the migration, and run it again. Also, make sure you don't have any important data in that table before deleting it.

Btw, you can also check if your mapping is valid with the next command:
$ bin/console doctrine:schema:validate

It's always a good idea to check if your mapping is valid after making some changes to it.

Cheers!

Reply

Hi! I would like to use a single ManyToMany relationship for 2 different entities let me explain:
I have news and warnings, which are different, so I have create 2 different entities for them but they both have the field category, and they can have more than one category. So I need a manytomany table yet I want them to have a single table for both. How can I do that with doctrine?

Reply

Hey Giacomo Balloccu

Just do It! Woops, it's not Nike ads =) You should can easily setup ManyToMany relation, between News entity and Category, and the same way for Warnings entity. Doctrine will create separate join tables so everything will work as you want!

Cheers!

Reply
Default user avatar
Default user avatar Chris R | posted 4 years ago

I had a weird one where when I added the @ORM\JoinTable annotation and ran the migrations:diff command, it wasn't changing the name of the table.

Executing the following command fixed the problem:
php bin/console cache:clear --env=dev

Reply

Hey Chris,

Thanks for sharing this! Symfony do its best to recompile the cache in dev mode, but sometimes you need to clear the cache manually. Actually, clearing the cache is the first thing you have to do before finding other solutions :)

Cheers!

Reply

Hi,

Once again your videos are really awesome.
I have a question about join table how can I do if I want an extra column like position.
Imagine with your example like scientist, I want to order them by a position. Is the ManyToMany still work ? Or should I made my relation manually?
Thanks again.
G.

Reply

Hey Greg,

That's really good question! Well, probably you can iterate over the collection and order it by some custom logic... but I think it'll better to use OneToMany + ManyToOne relationships instead of ManyToMany. i.e. you need an auxiliary entity (let's call it GenusScientist in our example) which will hold your position property (or any other additional properties you need for). Let me summarize that all: you will need OneToMany relationship between Genus and GenusScientist, and then ManyToOne relationship between GenusScientist and User. As a result, you will have the same ManyToMany relationship between Genus and User, but you can also add additional fields to the GenusScientist entity. It's a common practice in such situation.

I hope you understand what I mean here and it makes sense for you.

Cheers!

Reply

Hey Victor,

I did like this in my project but I would like to know if there is another way to do.
I'm glad to know this is a good way to do ;)

Thanks again for your answer. And like usual you're the best ;)

Cheers.

Reply

Great!

Look at the "Sortable" extension from StofDoctrineExtensionsBundle - it could help with your issue about ordering entities.

Cheers!

Reply
Default user avatar
Default user avatar Евгений | posted 4 years ago

Hello, can you help me?
When did we add isScientist, avatarUri, universityName fields in user entity?

Reply

Hey Евгений!

Ah yes, sorry about this - we didn't mean to confuse you! This was added *before* the recording of this course. Sometimes I will make small changes to the code between episodes to make things more "smooth". I try not to do this (because it's annoying if you're coding through all the courses), but sometimes it's necessary! I usually warn about this specifically in the first chapter... but I see that I didn't in this case! My apologies!

So anyways, that's where it was added, in addition to a few other changes, like upgrading Symfony, adding a user "show" page, tweaking the /genus/new code to work with the new SubFamily relation and redirecting to the edit page after save.

Cheers!

Reply
Default user avatar
Default user avatar Евгений | weaverryan | posted 4 years ago

Hey Weaverryan,
I got it. Thanks =)

Reply

Hey Nick,

What do you mean? Are you talking about migrating legacy app which already has some data to Symfony and Doctrine? Let me know if I understand you right

Cheers!

Reply
Default user avatar
Default user avatar Laravel Nick | victor | posted 4 years ago

Hey Victor,

Thanks for the reply.

Yes, I am talking about migrating legacy app which already has some data to Symfony and Doctrine and the legacy database already includes the 'link table' and all tables are populated with data.

The 'link table' contains just two columns - composite primary keys, each being a foreign key relating to the two corresponding 'Many' tables.

I have an Entity for the 'link table' which includes two @ORM\Id (one for each primary key).

However, when I try to retrieve data from these tables via queryBuilder, it throws an exception, because the getters and setters refer to a private variable which is already taken by @ORM\Id.

If I remove @ORM\Id it complains that there is no primary key.

Thanks again for your reply.

Reply

Hey Nick,

Complex question. Actually I have a little experience with migrating legacy apps to the Symfony, but let me give you a few advices.

If legacy app's DB schema very similar to Doctrine's one - you can try to reuse it with Doctrine, but most probably you'll need to drop old foreign keys and add new ones with Doctrine. You can use "./bin/console doctrine:schema:validate" Symfony console command to validate your schema and "./bin/console doctrine:schema:update --dump-sql" to see what SQL queries you need to run to fix problems before Doctrine is able to work with the legacy app's DB.

The other solution is to create a completely new DB with already valid schema which you can create with proper Symfony console commands. Then write a one-time migrate script which will migrate data from the legacy app's DB to your new DB.

I'd say the 2nd solution is simpler as for me because you don't deal with fixing foreign keys and indexes failures.

I hope this helps you.

Cheers!

Reply
Robert V. Avatar
Robert V. Avatar Robert V. | posted 4 years ago

I have a question regarding setting relationship to individual fields in a entity. For example, I have a entity called Setup which has fields such as Color1, Color2, Color3. Then I have a second table called Plate (printing plate info for each color in the Setup table). Now in my app I would like to relate a Plate to each Color1, Color2, Color3 field in the Setup table. I'm not sure if it is suggested to have relationships on individual fields within a entity, like this: Color1Plate, Color2Plate, etc.

Reply

Yo Rob!

I don't see a problem with this :). Often, when we have multiple "things" (like colors), there is a temptation to turn it into a collection field - e.g. a "colors" property on Setup, perhaps a ManyToMany relationship or something different. But, I hate this - if you know that you will always have 3 fields, then yea, I like color1, color2, and color3. And I don't see a problem with have 3 relation fields. Your code might have some small duplication because of this, but it will be really easy to understand :).

I hope that helps! Cheers!

Reply
Robert V. Avatar

Yo weaverryan !

It does help! Now I would have a max of like 15 color fields in Setup (i only said 3), but that would be it. I should have stated 15 fields first... For my learning I'm going to set it up and run some tests to see how it works out. I don't see another way at the moment and this advice will keep me going! I'm only messing around with it anyway and thats a good thing! Thanks!

Reply
Default user avatar

Can you guys help me figure out something: since few weeks I cannot make updates to my db. I have to drop it and do the usual process to create it again.
In this case I got a PDOexception SQLSTATE[42S01]: Base table or view already exists: 1050 La table 'genus' existe déjà

This doesnt make sense to me since the migration just asks to create a table genus_scientist

public function up(Schema $schema)
{
// this up() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.');

$this->addSql('CREATE TABLE genus_scientist (genus_id INT NOT NULL, user_id INT NOT NULL, INDEX IDX_66CF3FA885C4074C (genus_id), INDEX IDX_66CF3FA8A76ED395 (user_id), PRIMARY KEY(genus_id, user_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
$this->addSql('ALTER TABLE genus_scientist ADD CONSTRAINT FK_66CF3FA885C4074C FOREIGN KEY (genus_id) REFERENCES genus (id) ON DELETE CASCADE');
$this->addSql('ALTER TABLE genus_scientist ADD CONSTRAINT FK_66CF3FA8A76ED395 FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE');
}

Thx for your help

Reply

Hey ugo .p!

Hmm. Are you sure that *this* specific migration causes the problem? Also, how are you "dropping your database"? Are you truly dropping the entire database and re-creating it, or just running bin/console doctrine:schema:drop --force? The drop command doesn't necessarily drop *all* of the tables - it only drops tables that it is aware of (which *should* include genus, but it's possible it's causing a problem).

Cheers!

Reply
Default user avatar

Hi Ryan, I do droped the entire table (doctrine:database:drop --force). What is strange is that I could initially update databases few weeks ago but not It does not want... makes no sense to me. :(

Reply

Hey @ugo .p!

Hmm. Can you tell me exactly what commands you're running and what you're seeing? After you drop (and re-create the database), are you running doctrine:migrations:migrate? And then you are seeing the Base table or view already exists: 1050 La table 'genus' existe déjà error?

If so, then it means that one of your migrations has a problem in it. This is actually pretty easy to do: if your database isn't fully up-to-date, and you run doctrine:migrations:diff, it might have generated some *extra* SQL statements. Then, when you migrate, it has *two* migrations with CREATE TABLE genus.

Double-check your migrations. And if you can send a screenshot of the full error, it might help :).

Cheers!

Reply
Cat in space

"Houston: no signs of life"
Start the conversation!

This course is built on Symfony 3, but most of the concepts apply just fine to newer versions of Symfony.

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": "^7.1.3",
        "symfony/symfony": "3.4.*", // v3.4.49
        "doctrine/orm": "^2.5", // 2.7.5
        "doctrine/doctrine-bundle": "^1.6", // 1.12.13
        "doctrine/doctrine-cache-bundle": "^1.2", // 1.4.0
        "symfony/swiftmailer-bundle": "^2.3", // v2.6.7
        "symfony/monolog-bundle": "^2.8", // v2.12.1
        "symfony/polyfill-apcu": "^1.0", // v1.23.0
        "sensio/distribution-bundle": "^5.0", // v5.0.25
        "sensio/framework-extra-bundle": "^3.0.2", // v3.0.29
        "incenteev/composer-parameter-handler": "^2.0", // v2.1.4
        "composer/package-versions-deprecated": "^1.11", // 1.11.99.4
        "knplabs/knp-markdown-bundle": "^1.4", // 1.9.0
        "doctrine/doctrine-migrations-bundle": "^1.1", // v1.3.2
        "stof/doctrine-extensions-bundle": "^1.2" // v1.3.0
    },
    "require-dev": {
        "sensio/generator-bundle": "^3.0", // v3.1.7
        "symfony/phpunit-bridge": "^3.0", // v3.4.47
        "nelmio/alice": "^2.1", // v2.3.6
        "doctrine/doctrine-fixtures-bundle": "^2.3" // v2.4.1
    }
}