Using IS NULL and COALESCE in OrderBy Doctrine Querybuilder

Question

I basically have the following (My)SQL-Query

SELECT * FROM `address`
ORDER BY ISNULL(`company`), `company` ASC, COALESCE(`parent_id`, `address_id`), `parent_id` IS NOT NULL

Which does the job perfeclty

What I'm targeting is the following sorted Output

ID| PARENT_ID | COMPANY | NAME
1 | NULL      | A       | NULL
2 | 1         | A.A     | NULL
3 | 1         | A.B     | NULL
4 | NULL      | B       | NULL
5 | NULL      | C       | NULL
6 | NULL      | D       | NULL
7 | 6         | D.A     | NULL
8 | NULL      | NULL    | A

I'm using Symfony 2 and Doctrine. Currently I use the Doctrine query Builder because the OrderBy should be part of a search I implemented with different aspects (serch by tags, search by different fields "address" ...)

When I try to add "IS NULL" or "COALESCE" to

$qb->add('orderBy', ' >ORDERBY_STATEMENT< ');

Or in any other way to write the order by statement with the query builder

I get the following

[Syntax Error] line 0, col 90: Error: Expected end of string, got 'IS'

I figured out that Doctrine is not vendor specific and therefore can't support all vendor functions.

I read an article about extending Doctrines DQL functions. But there is already an "isNull" function using the Doctrine Expressions which doesnt work inside the OrderBy for me.

Anybody got an Idea how I can achieve the described OrderBy statement using the query builder ?

What if you use `$qb->add('orderBy', 'company IS NULL');` instead? — cheesemacfly, Aug 09 '13 at 14:41
it seems that one just can order by one column with one direction (makes sense anyway -_-) Of course i tested it with your code, but then again i am missing the COALESCE part of it — nixoschu, Aug 12 '13 at 15:52
http://stackoverflow.com/questions/5957330/doctrine-2-mysql-field-function-in-order-by — ZhukV, Nov 15 '13 at 12:30
thank for the updat, i managed to do it with my own extension just for the current problem — nixoschu, Nov 15 '13 at 12:57

score 22 · Answer 1 · answered Jun 11 '14 at 02:57

22

may be this is can help :

$qb = $em->createQueryBuilder();
$qb->select('Entity, COALESCE(Entity.column1, Entity.column2) as columnOrder')
   ->from('Namespace\EntityName', 'Entity')
   ->addOrderBy('columnOrder', 'ASC')
   ->getQuery()
   ->execute();

answered Jun 11 '14 at 02:57

Andi Sholihin

479
3
8

score 0 · Answer 2 · answered Aug 29 '17 at 12:58

Thank's Andi Sholihin !

I just post a working alternative under symfony 3 with the function IDENTITY() :)

$repository = $this->getDoctrine()->getRepository(Address::class);
$query = $repository->createQueryBuilder('a')
    ->select('a, COALESCE(IDENTITY(a.parent), a.id) as columnOrder')
    ->orderBy('columnOrder', 'ASC')
    ->addOrderBy('a.parent', 'ASC')
    ->getQuery();

Using IS NULL and COALESCE in OrderBy Doctrine Querybuilder

2 Answers2

Linked