In symfony 4.4 I try to create ilike dql function. Because it's not possible to use ILIKE in query builder. Only LIKE with LOWER. But it's not possible to use index with LIKE and LOWER.
I found some example of DQL function:
orm:
auto_generate_proxy_classes: '%kernel.debug%'
default_entity_manager: client
entity_managers:
client:
naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
auto_mapping: true
connection: dbalclient
mappings:
Client:
is_bundle: false
type: annotation
dir: '%kernel.project_dir%/src/Entity/Client'
prefix: 'App\Entity\Client'
alias: Client
dql:
string_functions:
ILike: App\DQL\ILike
vente:
Here, this is my function:
namespace App\DQL;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
class ILike extends FunctionNode
{
public $field;
public $value;
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
return $sqlWalker->getConnection()->getDatabasePlatform()->getILikeExpression(
$sqlWalker->walkArithmeticExpression($this->field),
$sqlWalker->walkArithmeticExpression($this->value)
);
}
public function parse(\Doctrine\ORM\Query\Parser $parser)
{
$parser->match(Lexer::T_IDENTIFIER);
$parser->match(Lexer::T_OPEN_PARENTHESIS);
$this->field = $parser->ArithmeticPrimary();
$parser->match(Lexer::T_COMMA);
$this->value = $parser->ArithmeticPrimary();
$parser->match(Lexer::T_CLOSE_PARENTHESIS);
}
}
In my controller, I call my function in my query builder:
$clientRepository = $this->emc->getRepository(Client::class);
/** @var ORMQueryBuilder */
$searchQuery = $clientRepository->createQueryBuilder('c');
$searchQuery->select('partial c.{id}');
if (isset($name)) {
$searchQuery->andWhere('ILIKE(c.name, :name)');
$searchQuery->setParameter('name', '%'.addslashes($nom).'%');
}
I get this error:
It's a requirement to specify a Metadata Driver and pass it to Doctrine\ORM\Configuration::setMetadataDriverImpl(). (500 Internal Server Error)