Encapsulate Reserved Words in Doctrine 2 Entities

When dealing with legacy applications and moving them to Doctrine (or any ORM), you will undoubtedly encounter poorly named columns and tables in need of migration. Unfortunately in the case of Doctrine 2, the default behavior for SQL query generation fails to encapsulate the column names properly, causing syntax errors with column names like “delete”.

To handle this, we can make use of the QuoteStrategy interface. First, create your strategy. Mine uses backticks for encapsulation:

<?php

namespace YourApp\SomeNamespace;

use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\ORM\Mapping\ClassMetadata;
use Doctrine\ORM\Mapping\QuoteStrategy;

/**
 * Handles backtick escaping
 */
class BacktickQuoteStrategy implements QuoteStrategy
{

    /**
     * @param string $inputString
     *
     * @return string
     */
    private function wrapStringInBackticks(string $inputString): string
    {
        return "`$inputString`";
    }

    /**
     * @param array $inputArray
     *
     * @return string
     */
    private function wrapArrayInBackticks(array $inputArray): string
    {
        array_walk($inputArray, function (&$x) {
            $x = "`$x`";
        });

        return implode(',', $inputArray);
    }

    /**
     * {@inheritdoc}
     */
    public function getColumnName($fieldName, ClassMetadata $class, AbstractPlatform $platform)
    {
        return $this->wrapStringInBackticks($class->fieldMappings[$fieldName]['columnName']);
    }

    /**
     * {@inheritdoc}
     */
    public function getTableName(ClassMetadata $class, AbstractPlatform $platform)
    {
        return $this->wrapStringInBackticks($class->table['name']);
    }

    /**
     * {@inheritdoc}
     */
    public function getSequenceName(array $definition, ClassMetadata $class, AbstractPlatform $platform)
    {
        return $this->wrapStringInBackticks($definition['sequenceName']);
    }

    /**
     * {@inheritdoc}
     */
    public function getJoinColumnName(array $joinColumn, ClassMetadata $class, AbstractPlatform $platform)
    {
        return $this->wrapStringInBackticks($joinColumn['name']);
    }

    /**
     * {@inheritdoc}
     */
    public function getReferencedJoinColumnName(array $joinColumn, ClassMetadata $class, AbstractPlatform $platform)
    {
        return $this->wrapStringInBackticks($joinColumn['referencedColumnName']);
    }

    /**
     * {@inheritdoc}
     */
    public function getJoinTableName(array $association, ClassMetadata $class, AbstractPlatform $platform)
    {
        return $this->wrapStringInBackticks($association['joinTable']['name']);
    }

    /**
     * {@inheritdoc}
     */
    public function getIdentifierColumnNames(ClassMetadata $class, AbstractPlatform $platform)
    {
        return $this->wrapArrayInBackticks($class->identifier);
    }

    /**
     * {@inheritdoc}
     */
    public function getColumnAlias($columnName, $counter, AbstractPlatform $platform, ClassMetadata $class = null)
    {
        return $this->wrapStringInBackticks($platform->getSQLResultCasing($columnName . '_' . $counter));
    }
}

Next, you’ll need to add this to your configuration:

<?php

use Doctrine\ORM\Tools\Setup;
use YourApp\SomeNamespace\BacktickQuoteStrategy;

...

$config = Setup::createAnnotationMetadataConfiguration();
$config->setQuoteStrategy(new BacktickQuoteStrategy());

And that’s it! From here on your application can use all the reserved words it wants (until you have time to migrate those to better names at least).

Advertisements