Feature #85949

Advanced "ORDER BY" in TCA

Added by Stephan Schuler 12 months ago. Updated 12 months ago.

Status:
New
Priority:
Should have
Assignee:
-
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2018-08-23
Due date:
% Done:

0%

PHP Version:
Tags:
Complexity:
medium
Sprint Focus:

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.

History

#1 Updated by Stephan Schuler 12 months ago

  • Complexity set to medium

#2 Updated by Martin Kutschker 12 months ago

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.

Also available in: Atom PDF