Doctrine 2 Order By ASC and Null values in last

Question

I am trying to fetch result which and I need to sort in ascending order. But some values would be null/empty, I need the to be in last while sorting starts from 0 1 2 and then null value.

I tried SortableNullsWalker but it did not help. The value I am sorting is not column, its a multiplication of two values which is being sorted, thats why I think SortableNullsWalker did not work. Any help please

$dql = 'SELECT (column1 * column2) as distance FROM table)

                ORDER BY distance ASC ';

        $dq = $this->getEntityManager()->createQuery($dql);

The result comes as '', '', 0, 1, 2.334, ....

But I am trying to get it like : 0, 1, 2.334,......, '', ''

Answer 1

This is similar solution, which works with non numerical colums/expressions:

/* @var $datasource QueryBuilder */  
$datasource->addSelect('CASE WHEN xxx.yyy IS NULL THEN 1 ELSE 0 END as HIDDEN yyy_is_null');
$datasource->orderBy('yyy_is_null', 'ASC'); // always ASC
$datasource->addOrderBy('xxx.yyy','DESC'); //DESC or ASC

Answer 2

SOLUTION:

I had to use a hidden variable with same value as of distance and add minus(-) before it and Order the result by new hidden variable in DESC order

$dql = 'SELECT (column1 * column2) as distance,
        -(column1 * column2) as HIDDEN distance1 
        FROM table
        ORDER BY distance1 DESC';

        $dq = $this->getEntityManager()->createQuery($dql);