CASTING attributes for Ordering on a Doctrine2 DQL

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

Not sure if this works, but to access an entity ID you need the IDENTITY() DQL function. Try this:

SELECT entity1  FROM Namespace\Bla\MyEntity  ORDER BY IDENTITY(entity1)

Answer 2

I just did something similar in my own code yesterday. I was able to do:

select cast(entity1 as int) as OrderID
from yourtablename
where yourconditions

I had to actually cast mine as money and then int, but if you don't have a decimal, you should not have that issue. You can also try casting as numeric or using convert instead of cast, but cast is better in this situation.

Why do you need entity1 as a column if you already have the same value in OrderID?

Answer 3

I think you want order by entity1. if your entity1 data type is integer then no need to change it into integer or if it is not then you should do it. below is query for you.try this one.

select entity1,cast(entity1 as integer) as order_id from Table_Name order by 1 asc;

Answer 4

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 by possible to write DQL like this:

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

Answer 5

Try this one by with out changing the data type

  select (entity1 * 1) as display_value, entity1 as return_value 
      from Table_Name
     order by 1 asc;

Answer 6

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', 'MyProject\Query\CastAsInteger');

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