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". <a href="https://ourcodeworld.com/articles/read/243/how-to-implement-soundex-search-in-mysql-with-doctrine-and-symfony-3">Resources online</a> are a bit dated so I decided to publish this quick blag post.
<span id="more-5844"></span>
The core is a child class of `Doctrine\ORM\Query\AST\Functions\FunctionNode` which you can define in `src/Doctrine/SoundexFunction.php`:
```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`:
```yaml
doctrine:
orm:
dql:
string_functions:
SOUNDEX: App\Doctrine\SoundexFunction
```
This makes the function `SOUNDEX` available in all query builders:
```php
<?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