Bug #88166
closedTCA entry 'foreign_table_where' => ' ORDER BY LOWER(tx_my_table.name) ASC' not working anymore
100%
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.
Updated by Christopher Schnell about 5 years 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
Updated by Christian Eßl over 4 years ago
- Category set to Database API (Doctrine DBAL)
Updated by Susanne Moog over 4 years ago
- Related to Bug #82955: Doctrine: foreign_table_where cannot handle ORDER BY FIELD/ORDER BY FIND_IN_SET added
Updated by Gerrit Code Review over 4 years ago
- Status changed from New to Under Review
Patch set 2 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/63755
Updated by Gerrit Code Review over 4 years ago
Patch set 1 for branch 9.5 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/63858
Updated by Susanne Moog over 4 years ago
- Status changed from Under Review to Resolved
- % Done changed from 0 to 100
Applied in changeset 011a441712f833a880f5b35f46d736cd5dcc1ce5.