[#DDC-775] Ability to use IFNULL() function in ORDER BY clause - Doctrine Project

Details

Type: Improvement
Status: Closed
Priority: Major
Resolution: Won't Fix
Affects Version/s: 2.2
Fix Version/s: None
Component/s: ORM
Security Level: All
Labels:
None

Description

    $qb = $em->createQueryBuilder(); /* @var $qb Doctrine\ORM\QueryBuilder */
    $roles = $qb->from('App_Model_Role', 'r')
                ->select('r')
                ->orderBy('IFNULL(r.rootId, r.id), r.lft')
                ->getQuery()
                ->getResult();

[Syntax Error] line 0, col 46: Error: Expected end of string, got '('

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Nikita Chirkov added a comment - 20/Feb/12 2:34 PM

I have the same error while using FIELD mysql function.

I added beberlei/DoctrineExtensions to my Symfony2 project to enable the FIELD function, but I'm able to use it in SELECT clause only. The problem is that in that case Doctrine returns bad result for me. Here is the example:

           $queryBuilder
                 ->addSelect('FIELD(u.id, 1, 2) as relevanceField')
                 ->orderBy('relevanceField')
            ;

The result of

foreach($queryBuilder->getQuery()->getResult() as $r) { ... }

is:

Array(Object, relevanceField)
Array(Object, relevanceField)
...

Without that addSelect() function call I have correct result:

Object
Object 
...

So I think orderByItem() function in Parser.php class should be extended so that MySQL function could be used there.

Show

Nikita Chirkov added a comment - 20/Feb/12 2:34 PM I have the same error while using FIELD mysql function. I added beberlei/DoctrineExtensions to my Symfony2 project to enable the FIELD function, but I'm able to use it in SELECT clause only. The problem is that in that case Doctrine returns bad result for me. Here is the example: $queryBuilder ->addSelect('FIELD(u.id, 1, 2) as relevanceField') ->orderBy('relevanceField') ; The result of foreach($queryBuilder->getQuery()->getResult() as $r) { ... } is: Array( Object , relevanceField) Array( Object , relevanceField) ... Without that addSelect() function call I have correct result: Object Object ... So I think orderByItem() function in Parser.php class should be extended so that MySQL function could be used there.

Hide

Permalink

Fabio B. Silva added a comment - 20/Feb/12 9:48 PM

Hi Nikita,

I have a pull request under discussion about this ticket :
https://github.com/doctrine/doctrine2/pull/288

please follow, and fell free to comment.

Show

Fabio B. Silva added a comment - 20/Feb/12 9:48 PM Hi Nikita, I have a pull request under discussion about this ticket : https://github.com/doctrine/doctrine2/pull/288 please follow, and fell free to comment.

Hide

Permalink

Fabio B. Silva added a comment - 25/Mar/12 1:19 PM

i'm +1 for this feature.
But is not cross-platform then we can't support..

More details : https://github.com/doctrine/doctrine2/pull/288

Show

Fabio B. Silva added a comment - 25/Mar/12 1:19 PM i'm +1 for this feature. But is not cross-platform then we can't support.. More details : https://github.com/doctrine/doctrine2/pull/288

Ability to use IFNULL() function in ORDER BY clause

Details

Description

Activity

People

Dates