Doctrine 2 Query Builder abs Function

This Work:

    $qb = $this->em->createQueryBuilder();

    $qb->select("abs(u.code) as code")
       ->from("User", "u")
       ->orderBy("code","ASC")
       ->getQuery()
       ->getArrayResult();

This Don’t Work:

    $qb = $this->em->createQueryBuilder();

    $qb->select("u.code")
       ->from("User", "u")
       ->orderBy("abs(u.code)","ASC")
       ->getQuery()
       ->getArrayResult();

The Error:

Syntax Error] line 0, col 118: Error: Expected end of string, got ‘(‘

The native doctrine function abs work only on a select part of statment and don’t work on order by part.

Obs:

1-) Im avoiding to use NativeQuery.

2-) u.code is a varchar fild on mysql and need to be varchar ( some times numeric and some times string), and i need to order then like a number in numeric case.

Any Help?

Answer

Solved atma thank’s abs() realy doesn’t work directly in orderBy but as an alias.

soluction

SELECT field1,field2,abs(field3) AS abs_field3 
FROM table 
ORDER BY abs_field3 ASC

Obs: It’s a doctrine limitation, not a mysql limitation, mysql suports abs direct on order by part.