Buy

Head back to /genus and click into one of our genuses. Thanks to our hard work, we can link genuses and users. So I know that Eda Farrell is a User that studies this Genus.

But, hmm, what if I need to store a little extra data on that relationship, like the number of years that each User has studied the Genus. Maybe Eda has studied this Genus for 10 years, but Marietta Schulist has studied it for only 5 years.

In the database, this means that we need our join table to have three fields now: genus_id, user_id, but also years_studied. How can we add that extra field to the join table?

The answer is simple, you can't! It's not possible. Whaaaaat?

You see, ManyToMany relationships only work when you have no extra fields on the relationship. But don't worry! That's by design! As soon as your join table need to have even one extra field on it, you need to build an entity class for it.

Creating the GenusScientist Join Entity

In your Entity directory, create a new class: GenusScientist. Open Genus and steal the ORM use statement on top, and paste it here:

71 lines src/AppBundle/Entity/GenusScientist.php
... lines 1 - 2
namespace AppBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
... lines 6 - 10
class GenusScientist
{
... lines 13 - 70
}

Next, add some properties: id - we could technically avoid this, but I like to give every entity an id - genus, user, and yearsStudied:

71 lines src/AppBundle/Entity/GenusScientist.php
... lines 1 - 2
namespace AppBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
... lines 6 - 10
class GenusScientist
{
... lines 13 - 17
private $id;
... lines 19 - 23
private $genus;
... lines 25 - 29
private $user;
... lines 31 - 34
private $yearsStudied;
... lines 36 - 70
}

Use the "Code"->"Generate" menu, or Command+N on a Mac, and select "ORM Class" to generate the class annotations:

71 lines src/AppBundle/Entity/GenusScientist.php
... lines 1 - 2
namespace AppBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
/**
* @ORM\Entity
* @ORM\Table(name="genus_scientist")
*/
class GenusScientist
{
... lines 13 - 70
}

Oh, and notice! This generated a table name of genus_scientist: that's perfect! I want that to match our existing join table: we're going to migrate it to this new structure.

Go back to "Code"->"Generate" and this time select "ORM Annotation". Generate the annotations for id and yearsStudied:

71 lines src/AppBundle/Entity/GenusScientist.php
... lines 1 - 10
class GenusScientist
{
/**
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
* @ORM\Column(type="integer")
*/
private $id;
... lines 19 - 31
/**
* @ORM\Column(type="string")
*/
private $yearsStudied;
... lines 36 - 70
}

Perfect!

So how should we map the genus and user properties? Well, think about it: each is now a classic ManyToOne relationship. Every genus_scientist row should have a genus_id column and a user_id column. So, above genus, say ManyToOne with targetEntity set to Genus Below that, add the optional @JoinColumn with nullable=false:

71 lines src/AppBundle/Entity/GenusScientist.php
... lines 1 - 10
class GenusScientist
{
... lines 13 - 19
/**
* @ORM\ManyToOne(targetEntity="Genus")
* @ORM\JoinColumn(nullable=false)
*/
private $genus;
... lines 25 - 70
}

Copy that and put the same thing above user, changing the targetEntity to User:

71 lines src/AppBundle/Entity/GenusScientist.php
... lines 1 - 10
class GenusScientist
{
... lines 13 - 25
/**
* @ORM\ManyToOne(targetEntity="User")
* @ORM\JoinColumn(nullable=false)
*/
private $user;
... lines 31 - 70
}

And... that's it! Finish the class by going back to the "Code"->"Generate" menu, or Command+N on a Mac, selecting Getters and choosing id:

71 lines src/AppBundle/Entity/GenusScientist.php
... lines 1 - 10
class GenusScientist
{
... lines 13 - 36
public function getId()
{
return $this->id;
}
... lines 41 - 70
}

Do the same again for Getters and Setters: choose the rest of the properties:

71 lines src/AppBundle/Entity/GenusScientist.php
... lines 1 - 10
class GenusScientist
{
... lines 13 - 41
public function getGenus()
{
return $this->genus;
}
public function setGenus($genus)
{
$this->genus = $genus;
}
public function getUser()
{
return $this->user;
}
public function setUser($user)
{
$this->user = $user;
}
public function getYearsStudied()
{
return $this->yearsStudied;
}
public function setYearsStudied($yearsStudied)
{
$this->yearsStudied = $yearsStudied;
}
}

Entity, done!

Updating the Existing Relationships

Now that the join table has an entity, we need to update the relationships in Genus and User to point to it. In Genus, find the genusScientists property. Guess what? This is not a ManyToMany to User anymore: it's now a OneToMany to GenusScientist. Yep, it's now the inverse side of the ManyToOne relationship we just added. That means we need to change inversedBy to mappedBy set to genus. And of course, targetEntity is GenusScientist:

204 lines src/AppBundle/Entity/Genus.php
... lines 1 - 14
class Genus
{
... lines 17 - 71
/**
* @ORM\OneToMany(targetEntity="GenusScientist", mappedBy="genus", fetch="EXTRA_LAZY")
*/
private $genusScientists;
... lines 76 - 202
}

You can still keep the fetch="EXTRA_LAZY": that works for any relationship that holds an array of items. But, we do need to remove the JoinTable: annotation: both JoinTable and JoinColumn can only live on the owning side of a relationship.

There are more methods in this class - like addGenusScientist() that are now totally broken. But we'll fix them later. In GenusScientist, add inversedBy set to the genusScientists property on Genus:

71 lines src/AppBundle/Entity/GenusScientist.php
... lines 1 - 10
class GenusScientist
{
... lines 13 - 19
/**
* @ORM\ManyToOne(targetEntity="Genus", inversedBy="genusScientists")
* @ORM\JoinColumn(nullable=false)
*/
private $genus;
... lines 25 - 70
}

Finally, open User: we need to make the exact same changes here.

For studiedGenuses, the targetEntity is now GenusScientist, the relationship is OneToMany, and it's mappedBy the user property inside of GenusScientist:

243 lines src/AppBundle/Entity/User.php
... lines 1 - 16
class User implements UserInterface
{
... lines 19 - 77
/**
* @ORM\OneToMany(targetEntity="GenusScientist", mappedBy="user")
*/
private $studiedGenuses;
... lines 82 - 241
}

The OrderBy doesn't work anymore. Well, technically it does, but we can only order by a field on GenusScientist, not on User. Remove that for now.

Tip

You should also add the inversedBy="studiedGenuses" to the user property in GenusScientist:

71 lines src/AppBundle/Entity/GenusScientist.php
... lines 1 - 10
class GenusScientist
{
... lines 13 - 25
/**
* @ORM\ManyToOne(targetEntity="User", inversedBy="studiedGenuses")
* @ORM\JoinColumn(nullable=false)
*/
private $user;
... lines 31 - 70
}

It didn't hurt anything, but I forgot that!

The Truth About ManyToMany

Woh! Ok! Step back for a second. Our ManyToMany relationship is now entirely gone: replaced by 3 entities and 2 classic ManyToOne relationships. And if you think about it, you'll realize that a ManyToMany relationship is nothing more than two ManyToOne relationships in disguise. All along, we could have mapped our original setup by creating a "join" GenusScientist entity with only genus and user ManyToOne fields. A ManyToMany relationship is just a convenience layer when that join table doesn't need any extra fields. But as soon as you do need extra, you'll need this setup.

Generating (and Fixing) the Migration

Last step: generate the migration:

./bin/console doctrine:migrations:diff

Tip

If you get a

There is no column with name id on table genus_scientist

error, this is due to a bug in doctrine/dbal 2.5.5. It's no big deal, as it just affects the generation of the migration file. There are 2 possible solutions until the bug is fixed:

1) Downgrade to doctrine/dbal 2.5.4. This would mean adding the following line to your composer.json file:

"doctrine/dbal": "2.5.4"

Then run composer update

2) Manually rename genus_scientist to something else (e.g. genus_scientist_old) and then generate the migration. Then, rename the table back. The generated migration will be incorrect, because it will think that you need to create a genus_scientist table, but we do not. So, you'll need to manually update the migration code by hand and test it.

Look in the app/DoctrineMigrations directory and open that migration:

48 lines app/DoctrineMigrations/Version20161017160251.php
... lines 1 - 10
class Version20161017160251 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('ALTER TABLE genus_scientist DROP FOREIGN KEY FK_66CF3FA885C4074C');
$this->addSql('ALTER TABLE genus_scientist DROP FOREIGN KEY FK_66CF3FA8A76ED395');
$this->addSql('ALTER TABLE genus_scientist DROP PRIMARY KEY');
$this->addSql('ALTER TABLE genus_scientist ADD id INT AUTO_INCREMENT NOT NULL, ADD years_studied VARCHAR(255) NOT NULL');
$this->addSql('ALTER TABLE genus_scientist ADD CONSTRAINT FK_66CF3FA885C4074C FOREIGN KEY (genus_id) REFERENCES genus (id)');
$this->addSql('ALTER TABLE genus_scientist ADD CONSTRAINT FK_66CF3FA8A76ED395 FOREIGN KEY (user_id) REFERENCES user (id)');
$this->addSql('ALTER TABLE genus_scientist ADD PRIMARY KEY (id)');
}
... lines 29 - 32
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('ALTER TABLE genus_scientist MODIFY id INT NOT NULL');
$this->addSql('ALTER TABLE genus_scientist DROP FOREIGN KEY FK_66CF3FA885C4074C');
$this->addSql('ALTER TABLE genus_scientist DROP FOREIGN KEY FK_66CF3FA8A76ED395');
$this->addSql('ALTER TABLE genus_scientist DROP PRIMARY KEY');
$this->addSql('ALTER TABLE genus_scientist DROP id, DROP years_studied');
$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');
$this->addSql('ALTER TABLE genus_scientist ADD PRIMARY KEY (genus_id, user_id)');
}
}

So freakin' cool! Because we already have the genus_scientist join table, the migration does not create any new tables. Nope, it simply modifies it: drops a couple of foreign keys, adds the id and years_studied columns, and then re-adds the foreign keys. Really, the only thing that changed of importance is that we now have an id primary key, and a years_studied column. But otherwise, the table is still there, just the way it always was.

If you try to run this migration...it will blow up, with this rude error:

Incorrect table definition; there can be only one auto column...

It turns out, Doctrine has a bug! Gasp! The horror! Yep, a bug in its MySQL code generation that affects this exact situation: converting a ManyToMany to a join entity. No worries: it's easy to fix... and I can't think of any other bug like this in Doctrine... and I use Doctrine a lot.

Take this last line: with ADD PRIMARY KEY id, copy it, remove that line, and then - after the id is added in the previous query - paste it and add a comma:

47 lines app/DoctrineMigrations/Version20161017160251.php
... lines 1 - 10
class Version20161017160251 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('ALTER TABLE genus_scientist DROP FOREIGN KEY FK_66CF3FA885C4074C');
$this->addSql('ALTER TABLE genus_scientist DROP FOREIGN KEY FK_66CF3FA8A76ED395');
$this->addSql('ALTER TABLE genus_scientist DROP PRIMARY KEY');
$this->addSql('ALTER TABLE genus_scientist ADD id INT AUTO_INCREMENT NOT NULL, ADD PRIMARY KEY (id), ADD years_studied VARCHAR(255) NOT NULL');
$this->addSql('ALTER TABLE genus_scientist ADD CONSTRAINT FK_66CF3FA885C4074C FOREIGN KEY (genus_id) REFERENCES genus (id)');
$this->addSql('ALTER TABLE genus_scientist ADD CONSTRAINT FK_66CF3FA8A76ED395 FOREIGN KEY (user_id) REFERENCES user (id)');
}
... lines 28 - 31
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('ALTER TABLE genus_scientist MODIFY id INT NOT NULL');
$this->addSql('ALTER TABLE genus_scientist DROP FOREIGN KEY FK_66CF3FA885C4074C');
$this->addSql('ALTER TABLE genus_scientist DROP FOREIGN KEY FK_66CF3FA8A76ED395');
$this->addSql('ALTER TABLE genus_scientist DROP PRIMARY KEY');
$this->addSql('ALTER TABLE genus_scientist DROP id, DROP years_studied');
$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');
$this->addSql('ALTER TABLE genus_scientist ADD PRIMARY KEY (genus_id, user_id)');
}
}

MySQL needs this to happen all in one statement.

But now, our migrations are in a crazy weird state, because this one partially ran. So let's start from scratch: drop the database fully, create the database, and then make sure all of our migrations can run from scratch:

./bin/console doctrine:database:drop --force
./bin/console doctrine:database:create
./bin/console doctrine:migrations:migrate

Success!

Now that we have a different type of relationship, our app is broken! Yay! Let's fix it and update our forms to use the CollectionType.

Leave a comment!

  • 2017-01-13 weaverryan

    Woohoo! I know what you mean - there are so many subtle options with relationships... but if you get them right, man, Doctrine relations really kill it.

    Cheers and good luck!

  • 2017-01-12 Matthias

    Thanks for looking into it, Ryan! That IS annoying, but at least now we know what's up. And also, this tutorial cleared up something I've been fighting with for a long time, so -- I owe you big time!

  • 2017-01-12 weaverryan

    Ah hah! I got it, finally! The tutorial uses doctrine/dbal 2.5.4, and there is a behavior change in doctrine/dbal 2.5.5! Here is the issue about it: https://github.com/doctrine...

    So, it's quite an annoying thing. There are 2 fixes:

    1) Downgrade to doctrine/dbal 2.5.4. This would mean adding the following line to your composer.json file:

    "doctrine/dbal": "2.5.4"

    Then run composer update

    2) Manually rename genus_scientist to something else (e.g. genus_scientist_old) and then generate the migration. Then, rename the table back. The generated migration will be *incorrect*, because it will think that you need to create a genus_scientist table, but we do not. So, you'll need to manually update the migration code by hand and test it.

    Ultimately, the bug in Doctrine only prevents us from automatically generating the migration file. If you can write that file by hand, or get it partially-generated and then fix it, all is right with the world after.

    Thanks for all the information guys - I couldn't reproduce this for a LONG time and you finally gave me enough information to do that!

    Cheers!

  • 2017-01-11 Matthias

    Any news on this problem? The exact thing is happening to me, and my entities look exactly like Greg's.

  • 2016-12-01 Stéphane

    I use MySQL Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper and doctrine/dbal v2.5.5.
    Cheers.

  • 2016-11-30 weaverryan

    Dang! Then, I wonder if it's a Doctrine or MySQL weird version problem. What version of MySQL do you have? And what version of doctrine/dbal? You can run composer info to find out.

    Thanks for letting me know - hopefully we can find out what the issue is :).

    Cheers!

  • 2016-11-30 Greg

    Hey Ryan
    I will send you my entities this evening for me ( I am french ).
    Thanks you again for your time.

    Sorry for the delay this is my entities
    https://gist.github.com/Gre...
    Like Stéphane , doctrine/dbal v2.5.5
    Cheers.

  • 2016-11-30 Stéphane

    Hey Ryan,
    For information, I have the same error than Greg also.

  • 2016-11-29 weaverryan

    Hey Greg!

    Ah, it does! Well, sort of :). I was thinking about the problem incorrectly, but your gist cleared it up. Basically, when Doctrine tries to calculate what is different in your genus_scientist table between the database and your annotations metadata, for some reason, it thinks that both the old and new tables have an index on it on the column id. The question is why? It could be a bug in Doctrine, after all, there is the small bug I mention in the screencast, even when the diff works. If that's that case, I'm not sure why you're seeing it and I'm not (I also haven't heard anyone else mention this yet, but this is also a new screencast - so time will tell). Or, there is something very subtly wrong in your code somewhere. If you are willing, I would love to see your GenusScientist, Genus and User entities, to see if I can spot anything.

    Cheers!

  • 2016-11-29 Greg

    Hey Ryan

    This is the result of the command

    https://gist.github.com/Gre...

    I hope that is help you to understand.

    Cheers.

  • 2016-11-29 weaverryan

    Hey Greg!

    I was just looking for a bit more information, because I'm not convinced I've given you enough to solve this yet (and I'm curious about what's going on). As I said earlier, for some reason, Doctrine is looking at your annotation metadata and expecting there to be an "id" column on your genus_scientist table and not finding it. This can happen for a few reasons:

    1) You have an Index above one of your entities referring to this column
    2) There is a foreign key constraint that references this column (this would be a JoinTable or JoinColumn - did you remove the JoinTable from Genus?)

    If none of the pointers are helping you find a possible cause, re-run the command that causes the error with a -vvv flag (so bin/console doctrine:migrations:diff -vvv) and then paste the output here / take a screenshot. I'm curious to see exactly where the error is coming from.

    Cheers!

  • 2016-11-28 Greg

    Yes I renamed it in sequel pro, but I have a migration with a create table not an alter table.

  • 2016-11-28 weaverryan

    > but if I rename my old genus_scientist table the migration is ok

    Do you mean that if you manually rename the table in your database, then the migration generates with no errors? If so, that's very interesting - let me know! :)

    Cheers!

  • 2016-11-28 Greg

    Hi Ryan

    Yes I have this error when I run the doctrine command. I use MySQL and I change the relation in Genus and User by the OneToMany.
    I do exactly the same as the tutorial but if I rename my old genus_scientist table the migration is ok.

    I will check again this evening.
    Thanks again for your really awesome works.
    Cheers

  • 2016-11-28 weaverryan

    Hey Greg!

    Hmm, when *exactly* do you get this error? Is it when you run doctrine:migrations:diff? And are you using MySQL or something different? Oh, and did you remove the ManyToMany on Genus and User (i.e. change them to OneToMany and give each the mappedBy option instead of inversedBy). And last thing :)... do you definitely have the @ORM\Column annotation above your id property in GenusScientist (with two stars to begin the comments - /**).

    I'm listing a bunch of possible tiny things because this error tells me two things:
    1) *Something* is *pointing* to an "id" column on genus_scientist. I'm not sure what this is. We added the id column in the screencast... but nothing really depends on it. Your error seems to suggest that there is either a foreign key or some sort of index that's referring to this.

    2) And of course, this error tells me that Doctrine doesn't see the `id` column on `genus_scientist` (you might have it in your database, but Doctrine doesn't see any evidence for it when it reads all of its mapping annotation metadata).

    Let me know if this helps! Cheers!

  • 2016-11-26 Greg

    Hey,

    I have a little error when I want to make my migrations:diff.

    "There is no column with name 'id' on table 'genus_scientist' "
    But I have this one.

    If I rename my old table the migration is ok and create the new table, how can I have like you an update and not a creation ?
    Thanks again.

    Greg

  • 2016-11-24 Victor Bocharsky

    Great! Sorry for no code blocks in the recent chapters - I'll add it soon

  • 2016-11-24 Sergiu Popa

    Thanks. Copy-pasta error


    [Mapping] OK - The mapping files are correct.
    [Database] OK - The database schema is in sync with the mapping files.
  • 2016-11-24 Victor Bocharsky

    Hey Sergiu,

    You should use "ManyToOne" relationships instead of "ManyToMany" in GenusScientist class, since you use "OneToMany" in Genus and User. I bet you have an invalid mapping if you do "bin/console doctrine:schema:validate" - it's a nice tip btw ;)

    So the result GenusScientist entity mapping should be:


    /**
    * @ORM\ ManyToOne(targetEntity="Genus", inversedBy="genusScientists")
    * @ORM\JoinColumn(nullable=true)
    */
    private $genus;

    /**
    * @ORM\ ManyToOne(targetEntity="User", inversedBy="studiedGenuses")
    * @ORM\JoinColumn(nullable=true)
    */
    private $user;

    Cheers!

  • 2016-11-24 Sergiu Popa

    Generating the migration creates two new tables: genus_scientist_genus, genus_scientist_user. How can I avoid that?

    GenusScientist.php


    /**
    * @ORM\ManyToMany(targetEntity="Genus", inversedBy="genusScientists")
    * @ORM\JoinColumn(nullable=true)
    */
    private $genus;

    /**
    * @ORM\ManyToMany(targetEntity="User", inversedBy="studiedGenuses")
    * @ORM\JoinColumn(nullable=true)
    */
    private $user;

    Genus.php


    /**
    * @ORM\OneToMany(targetEntity="GenusScientist", mappedBy="genus")
    */
    private $genusScientists;

    User.php


    /**
    * @ORM\OneToMany(targetEntity="GenusScientist", mappedBy="user")
    */
    private $studiedGenuses;