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();
}
}