Issue Feature Closed

#85949Advanced "ORDER BY" in TCA

Stephan Schuler 08/23/18, 17:17 updated 10/23/26, 14:42

Description

Hey there.

What do I address?

The current TCA ctrl “default_sortby” lacks a couple of features plan SQL provides, and even got cut down to a bare minimum when Doctrine was introduced.

So this is partially a bug report because upgrading from 6.2 to 8.7 broke an extension, and partially a feature request because I don’t expect it to be “fixed” to provide the same legacy API as back in 6.2.
Maybe treat it like a feature request which is based on having lost a beloved feature that passed away recently.

I’m talking of both, backend table view as well as CONTENT and RECORD cObj. Extbase queries are a complete different story.

My real world code:

I have a legacy TCA that looks something like this:

return [
    'ctrl' => [
        'default_sortby' => '
            ORDER BY
                (
                    CASE value
                        WHEN 0 THEN 999
                        ELSE value
                    END
                ) DESC
        '
    ]
];

This used to work back in 6.2 but stopped working once we switched to Doctrine.

I know where the issue is located:
https://api.typo3.org/typo3cms/8/html/_query_helper_8php_source.html#l00048

I could rewrite my TCA to this:

return [
    'ctrl' => [
        'default_sortby' => '
            value <>0,
            value DESC
        '
    ]
];

If you follow the code of the QueryHelper, that’s what happens:

  1. 'ORDER BY value <>0, value DESC'
  2. 'value <>0, value DESC'
  3. ['value <>0'], ['value DESC']
  4. ['value', '<>0'], ['value', 'DESC']

So clearly my current solution isn’t how it’s meant to be, and I expect it to break any time soon.

But of course there are several other statements you can ORDER BY in plain SQL that are currently not possible, such as IF, CONCAT and a lot more.

Proposal as a feature request

I get that the “default_sortby” in its current way is meant to stay and trying to parse that will be a never ending story.

But we could allow the “default_sortby” to (optionally, alternatively) be an array of [$columnName => $direction] and use that for sorting.

if (is_array($TCA[$table]['ctrl']['default_sortby']) {
    /** @var \TYPO3\CMS\Core\Database\Query\QueryBuilder $queryBuilder */
    $i=0;
    foreach ($TCA[$table]['ctrl']['default_sortby'] as $column => $direction) {
        $temporaryColumnName = 'hidden_ordering_column_' . $i++;
        $queryBuilder->addSelect($column . ' AS HIDDEN ' . $temporaryColumnName);
        $queryBuilder->orderBy($temporaryColumnName, $direction);
    }
}

Obviously this could be implemented as a QueryRestriction.

Regards,
Stephan.

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.

History 4

  1. Martin Kutschker commented <span data-processor="relativetime" data-value="2018-09-10T16:57:06+00:00" > 09/10/18, 16:57 </span>

    IMHO all plain SQL should be removed from the configuration. So if TYPO3 allows query partes in TCA in the future it would be nice if it supported a structure that directly translates into Doctrine without plain text parsing.

  2. Georg Ringer commented and changed status New Closed <span data-processor="relativetime" data-value="2026-10-23T14:42:36+00:00" > 10/23/26, 14:42 </span>

    thanks for the detailed report and sorry for the long silence.

    Since this was filed, the situation has changed:

    • h3. Parser
      QueryHelper::parseOrderBy() (see #88819) now splits only outside of parentheses and quotes, so expressions such as FIND_IN_SET(aField, "1,2,3") are kept intact.
    • h3. Events
      For the backend, the query can be changed freely via PSR-14 events that receive the QueryBuilder, so any ORDER BY expression is possible there:
      • TYPO3\CMS\Backend\View\Event\ModifyDatabaseQueryForRecordListingEvent (record list)
      • TYPO3\CMS\Backend\View\Event\ModifyDatabaseQueryForContentEvent (page module)
      • TYPO3\CMS\Backend\Search\Event\ModifyQueryForLiveSearchEvent (live search)

    Extending default_sortby with an array syntax would touch every consumer of the setting and add yet another TCA option. As Martin noted, the goal is to get less plain SQL into TCA, not more. Therefore I’m closing this issue as “works as designed”.