Project

General

Profile

Actions

Bug #88166

closed

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

Added by Kurt Gusbeth over 5 years ago. Updated over 4 years ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2019-04-17
Due date:
% Done:

100%

Estimated time:
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.


Related issues 1 (0 open1 closed)

Related to TYPO3 Core - Bug #82955: Doctrine: foreign_table_where cannot handle ORDER BY FIELD/ORDER BY FIND_IN_SETClosed2017-11-09

Actions
Actions #1

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

Actions #2

Updated by Christian Eßl over 4 years ago

  • Category set to Database API (Doctrine DBAL)
Actions #3

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
Actions #4

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

Actions #5

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

Actions #6

Updated by Susanne Moog over 4 years ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100
Actions #7

Updated by Benni Mack over 4 years ago

  • Status changed from Resolved to Closed
Actions

Also available in: Atom PDF