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;

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

    /**
     * @throws QueryException
     */
    public function parse(Parser $parser)
    {
        $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
    {
        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

Article Link: Mysql SOUNDEX function in Symfony with Doctrine – nullteilerfrei