Using DQL functions inside Doctrine 2 ORDER BY

Question

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

Answer 1

Doctrine DQL does not accept functions as sort criteria but it does accept a "result variable". It means that you can do the following:

$q = $this->createQueryBuilder('e')
    ->addSelect('INT(age) as HIDDEN int_age')
    ->orderBy('int_age');

Answer 2

Doctrine 2 does not support INT by default, but you can use age+0.

$q = $this->createQueryBuilder('e')
    ->addSelect('age+0 as HIDDEN int_age')
    ->orderBy('int_age');