Mysql SOUNDEX function in Symfony with Doctrine

If you want to perform SOUNDEX similarity searches with Doctrine in Symfony, you first need to define it as a so called "DQL User Defined Functions". Resources online are a bit dated so I decided to publish this quick blag post. The core is a child class of Doctrine\ORM\Query\AST\Functions\FunctionNode which you can define in src/Doctrine/SoundexFunction.php:

<?php declare(strict_types=1);

namespace App\Doctrine;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\AST\Node;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\QueryException;
use Doctrine\ORM\Query\SqlWalker;
use RuntimeException;

class SoundexFunction extends FunctionNode
{
    private ?Node $node = null;

    /**
     * @throws QueryException
     */
    public function parse(Parser $parser): void
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->node = $parser->StringPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(SqlWalker $sqlWalker): string
    {
        if ($this->node === null) {
            throw new RuntimeException();
        }

        return sprintf('SOUNDEX(%s)', $this->node->dispatch($sqlWalker));
    }
}

To make it available in Symfony just configure it in config/packages/doctrine.yaml:

doctrine:
    orm:
        dql:
            string_functions:
                SOUNDEX: App\Doctrine\SoundexFunction

This makes the function SOUNDEX available in all query builders:

<?php declare(strict_types=1);

namespace App\Repository;

use App\Entity\Country;
use App\Exception\CountryNotFoundException;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;

/**
 * @template-extends ServiceEntityRepository<Country>
 */
class CountryRepository extends ServiceEntityRepository
{
    /**
     * @return Country[]
     */
    public function findSimilar(string $name): array
    {
        $qb = $this->createQueryBuilder('c');
        $qb->expr()->eq('SOUNDEX(c.name)', 'SOUNDEX(:name)');
        $qb->setParameters(['name' => $name]);
        return $qb->getQuery()->getResult();
    }
}

# Resources * https://ourcodeworld.com/articles/read/243/how-to-implement-soundex-search-in-mysql-with-doctrine-and-symfony-3 * https://www.doctrine-project.org/projects/doctrine-orm/en/2.14/cookbook/dql-user-defined-functions.html * https://symfony.com/doc/5.4/doctrine/custom_dql_functions.html