#108534Using 'ORDERBY' in 'foreign_table_where' has no effect
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.
- Commit 75a14ee
- Task #106502
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;