orm: DDC-490: Cannot use ORDER BY xxx ASC NULLS LAST / ORDER BY field = value DESC syntax

Jira issue originally created by user mzach:

It is at the time of this writing not possible to use something like:

$qb = $em->createQueryBuilder()
            ->select('p')
            ->from('Domain\Person', 'p')
            ->where('p.id = 1')
            ->orderBy('p.firstname', 'ASC NULLS LAST');

$qb = $em->createQueryBuilder()
            ->select('p')
            ->from('Webges\Domain\Core\Person\Person', 'p')
            ->where('p.id = 1')
            ->orderBy('p.firstname', '= ?');

which is perfectly valid syntax in PostgreSQL and also standard according to: http://troels.arvin.dk/db/rdbms/#select-order_by

Question is: will this be implemented in the near future / do any plans exist to support this at all?

Currently the only way is to use a native query with a custom ResultSetMapping

I do understand that not all database systems implement this, but an improvement in this direction would be a nice feature.

About this issue

Original URL
State: closed
Created 14 years ago
Comments: 16

Most upvoted comments

Comment created by @beberlei:

It could be added via QueryHints without changes to the parser and lexer, say:

    $query = $entityManager->createQuery("SELECT ... ORDER BY e.foo ASC, e.bar DESC")
    $query->setQueryHint(Query::HINT*CUSTOM_OUTPUT*WALKER, 'DoctrineExtensions\Query\SortableNullsWalker');
    $query->setQueryHint("sortableNulls.fields", array(
        "foo" => SortableNulls::NULLS_FIRST,
        "bar" => SortableNulls::NULLS_LAST,
    ));
    $results = $query->getResult();

See the latest commit with a cookbook recipe on how to write your own SQL Walker:

http://trac.doctrine-project.org/browser/docs/trunk/cookbook/en/dql-custom-walkers.txt?rev=7523

doctrinebot on Dec 6, 2015