0

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?

Maximilian Z.
  • 105
  • 9

2 Answers2

3

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.

Mathias Brodala
  • 5,905
  • 13
  • 30
0

Thank you Mathias for pushing me in the right direction.

In the TYPO3 documentation https://docs.typo3.org/m/typo3/reference-coreapi/master/en-us/ApiOverview/Database/ExpressionBuilder/Index.html

expr()->not()

is not listed as a method of TYPO3 querybuilder.

But

expr()->isNotNull()

is existing.

So this will work:

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

Thank you again

Maximilian Z.
  • 105
  • 9