Using IS NULL and COALESCE in OrderBy Doctrine Que

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 ?

Answer 1

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();

Answer 2

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();