[SOLVED] TYPO3: Using IS NULL and COALESCE in OrderBy with TYPO3 Querybuilder

TYPO3: Using IS NULL and COALESCE in OrderBy with TYPO3 Querybuilder

I want to using the query

SELECT `uid` FROM `machines` ORDER BY NOT ISNULL(`changed`),`changed` DESC

in my controller . For this I use the querybuilder like this:

$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('forklifts');
$statement = $queryBuilder
    ->select('*')
    ->from('machines');

I tryed to add the ORDERBY in this way:

$statement->orderBy("NOT ISNULL(`changed`)");
$statement->addOrderBy("changed", "DESC");

But this don't work. Is it possible to do this in TYPO3 Querybuilder or is there another solution to this problem?

Solution

You can use QueryBuilder::add() to bypass this limitation:

$queryBuilder->add(
    'orderBy',
    $queryBuilder->expr()->isNotNull('changed'),
    true
);
$queryBuilder->addOrderBy('changed', 'DESC');

This is basically what QueryBuilder::addOrderBy() does internally but without automatic identifier quoting. Notice that the ExpressionBuilder is used here instead of a plain SQL snippet to ensure the changed field name is still quoted, even as part of a constraint.