TYPO3 Core

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.