CODEWITHSUNDEEP
doctrine-ormdoctrinesymfony4
alex
alex

Reputation: 307

Doctrine and quoting reserved word of MySql

I have in my Symfony 4.3 project entity named Group and table group in MySql database. Group is a reserved word. I have 3 entities : User, Group, UserGroup. They have relations: User ->OneToMany-> UserGroup and Group -> OneToMany -> UserGroup ( UserGroup has additional attributes, so I can't use ManyToMany relation). When I try to get group names for user with id=1:

$entityManager = $this->getDoctrine()->getManager();
   $user = $entityManager->getRepository(User::class)
        ->find($id);
   $groups = $user->getUserGroups();

   foreach ($groups as $group) {
        $group_name = $group->getGroupId()->getName();
        echo $group_name, '<br>';
    }

I get error message :

You have an error: An exception occurred while executing 'SELECT t0.id AS id_1, t0.name AS name_2 FROM group t0 WHERE t0.id = ?' with params [1]: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group t0 WHERE t0.id = 1' at line 1 with code: 0

There is a recipe in documentation (http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/basic-mapping.html#quoting-reserved-words) how to enable quoting of reserved words:

<?php
use Doctrine\ORM\Mapping\AnsiQuoteStrategy;

$configuration->setQuoteStrategy(new AnsiQuoteStrategy());

I don't understand where I have to use this code - in EntityManager, in Controller? And how I can get $configuration?

Upvotes: 1

Views: 2277

Answers (1)

vely
vely

Reputation: 111

I think the better solution is to use ticks in the definition of your entity as mentioned in Doctrine Documentation.

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass="App\Repository\GroupRepository")
 * @ORM\Table(name="`group`")
 */
class Group
{
    // ...
}

Note, that this approach will work only if you are using the default quote strategy. So, delete quote_strategy parameter or set it as doctrine.orm.quote_strategy.default.

# config/packages/doctrine.yml

doctrine:
    # ...
    orm:
        quote_strategy: doctrine.orm.quote_strategy.default

Upvotes: 4

Related Questions