Symfony2: Doctrine MySql math functions

Question

I am trying to get the number of people by age.

AGEBRACKET | NBR 10 | 3 20 | 14 30 | 123 40 | 4 50 | 55 ... 

This is my code:

 $qb = $em->createQueryBuilder(); $qb->select('FLOOR((YEAR(CURDATE())-YEAR(p.date_birth)) / 10) * 10 AS age, COUNT(p.id)'); $qb->from('MyBundle:Person', 'p'); $qb->groupBy('age'); $countByAge = $qb->getQuery()->execute(); 

I get this error:

[Syntax error] row 0, column 7: Error: expected known function received 'Floor'

I was looking a bit for a solution, and here is what I found:

 <?php namespace MyProject\Query\AST; use \Doctrine\ORM\Query\AST\Functions\FunctionNode; use \Doctrine\ORM\Query\Lexer; class MysqlFloor extends FunctionNode { public $simpleArithmeticExpression; public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker) { return 'FLOOR(' . $sqlWalker->walkSimpleArithmeticExpression( $this->simpleArithmeticExpression ) . ')'; } public function parse(\Doctrine\ORM\Query\Parser $parser) { $lexer = $parser->getLexer(); $parser->match(Lexer::T_IDENTIFIER); $parser->match(Lexer::T_OPEN_PARENTHESIS); $this->simpleArithmeticExpression = $parser->SimpleArithmeticExpression(); $parser->match(Lexer::T_CLOSE_PARENTHESIS); } } <?php \Doctrine\ORM\Query\Parser::registerNumericFunction('FLOOR', 'MyProject\Query\MysqlFloor'); $dql = "SELECT FLOOR(person.salary * 1.75) FROM CompanyPerson person"; 

And I get another error:

 Attempted to call method "registerNumericFunction" on class "Doctrine\ORM\Query\Parser". 

You know how I can do to get the desired result.

thanks

Answer 1

There is an updated version in the Doctrine docs that should help you: http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html#adding-your-own-functions-to- the-dql-language

If you want to add it to your Symfony configuration so that it can be used everywhere in your project, see http://symfony.com/doc/current/cookbook/doctrine/custom_dql_functions.html how you can do it.

Answer 2

Decision:

 #config.yml orm: dql: numeric_functions: FLOOR: FrontBundle\DoctrineFunctions\FloorFunction 

---

 #FloorFunction.php <?php namespace MyBundle\DoctrineFunctions; use \Doctrine\ORM\Query\AST\Functions\FunctionNode; use \Doctrine\ORM\Query\Lexer; class FloorFunction extends FunctionNode { public $simpleArithmeticExpression; public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker) { return 'FLOOR(' . $sqlWalker->walkSimpleArithmeticExpression( $this->simpleArithmeticExpression ) . ')'; } public function parse(\Doctrine\ORM\Query\Parser $parser) { $parser->match(Lexer::T_IDENTIFIER); $parser->match(Lexer::T_OPEN_PARENTHESIS); $this->simpleArithmeticExpression = $parser->SimpleArithmeticExpression(); $parser->match(Lexer::T_CLOSE_PARENTHESIS); } } 

---

 $config = $em->getConfiguration(); $config->addCustomNumericFunction('FLOOR', 'MyBundle\DoctrineFunctions\FloorFunction');