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

Camenae

I think you're missing a ( in line 13. – Adventitia

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

Ragged

In my own function, I have a SingleValuedPathExpression like u.group and want to "wrap" this in a "CAST(" SingleValuedPathExpression " AS DECIMAL(10,2))" using actual Expression objects... how would I do that? What kind of Expression would CAST ... be when building it handcrafted? – Keeler

ok nevermind; I used a custom expression and modified its ->dispatch() method to fit my needs. Cleaner as expected. Doctrine's awesome. :) – Keeler

If target database is MySql, this line doesn't work: return 'CAST(' . $this->stringPrimary->dispatch($sqlWalker) . ' AS integer)'; Try this: return 'CAST(' . $this->stringPrimary->dispatch($sqlWalker) . ' AS SIGNED)'; – Nafis

Just for the record in Symfony, you put the register part on config.yml – Juror

Answer 2

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

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

Keith

Answer 3

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;

Ehtelehud

you are aware that i am using doctrine2 DQL and not MySQL? – Camenae

Answer 4

Think it's better to use some extra functional in such cases ( without trying "to circumvent" theirs). E.g. an excellent solution adding almost all necessary ( not supported from box ) stuff for Doctrine 2 is DoctrineExtensions by beberlei (github). With it it's possible to use directly CAST-statement like in OP's case:

("Symfony-example") E.g. in your config.xml add lines:

orm:
    ..
    entity_managers:
            ....
            dql:
                ....
                string_functions:
                    CAST: DoctrineExtensions\Query\Mysql\Cast

Then U can use it like:

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

Balkan

Answer 5

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)

Compute

Answer 6

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;

Ehtelehud

As mentioned i can't change it due to customer requirements – Camenae

Answer 7

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?

Monaco

you are aware that i am using doctrine2 DQL and not MySQL? – Camenae

I was not aware of that and I was using the sql for sqlserver, not mysql, but they are very similar. – Monaco