Issue Bug Closed

#108534Using 'ORDERBY' in 'foreign_table_where' has no effect

Georg Tiefenbrunn 12/19/25, 12:28 updated 03/31/26, 08:59

Description

TCA select fields currently ignore ORDERBY in foreign_table_where.

// EXT:sitepackage/Configuration/TCA/Overrides/sys_category.php

// Order category tree by sys_category.sorting column
$GLOBALS['TCA']['sys_category']['columns']['parent']['config']['foreign_table_where'] = ' AND {#sys_category}.{#sys_language_uid} IN (-1, 0) ORDER BY sys_category.sorting ASC';

Since #106502 AbstractItemProvider->buildForeignTableQueryBuilder() use an innerJoin to retrieve the full records using column wildcard.
Foreign table ORDERBY statements or default sorting from TCA are applied to $queryBuilder (used as sub-query in $wrapQueryBuilder), but not the returned $wrapQueryBuilder.

Imho orderBy should be removed from the sub-query ($queryBuilder; L665 to 678) and added to $wrapQueryBuilder.

// EXT:backend/Classes/Form/FormDataProvider/AbstractItemProvider.php#L748

        // Add orderings
        $orderByClauses = [];
        if (!empty($foreignTableClauseArray['ORDERBY'])) {
            $orderByClauses = $foreignTableClauseArray['ORDERBY'];
        } elseif ($schema->hasCapability(TcaSchemaCapability::DefaultSorting)) {
            $orderByClauses = QueryHelper::parseOrderBy($schema->getCapability(TcaSchemaCapability::DefaultSorting)->getValue());
        }
        foreach ($orderByClauses as $orderPair) {
            [$fieldName, $order] = $orderPair;
            // Remove table name
            if (str_contains($fieldName, '.')) {
                $fieldName = substr($fieldName, strpos($fieldName, '.') + 1);
            }
            // Append 'joined_table'
            $fieldName = 'joined_table.' . $fieldName;
            $wrapQueryBuilder->addOrderBy($fieldName, $order);
        }

        return $wrapQueryBuilder;

Reviews 1