Doctrine 2 DQL MySQL equivalent to ROUND()?

Question

I know from the documentation at: http://docs.doctrine-project.org/en/2.1/reference/dql-doctrine-query-language.html#dql-functions that there is no ROUND function but is there an easy way to do it without writing my own DQL class function?

Edit: I would not need an exact match if doing an average and returning a whole number is possible.

Answer 1

You need to implement a custom DQL function for that.

There's some examples in DoctrineExtensions.

You can implement it like following:

<?php

namespace MyApp\DQL;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\SqlWalker;

class Round extends FunctionNode
{
    private $arithmeticExpression;

    public function getSql(SqlWalker $sqlWalker)
    {

        return 'ROUND(' . $sqlWalker->walkSimpleArithmeticExpression(
            $this->arithmeticExpression
        ) . ')';
    }

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {

        $lexer = $parser->getLexer();

        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->arithmeticExpression = $parser->SimpleArithmeticExpression();

        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

You can then register it in the configuration while bootstrapping the ORM:

$config = new \Doctrine\ORM\Configuration();

$config->addCustomNumericFunction('ROUND', 'MyApp\DQL\Round');

Answer 2

A bit cleaner approach would be using slightly modified @Ocramius code.

Put this piece of code in: src/YourNamespace/YourMainBundle/DoctrineFunctions/ directory as the Round.php filename:

<?php
namespace YourApp\YourMainBundle\DoctrineFunctions;

use Doctrine\ORM\Query\AST\Functions\FunctionNode,
    Doctrine\ORM\Query\Lexer;

class Round extends FunctionNode
{
    private $arithmeticExpression;

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {

        $lexer = $parser->getLexer();

        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->arithmeticExpression = $parser->SimpleArithmeticExpression();

        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return 'ROUND(' . $sqlWalker->walkSimpleArithmeticExpression($this->arithmeticExpression) . ')';
    }
}

Then put this in your app/config/config.yml:

doctrine:
    dql:
        numeric_functions:
            round: YourApp\YourMainBundle\DoctrineFunctions\Round

That would allow you to use the ROUND() function directly in your DQL SELECT queries; no matter if done with QueryBuilder or directly via createQuery()