I'm in the process of rolling out a feature to all clients, but some clients already have existing data, and I cannot override the content present in their meal_moment table.

I need to create a default meal_moment with ID 0 for all clients who don't have any meal_moment data, while also updating all orders to mealmoment_id 0, as I need to override that.

Clients where the feature is already deployed have a meal_moment with ID 0.

Here is my migration code:

public function up(Schema $schema): void
{
    // if no mealmoment with the id 0 exists, create it
    $this->addSql("
        INSERT INTO `meal_moment` (`id`, `name`, `code`, `ordre`, `customer_id`)
        VALUES (0, 'None', 'None', 0, 0);
        UPDATE `order` SET mealmoment_id = 0 WHERE mealmoment_id IS NULL
    ");
}

public function down(Schema $schema): void
{
    $this->addSql('UPDATE `order` SET mealmoment_id = NULL WHERE mealmoment_id = 0');
    $this->addSql('DELETE FROM meal_moment WHERE id = 0');
}

This craches approch craches for some clients but works in our dev envs where there is no mealmoment. Also, for a reason I can't find, the id = 0 in the insert always gets replaced by id = 1

Thanks for your help and suggestions !

I'm in the process of rolling out a feature to all clients, but some clients already have existing data, and I cannot override the content present in their meal_moment table.

I need to create a default meal_moment with ID 0 for all clients who don't have any meal_moment data, while also updating all orders to mealmoment_id 0, as I need to override that.

Clients where the feature is already deployed have a meal_moment with ID 0.

Here is my migration code:

public function up(Schema $schema): void
{
    // if no mealmoment with the id 0 exists, create it
    $this->addSql("
        INSERT INTO `meal_moment` (`id`, `name`, `code`, `ordre`, `customer_id`)
        VALUES (0, 'None', 'None', 0, 0);
        UPDATE `order` SET mealmoment_id = 0 WHERE mealmoment_id IS NULL
    ");
}

public function down(Schema $schema): void
{
    $this->addSql('UPDATE `order` SET mealmoment_id = NULL WHERE mealmoment_id = 0');
    $this->addSql('DELETE FROM meal_moment WHERE id = 0');
}

This craches approch craches for some clients but works in our dev envs where there is no mealmoment. Also, for a reason I can't find, the id = 0 in the insert always gets replaced by id = 1

Thanks for your help and suggestions !

• php
• sql
• mysql
• doctrine

• 1 Is there any good reason to set 0 as an ID (see this question) ? Also, how does the migration "crashes for some clients" ? – AymDev Commented Mar 12 at 16:14
• There's nothing stopping you running other queries first in a migration ($this->getEntityManager()->getConnection()->createQueryBuilder()...) and then checking the result. But I'd echo the comment about the design: why do you need to set a relationship to an "empty" row rather than just leaving it as null? What's the difference? – iainn Commented Mar 12 at 16:52
• The 0/1 ID issue is likely related to NO_AUTO_VALUE_ON_ZERO – iainn Commented Mar 12 at 16:56
• @AymDev Yes, we know for a fact that 0 is an unsed value in all our clients databases even for those that have the feature already enabled – kekaaafm Commented Mar 12 at 20:49
• 1 @iainn For the "why set a relationship when I can leave it empty, I can't due to a design in the application. Years ago, the first dev choose to use "EasyAdmin", I don't quite have the right term but it's allowed the previous developpers to "skip" the "front-end" part of the app. The thing is, EA wants everything to be defined at all cost. It's a nightmare but in our dev envs, when I leave the relation as "Empty" it just refuses to load the page. For the 0/1 issue, I think you're right, I'll try that next thing tomorrow and let you know ! Thanks a lot for your help ! – kekaaafm Commented Mar 12 at 20:53

1 Answer 1

The solution turned out to be quite straightforward, and I'm a bit embarrassed that I didn't realize it earlier. I needed to access the protected connection property of the parent class and execute a query to conditionally handle my migration. (Thanks to @iainn for the suggestion.)

For the specific case where id = 0, @AymDev was correct in pointing out that it was a SQL mode issue. The solution involved setting the SQL mode to include NO_AUTO_VALUE_ON_ZERO before the insertion and then resetting it afterward.

public function up(Schema $schema): void
{

    $r = $this->connection->prepare('SELECT COUNT(*) FROM meal_moment WHERE id = 0')
        ->executeQuery()
        ->fetchOne();

    if ($r == 0) {
        $modes = $this->connection->prepare("SELECT @@sql_mode;")
            ->executeQuery()
            ->fetchOne();

        $this->addSql("
            SET sql_mode = CONCAT(@@sql_mode, ',NO_AUTO_VALUE_ON_ZERO');
            INSERT INTO `meal_moment` (`id`, `name`, `code`, `ordre`, `customer_id`) VALUES (0, 'Aucun', 'Aucun', 0, 0);
            SET sql_mode = :modes
            ", ['modes' => $modes]);

    }
    $this->addSql("UPDATE `order` SET mealmoment_id = 0 WHERE mealmoment_id IS NULL");

}

public function down(Schema $schema): void
{
    $this->addSql('UPDATE `order` SET mealmoment_id = NULL WHERE mealmoment_id = 0');
    $this->addSql('DELETE FROM meal_moment WHERE id = 0');

}