As most Symfony developers know, Doctrine 2 is the most frequently used ORM for Symfony 2, and perhaps the most popular ORM for PHP, period. (ORM stands for "Object Relational Mapper" - a layer that translates between PHP objects and raw SQL programming, ideally saving you a lot of work and time.)
Doctrine 2 is a lot faster than Doctrine 1, and the new "DQL" (Doctrine Query Language) is much more stable than the old. There's a price to be paid for that though: DQL no longer "contains" every function of plain old SQL... because it is no longer just a thin wrapper around SQL that lets you stuff in whatever SQL you like. The new DQL parser actually parses the code and won't let you do illegal things. So if DQL doesn't know about a SQL function, it won't let you use it. Fortunately there's a right way to get past that barrier.
Today I used one of Doctrine 2's more impressive features: you can extend the DQL parser. Which is good, because it's missing lots of functions standard in MySQL, but not standard across many databases, and therefore left out of DQL. Specifically, I needed MySQL's REGEXP operator to port a sticky bit of raw SQL in an older application that undeniably did a great job matching names on word boundaries.
So how do you do it? There are tutorials for Doctrine that don't mention Symfony, and there's a Symfony tutorial that blows by the subject quickly without showing code. I thought I'd fill in the gap with some sample code.
Create a subclass of \Doctrine\ORM\Query\AST\Functions\FunctionNode, like this. Let's say we'll be naming the class RegexpTestFunction and keeping it in the src\Punk\SomeBundle\DoctrineFunctions folder. Here's how the code looks:
<?php
namespace Punk\SomeBundle\DoctrineFunctions;
use Doctrine\ORM\Query\Lexer;
class RegexpTestFunction extends \Doctrine\ORM\Query\AST\Functions\FunctionNode
{
public $regexpExpression = null;
public $valueExpression = null;
public function parse(\Doctrine\ORM\Query\Parser $parser)
{
$parser->match(Lexer::T_IDENTIFIER);
$parser->match(Lexer::T_OPEN_PARENTHESIS);
$this->regexpExpression = $parser->StringPrimary();
$parser->match(Lexer::T_COMMA);
$this->valueExpression = $parser->StringExpression();
$parser->match(Lexer::T_CLOSE_PARENTHESIS);
}
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
return '(' . $this->valueExpression->dispatch($sqlWalker) . ' REGEXP ' .
$sqlWalker->walkStringPrimary($this->regexpExpression) . ')';
}
}
The parse() function is responsible for parsing the DQL, while the getSql() function is responsible for outputting SQL (in this case, MySQL, since I know I'll be using that database). StringPrimary is the right parsing method to use to match a literal quoted string like a regexp pattern, while StringExpression matches a DQL expression such as u.displayname. Calling dispatch($sqlWalker) on the value expression outputs appropriate SQL for that, while $sqlWalker->walkStringPrimary is appropriate for a string literal. Note that my regexp_test function takes its arguments in the same order as preg_match, rather than the way the regular regexp operator does it.
But to actually make the regexp_test() function available in DQL, you'll need to register it. That's easy to do in your config.yml file where you configure Doctrine:
doctrine:
orm:
dql:
numeric_functions:
regexp_test: Punk\SomeBundle\DoctrineFunctions\RegexpTestFunction
You can also register functions that return strings and datetimes. Technically you can't register a comparison operator, which is why I implemented regexp_test as a numeric function taking two arguments rather than an operator sitting between them; since MySQL's comparison operators return 0 or 1 anyway, it all works out in the end.
