Issue

Assuming I am attempting to fetch data from a table which contains course modules, with a custom field for course ordering which defaults null.

$query = $this->createQueryBuilder('m')
            ->addOrderBy('m.listOrder','ASC');

The result of this query will be sorted as defined. However our rows with null list_order entries will appear first before the other rows.

Solution

In order to make those entries follow the non-null entries from the bottom we add a select and create a hidden variable to handle the null values.

$query = $this->createQueryBuilder('m')
            ->addSelect('CASE WHEN m.listOrder IS NULL THEN 1 ELSE 0 END as HIDDEN list_order_is_null')
            ->addOrderBy('list_order_is_null','ASC') // always ASC
            ->addOrderBy('m.listOrder','ASC'); //DESC or ASC