Forums

8.4.2: Doctrine orderBy with NULL last

Permalink
I have a table with items I have to sort, where some items have statuses. The items have a column 'status' (Status foreign key), the statuses have a column 'sort'. What I need is to:

1. Sort items by status' 'sort' values (ASC)
2. Sort items by timestamp (DESC)
3. Sort all items so that all items without any status are last but also sorted by timestamp

Here's what I tried:
$query->leftJoin('c', 'Statuses', 'st', 'st.id = c.status')
    ->addSelect('CASE WHEN c.status IS NULL THEN 1 ELSE 0 END as HIDDEN status_is_null')
    ->orderBy('status_is_null', 'asc')
    ->addOrderBy('st.sort', 'asc')
    ->addOrderBy('c.timestamp', 'desc');

But it throws an error:

An exception occurred while executing 'SELECT c.id, CASE WHEN c.status IS NULL THEN 1 ELSE 0 END as HIDDEN status_is_null FROM Items c WHERE c.active = ? ORDER BY status_is_null asc, c.timestamp desc LIMIT 10 OFFSET 0' with params [true]: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'status_is_null FROM Items c WHERE c.active = '1' ORDER BY ' at line 1

What am I doing wrong?
linuxoid