doctrine2: how to convert a one-to-many to a many-to-many without losing data

Question

In my application, I want to convert a one-to-many to a many-to-many without losing the data :

from:

/**
 * @ORM\ManyToOne(targetEntity="\AppBundle\Entity\FoodAnalytics\Recipe", inversedBy="medias")
 * @ORM\JoinColumn(name="recipeId", referencedColumnName="id", onDelete="CASCADE")
 */
protected $recipe;

to:

/**
 * @ORM\ManyToMany(targetEntity="\AppBundle\Entity\FoodAnalytics\Recipe", inversedBy="medias")
 * @ORM\JoinTable(
 *     name="media_recipes",
 *     joinColumns={@ORM\JoinColumn(name="mediaId", referencedColumnName="id", onDelete="CASCADE")},
 *     inverseJoinColumns={@ORM\JoinColumn(name="recipeId", referencedColumnName="id", onDelete="CASCADE")}
 * )
 */
protected $recipes;

When I dump my doctrine shema update, it says it's going to drop the data, not what I want :

CREATE TABLE media_recipes (mediaId INT UNSIGNED NOT NULL, recipeId INT UNSIGNED NOT NULL, INDEX IDX_C2BE64FC27D9F5AC (mediaId), INDEX IDX_C2BE64FC6DCBA54 (recipeId), PRIMARY KEY(mediaId, recipeId)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
ALTER TABLE media_recipes ADD CONSTRAINT FK_C2BE64FC27D9F5AC FOREIGN KEY (mediaId) REFERENCES media (id) ON DELETE CASCADE;
ALTER TABLE media_recipes ADD CONSTRAINT FK_C2BE64FC6DCBA54 FOREIGN KEY (recipeId) REFERENCES Recipe (id) ON DELETE CASCADE;
ALTER TABLE media DROP FOREIGN KEY FK_6A2CA10C6DCBA54;
DROP INDEX IDX_6A2CA10C6DCBA54 ON media;
ALTER TABLE media DROP recipeId;

Process finished with exit code 0 at 13:04:46.
Execution time: 5 003 ms.

How can I solve this ? i.e. have the previous mysql mediaId column of the recipe table be added to the new media_recipe table along with the recipe id ?

You should use DoctrineMigrationsBundle for updating production schemas — Emii Khaos, May 24 '15 at 19:49
I have it installed but does it really handle converting a one-to-many to a many-to-many ? if yes, do you know the specific commands? — Sébastien, May 24 '15 at 22:50
Please read the docs, about creating migrations files from the diff of entities and existing database — Emii Khaos, May 24 '15 at 23:31

score 11 · Accepted Answer · answered May 25 '15 at 14:59

OK, I found a way using doctrine migrations indeed.

I first made sure my changes where correctly set using a doctrine:schema:update --force, checking the profiler, and then reverting to the last database.

So using doctrine migrations bundle :

doctrine:migrations:diff

creates a new migration class

edit the class to suit your needs. Mine was :

<?php

namespace Application\Migrations;

use AppBundle\Entity\Core\Media;
use AppBundle\Entity\FoodAnalytics\Recipe;
use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;
use Symfony\Component\DependencyInjection\Container;
use Symfony\Component\DependencyInjection\ContainerAwareInterface;
use Symfony\Component\DependencyInjection\ContainerInterface;

/**
 * Auto-generated Migration: Please modify to your needs!
 */
class Version20150525154902 extends AbstractMigration implements ContainerAwareInterface
{

    private $customSQL = array();

    /** @var Container */
    private $container;


    public function setContainer(ContainerInterface $container = null)
    {
        $this->container = $container;
    }

    /**
     * @param Schema $schema
     */
    public function preUp(Schema $schema)
    {
        $query = "SELECT id as mediaId, recipeId FROM `media` WHERE recipeId IS NOT NULL";
        $data = $this->connection->prepare($query);
        $data->execute();
        foreach ($data as $row)
        {
            $mediaId = $row['mediaId'];
            $recipeId = $row['recipeId'];
            $this->customSQL[] = "($mediaId, $recipeId)";
        }

    }

    /**
     * @param Schema $schema
     */
    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 media_recipes (mediaId INT UNSIGNED NOT NULL, recipeId INT UNSIGNED NOT NULL, INDEX IDX_C2BE64FC27D9F5AC (mediaId), INDEX IDX_C2BE64FC6DCBA54 (recipeId), PRIMARY KEY(mediaId, recipeId)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('ALTER TABLE media_recipes ADD CONSTRAINT FK_C2BE64FC27D9F5AC FOREIGN KEY (mediaId) REFERENCES media (id) ON DELETE CASCADE');
        $this->addSql('ALTER TABLE media_recipes ADD CONSTRAINT FK_C2BE64FC6DCBA54 FOREIGN KEY (recipeId) REFERENCES Recipe (id) ON DELETE CASCADE');
        $this->addSql('ALTER TABLE media DROP FOREIGN KEY FK_6A2CA10C6DCBA54');
        $this->addSql('DROP INDEX IDX_6A2CA10C6DCBA54 ON media');
        $this->addSql('ALTER TABLE media DROP recipeId');
        $this->addSql('ALTER TABLE recipe ADD versionDetails VARCHAR(200) DEFAULT NULL');

    }

    public function postUp(Schema $schema)
    {
        $SQL = 'INSERT INTO media_recipes (mediaId, recipeId) VALUES ' . implode(', ', $this->customSQL);

        $this->connection->executeQuery($SQL);
    }



    /**
     * @param Schema $schema
     */
    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 media_recipes');
        $this->addSql('ALTER TABLE media ADD recipeId INT UNSIGNED DEFAULT NULL');
        $this->addSql('ALTER TABLE media ADD CONSTRAINT FK_6A2CA10C6DCBA54 FOREIGN KEY (recipeId) REFERENCES recipe (id) ON DELETE CASCADE');
        $this->addSql('CREATE INDEX IDX_6A2CA10C6DCBA54 ON media (recipeId)');
    }
}

then perform the update :

doctrine:migrations:migrate

and confirm y

This seems overly complicated when you can just write one single insert statement that copies the data. For me it was only one single line I had to add to the generated migration. See my answer below. — Alexander Rechsteiner, Jan 04 '19 at 13:09

score 9 · Answer 2 · answered Jan 04 '19 at 13:04

I have solved this by simply generating a migration with php bin/console make:migration and then extending the migration with an INSERT statement that copies all the data from the old oneToMany field into the new manyToMany table before dropping the old column.

To stick with the media/recipes example from the question, I would add this line just before the $this->addSql('ALTER TABLE media DROP recipeId'); line in the generaded migration:

$this->addSql('INSERT INTO media_recipes (mediaId, recipeId) SELECT id, recipeId FROM media WHERE recipeId IS NOT NULL');

Very helpful when you want a simple migration. For more complex ones, the accepted answer is more flexible, of course. — krom, Feb 19 '19 at 13:40

score 3 · Answer 3 · answered Sep 16 '15 at 20:55

If you want to continue using orm:schema-tool:update --force instead of adopting doctrine:migrations:migrate, then you can migrate a relationship from many-to-one to many-to-many using a two-step process.

Create a many-to-many-step1 branch
- Add the new @ManyToMany/@JoinTable properties and accessors alongside the old @ManyToOne/@JoinColumn/@OneToMany properties and accessors
- Deploy the code and run orm:schema-tool:update --force
- Populate the new join table with the old join column contents using an INSERT INTO ... SELECT statement
Create a many-to-many-step2 branch
- Change the code to use the new many-to-many relationship and accessors
- Remove all artifacts of the old many-to-one relationship
- Deploy the code and run orm:schema-tool:update --force

Thalys Menezes · Answer 4 · 2022-07-15T22:48:34.310

One easy way to do that is simply just drop the unique constraint of that column with the following command:

ALTER TABLE relation_table DROP INDEX UK_constraint_id;

If you have MySql Workbench installed it is even easier, because you don't need to discover the constraint id. Just select your table -> alter table and unmark the UQ checkbox of the column and then click on "Apply".

If you do not have MySql Workbench installed you can do the following query to find out the constraint id:

USE INFORMATION_SCHEMA;
SELECT TABLE_NAME,
       COLUMN_NAME,
       CONSTRAINT_NAME,
       REFERENCED_TABLE_NAME,
       REFERENCED_COLUMN_NAME
FROM KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = "<schema_name>" 
      AND TABLE_NAME = "<table_name>";

and just use the ALTER TABLE mentioned above.

doctrine2: how to convert a one-to-many to a many-to-many without losing data

4 Answers4

Linked