Project

General

Profile

Actions

Bug #97233

closed

TCA: foreign_table_where generates error with more than one placeholder of type ###REC_FIELD_*###

Added by Michael Stopp over 2 years ago. Updated over 2 years ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
FormEngine aka TCEforms
Target version:
-
Start date:
2022-03-24
Due date:
% Done:

0%

Estimated time:
TYPO3 Version:
10
PHP Version:
7.3
Tags:
Complexity:
Is Regression:
Sprint Focus:

Description

In a TCA field with renderType selecSingle we tried to restrict data from a foreign table by setting this foreign_table_where clause (table name simplified for readability):

'foreign_table' => 'mytable',
'foreign_table_where' => 'AND {#mytable}.{#otype} = \'###REC_FIELD_otype###\' AND {#mytable}.{#lang} = \'###REC_FIELD_lang###\'',

This generated the following error:

Database Error
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 'de'') AND ( 1=1) AND (`pages`.`uid` = `mytable`' 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.

Trying to narrow the problem down, I simplified the foreign_table_where in various ways.

Just using one of the placeholders worked for both of them:

'foreign_table_where' => 'AND {#mytable}.{#otype} = \'###REC_FIELD_otype###\'',
'foreign_table_where' => 'AND {#mytable}.{#lang} = \'###REC_FIELD_lang###\'',

Using both conditions, but setting a hardcoded value for one of them, worked as well:

'foreign_table_where' => 'AND {#mytable}.{#otype} = \'other\' AND {#mytable}.{#lang} = \'###REC_FIELD_lang###\'',
'foreign_table_where' => 'AND {#mytable}.{#otype} = \'###REC_FIELD_otype###\' AND {#mytable}.{#lang} = \'de\'',

So both conditions work in principle and both placeholder of type ###REC_FIELD_*### work on their own, but used in combination an error is thrown.

BTW: in the original file, there is additionally a ###PID### placeholder in use, which doesn't cause any problems (whether it's there or not) and works correctly. So it really seems to be a problem with the ###REC_FIELD_*### placeholders.

Actions

Also available in: Atom PDF