Bug #88166

TCA entry 'foreign_table_where' => ' ORDER BY LOWER(tx_my_table.name) ASC' not working anymore

Added by Kurt Gusbeth 4 months ago. Updated 6 days ago.

Status:
New
Priority:
Should have
Assignee:
-
Category:
-
Target version:
-
Start date:
2019-04-17
Due date:
% Done:

0%

TYPO3 Version:
9
PHP Version:
Tags:
tca
Complexity:
Is Regression:
Sprint Focus:

Description

In TYPO3 8 this worked:

'config' => [
    'type' => 'select',
    'renderType' => 'selectSingle',
    'foreign_table' => 'tx_my_table',
    'foreign_table_where' => ' ORDER BY LOWER(tx_my_table.name) ASC',
    'items' => [
        [' --- Bitte wählen --- ',0]
    ],
    'minitems' => 0,
    'maxitems' => 1,
],

In TYPO3 9.5.5 it does not work. Error:
Datenbank-Fehler

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BY LOWER(tx_my_table.name) ASC) AND ( 1=1) AND' at line 1.
Es ist ein SQL-Fehler aufgetreten. Dies kann auf eine Schema-Abweichung zwischen TCA und der Datenbank hindeuten. Versuchen Sie im Install Tool einen Datenbank-Vergleich durchzuführen.

It works only if I remove the
 LOWER()

And other mySQL functions causes this error.

History

#1 Updated by Christopher Schnell 6 days ago

I can confirm this error.
This seems not to work any more when using some more enhanced ORDER BY clauses like "ORDER BY name='',name" (This would order the Items without name at the end of the list, having the others ordered by their name ascending)
The normal "ORDER BY name, uid" works as expected

To order a foreign table with the foreign_table_where config is mentioned in the official documentation on https://docs.typo3.org/m/typo3/reference-tca/9.5/en-us/ColumnsConfig/Type/Select.html#foreign-table-where and should work with any valid ORDER BY statement

Also available in: Atom PDF