Using DQL functions inside Doctrine 2 ORDER BY

I'm doing a project in Symfony 2.3 with Doctrine 2.4 using MySQL database. I have an Entity of FieldValue (simplified): ``` class FieldValue { /** * The ID * * @var integer */ protected $fieldValueId; /** * Id of associated Field entity * * @var integer */ protected $fieldId; /** * Id of associated user * * @var integer */ protected $userId; /** * The value for the Field that user provided * * @var string */ protected $userValue; /** * @var \MyProjectBundle\Entity\Field */ protected $field; /** * @var \MyProjectBundle\Entity\User */ protected $user; ``` The problem I have is the fact that `$userValue`, while it's `LONGTEXT` in my database, can represent either actual text value , date or number, depending in the type of the `Field`. The Field can be dynamically added. After adding a one to any of the users every other user can also fill it's own value for that Field. While querying the database I use orderBy to sort on a certain column, which also can be one of those Fields. In that case I need to sort on `$userValue`. This is problematic when I need to have number fields sorted as numbers, and not as strings ('123' is less than '9' in that case...). The solution for it (I thought) is to `CAST` the `$sort`, so I would get SQL like: ``` ORDER BY CAST(age AS SIGNED INTEGER) ASC ``` Since Doctrine does not have a built-in DQL function for that, I took the liberty of adding that to my project as `INT` DQL function (thanks to Jasper N. Brouwer): ``` class CastAsInteger extends FunctionNode { public $stringPrimary; public function getSql(SqlWalker $sqlWalker) { return 'CAST(' . $this->stringPrimary->dispatch($sqlWalker) . ' AS SIGNED 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); } } ``` So happy with myself finding an easy solution I did that: ``` $sort = "INT(".$sort.")"; $queryBuilder->orderBy($sort, $dir); ``` which produced expected DQL: ``` ORDER BY INT(age) ASC ``` But also produced an exception: An exception has been thrown during the rendering of a template ("[Syntax Error] line 0, col 12272: Error: Expected end of string, got '('") in MyProject... So I've tried to find out what is going on and got into this in `Doctrine\ORM\Query\Parser.php`: ``` /** * OrderByItem ::= ( * SimpleArithmeticExpression | SingleValuedPathExpression | * ScalarExpression | ResultVariable * ) ["ASC" | "DESC"] * * @return \Doctrine\ORM\Query\AST\OrderByItem */ public function OrderByItem() { ... } ``` Does that mean that there is no possibility to use DQL functions inside `ORDER BY`? And if this is the case - is there any other way to achieve this? UPDATE I actually already have INT used in my select query, inside `CASE WHEN`: ``` if ($field->getFieldType() == 'number') { $valueThen = "INT(".$valueThen.")"; } $newFieldAlias = array(" (CASE WHEN ... THEN ".$valueThen." ELSE ... END ) as ".$field->getFieldKey()); ``` Later on the `$newFieldAlias` is being added to the query. Doesn't change anything... UPDATE 2 Even when I add an extra select to the query, which will result in this DQL: ``` SELECT age, INT(age) as int_age ``` and then sort like that: ``` ORDER BY int_age ASC ``` I still don't het the correct result. I've checked `var_dump` from `$query->getResult()`, and this is what I got: ``` 'age' => string '57' (length=2) 'int_age' => string '57' (length=2) ``` Like CAST does not matter. I'm clueless...