UNIX_TIMESTAMP, ROUND and other DQL queries via queryBuilder in Symfony 2

As is known, in Symfony 2 in Doctrine 2 from the “box” there is no support for some standard Mysql functions, such as UNIX_TIMESTAMP or ROUND and a few others. First of all, an article in order to understand how to supplement DQL with its functions. But before writing how to “cheat” and “githabit”, and suddenly someone has already written, I advise you not to fence bicycles and use ready-made practices, for example GitHub MysqlDoctrineFunctions .

The article is more suitable for beginners.

So, the task! Make the ROUND function, let 's get started:

The first thing we need to do is make a description of our method through FunctionNode from Doctrine \ ORM \ Query \ AST \ Functions \ FunctionNode .
Create in our Bundle a folder with the name DQL (you can of course call as you like) .
I have it looks like this: src / Acme / SimpleBundle / DQL
')
Create a file in this directory with a name such as Round.php , it turns out src / Acme / SimpleBundle / DQL / Round.php
Content:

namespace Acme\SimpleBundle\DQL; use Doctrine\ORM\Query\Lexer; use Doctrine\ORM\Query\AST\Functions\FunctionNode; class Round extends FunctionNode { protected $roundExp; protected $roundPrecission; public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker) { return 'ROUND(' . $sqlWalker->walkArithmeticExpression($this->roundExp) . ','. $sqlWalker->walkArithmeticExpression($this->roundPrecission) .')'; } /** * parse - allows DQL to breakdown the DQL string into a processable structure * @param \Doctrine\ORM\Query\Parser $parser */ public function parse(\Doctrine\ORM\Query\Parser $parser) { $parser->match(Lexer::T_IDENTIFIER); $parser->match(Lexer::T_OPEN_PARENTHESIS); $this->roundExp = $parser->ArithmeticExpression(); //     $parser->match(Lexer::T_COMMA); //   $this->roundPrecission = $parser->ArithmeticExpression(); //     $parser->match(Lexer::T_CLOSE_PARENTHESIS); } } 

We need to implement two functions, the getSql function, as is clear from the name, will return prepared SQL to ORM, and parse is intended for parsing variables passed to the query, for example round (sum, 2)

In order to pass parameters to a function, we need to define internal variables, in this example it is:

  protected $roundExp; protected $roundPrecission; //        round(roundExp, roundPrecission) 

Next, we need to tell the doctrine where to look for our functions, for this we indicate in the config.yml in the doctrine section : that we have additions to the standard DQL , for me it looks like this:

 doctrine: dbal: driver: %database_driver% host: %database_host% port: %database_port% dbname: %database_name% user: %database_user% password: %database_password% charset: UTF8 orm: auto_generate_proxy_classes: %kernel.debug% auto_mapping: true dql: string_functions: unix_timestamp: \Acme\SimpleBundle\DQL\UnixTimestamp numeric_functions: round: \Acme\SimpleBundle\DQL\Round 

Now when executing a query like:

 $queryBuilder->andWhere("ROUND (sum) , 1) = :condition"); 

The doctrine will get into our functions and make up the correct query in Mysql.

At the request of NeonXP , the unix_timestamp function has been added :

 namespace Acme\SimpleBundle\DQL; use Doctrine\ORM\Query\Lexer; use Doctrine\ORM\Query\AST\Functions\FunctionNode; class Round extends FunctionNode { protected $arithmeticExprt; public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker) { return 'UNIX_TIMESTAMP(' . $sqlWalker->walkArithmeticExpression($this->arithmeticExprt) .')'; } /** * parse - allows DQL to breakdown the DQL string into a processable structure * @param \Doctrine\ORM\Query\Parser $parser */ public function parse(\Doctrine\ORM\Query\Parser $parser) { $parser->match(Lexer::T_IDENTIFIER); $parser->match(Lexer::T_OPEN_PARENTHESIS); $this->arithmeticExprt = $parser->ArithmeticExpression(); //    $parser->match(Lexer::T_CLOSE_PARENTHESIS); } }