castingdoctrine-ormtype-conversiondql

CASTING attributes for Ordering on a Doctrine2 DQL Query


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


Solution

  • 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).