ManyToMany with Extra Fields

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.

Start your All-Access Pass
Buy just this tutorial for $12.00

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:

... 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:

... 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:

... 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:

... 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:

... 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:

... 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:

... 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:

... 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:

... 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:

... 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:

... 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:

... 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:

... 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:

... 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!

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": ">=5.5.9",
        "symfony/symfony": "3.1.*", // v3.1.4
        "doctrine/orm": "^2.5", // v2.7.2
        "doctrine/doctrine-bundle": "^1.6", // 1.6.4
        "doctrine/doctrine-cache-bundle": "^1.2", // 1.3.0
        "symfony/swiftmailer-bundle": "^2.3", // v2.3.11
        "symfony/monolog-bundle": "^2.8", // 2.11.1
        "symfony/polyfill-apcu": "^1.0", // v1.2.0
        "sensio/distribution-bundle": "^5.0", // v5.0.22
        "sensio/framework-extra-bundle": "^3.0.2", // v3.0.16
        "incenteev/composer-parameter-handler": "^2.0", // v2.1.2
        "knplabs/knp-markdown-bundle": "^1.4", // 1.4.2
        "doctrine/doctrine-migrations-bundle": "^1.1", // 1.1.1
        "stof/doctrine-extensions-bundle": "^1.2" // v1.2.2
    },
    "require-dev": {
        "sensio/generator-bundle": "^3.0", // v3.0.7
        "symfony/phpunit-bridge": "^3.0", // v3.1.3
        "nelmio/alice": "^2.1", // 2.1.4
        "doctrine/doctrine-fixtures-bundle": "^2.3" // 2.3.0
    }
}