CODEWITHSUNDEEP
typo3
Ens
Ens

Reputation: 95

TYPO3 DBAL formatting bug?

Following example is given in this documentation: https://docs.typo3.org/c/typo3/cms-core/main/en-us/Changelog/8.7.x/Important-81751-DbalCompatibleQuotingInTca.html

'columns' => [
    'aField' => [
        'config' => [
            'foreign_table' => 'tt_content',
            'foreign_table_where' => 'AND {#tt_content}.{#CType} IN (\'text\',\'textpic\',\'textmedia\') ORDER BY {#tt_content}.{#CType} ASC',
            ...
        ],
    ],
    ...
],

'columns' => [
    'aField' => [
        'config' => [
            'type' => 'text',
            'search' => [
                'andWhere' => '{#CType}=\'text\' OR {#CType}=\'textpic\' OR {#CType}=\'textmedia\''
            ],
            ...
        ],
    ],
    ...
],

I created a record with this field:

'fe_group' => [
            'exclude' => true,
            'label' => 'LLL:EXT:core/Resources/Private/Language/locallang_general.xlf:LGL.fe_group',
            'config' => [
                'type' => 'select',
                'renderType' => 'selectMultipleSideBySide',
                'size' => 5,
                'maxitems' => 20,
                'items' => [
                    [
                        'LLL:EXT:core/Resources/Private/Language/locallang_general.xlf:LGL.hide_at_login',
                        -1,
                    ],
                    [
                        'LLL:EXT:core/Resources/Private/Language/locallang_general.xlf:LGL.any_login',
                        -2,
                    ],
                    [
                        'LLL:EXT:core/Resources/Private/Language/locallang_general.xlf:LGL.usergroups',
                        '--div--',
                    ],
                ],
                'exclusiveKeys' => '-1,-2',
                'foreign_table' => 'fe_groups',
                'foreign_table_where' => 'AND ORDER BY {#fe_groups}.{#title} ASC',
            ],
        ],

When i try to edit the record with this field i get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BY fe_groups.title ASC) AND ( 1=1) AND (pages.uid = fe_groups.pid...' at line 1. A SQL error occurred. This may indicate a schema mismatch between TCA and the database. Try running database compare in the Install Tool.

When I write foreign_table_where like this:

...
'foreign_table_where' => 'AND ORDER BY fe_groups.title ASC',
...

I get no error. Where is the problem?

Upvotes: 0

Views: 122

Answers (3)

Ens
Ens

Reputation: 95

I fixed my problem. In this documentation: https://docs.typo3.org/m/typo3/reference-tca/11.5/en-us/ColumnsConfig/Type/Select/Properties/ForeignTableWhere.html#columns-select-properties-foreign-table-where it says:

Note that ORDER BY and GROUP BY should NOT be quoted, since they always receive proper quoting automatically through the API.

It is not quoted, you can see it when you scroll down in the documentation:

'foreign_table_where' => 'AND ({#sys_category}.{#sys_language_uid} = 0 OR {#sys_category}.{#l10n_parent} = 0) ORDER BY sys_category.sorting'

So in this documentation it is written wrong: https://docs.typo3.org/c/typo3/cms-core/main/en-us/Changelog/8.7.x/Important-81751-DbalCompatibleQuotingInTca.html

Upvotes: 0

Jo Hasenau
Jo Hasenau

Reputation: 2694

You should skip foreign_table_where in this case and go for sortItems instead, since this is about sorting and not about a 'real' where clause.

https://docs.typo3.org/m/typo3/reference-tca/10.4/en-us/ColumnsConfig/Type/selectMultipleSideBySide.html#sortitems

Upvotes: 1

Stefan Bürk
Stefan Bürk

Reputation: 1358

You should check the fully build TCA. From your "partly" posted error message, invalid SQL get's build:

to use near 'BY fe_groups.title ASC) AND ( 1=1) AND

That looks like a ORDER BY is added and afterwards additional "AND" conditions. These should be part of the WHERE part, ORDER BY has to come afterwards.

Additionally, "AND ORDER BY" is invalid also ... if you have no where condition, remove the AND.

Otherwise, your TCA should work ... and is also covered with tests. Without the "full stack trace" or the "full sql" hard to tell more.

  • check your fully tca if there are and conditions added after the order
  • set proper xdebug breakpoints and debug it, or retrieve the "full sql".

Just from pastes code, it should be fine except the stated obious generall issues.

Upvotes: 2

Related Questions