CASTING attributes for Ordering on a Doctrine2 DQL Query

Question

I am trying to get Doctrine2 Entities, ordered by their ID which apparently is a String even though it contains only Numbers. So what I would like to do is something like this:

SELECT entity1, cast (entity1.id AS integer) AS orderId
FROM Namespace\Bla\MyEntity 
ORDER BY orderId

Is there a way to do something like this in Doctrine2? Or, what would be the best practise to get my Result if i can't change the type of the id (due to customer requirements of course)?

---

Attention: I am not asking SQL Code, i am asking for a Doctrine2 Solution, preferably in DQL

Answer 1

You should be able to add your own function to implement this feature.

The class would look something like this:

namespace MyProject\Query;

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

class CastAsInteger extends FunctionNode
{
    public $stringPrimary;

    public function getSql(SqlWalker $sqlWalker)
    {
        return 'CAST(' . $this->stringPrimary->dispatch($sqlWalker) . ' AS integer)';
    }

    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->stringPrimary = $parser->StringPrimary();

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

You'll need to register your function:

$config = $em->getConfiguration();
$config->addCustomNumericFunction('INT', CastAsInteger::class);

Then you can use it:

SELECT e, INT(e.id) AS HIDDEN orderId
FROM Namespace\Bla\MyEntity e
ORDER BY orderId

PS: By adding the HIDDEN keyword, the alias orderId won't be in the results (and is only used for ordering).

Answer 2

Based on Jasper N. Brouwer answer, this is a little bit enhanced solution:

<?php
namespace MyProject\Query;

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

class Cast extends FunctionNode
{
    /** @var \Doctrine\ORM\Query\AST\PathExpression */
    protected $first;
    /** @var string */
    protected $second;
    /**
     * @param SqlWalker $sqlWalker
     *
     * @return string
     */
    public function getSql(SqlWalker $sqlWalker)
    {
        return sprintf("CAST(%s AS %s)",
            $this->first->dispatch($sqlWalker),
            $this->second
            );
    }


    /**
     * @param Parser $parser
     *
     * @return void
     */
    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->first = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_AS);
        $parser->match(Lexer::T_IDENTIFIER);
        $this->second = $parser->getLexer()->token['value'];
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

Now it should be possible to write DQL like this:

SELECT e, CAST(e.id AS integer) AS HIDDEN orderId FROM Namespace\Bla\MyEntity e ORDER BY orderId