Issue Bug Closed

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

Michael Stopp 03/24/22, 09:24 updated 03/29/22, 21:23

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.

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.