TCA: not possible to select over multiple tables
If you make a TCA-entry for "select" or "group" and define a 'foreign_table' it is not possible to make a where-statement over multiple tables.
you have a table 'tx_test_main', which has a field 'users'. This field is configured as a MM-relation to 'tx_test_year_user', so that you can add users to such a record.
Now you have a table 'tx_test_sub', which has a field 'main_id'. This field is configured as an 1:m-relation to 'tx_test_year' -> this works perfect. But now you want also a field 'user', which is configured as an 1:m-relation to 'fe_users', but it should only show the users, which are associated to the main-record -> this does not work actually, because:
the config have to look like this:
'user' => array(
'type' => 'select',
'label' => 'User',
'config' => array(
'foreign_table' => 'fe_users',
'foreign_table_where' => ' AND tx_test_main.uid=###REC_FIELD_main### AND tx_test_main.uid = tx_test_year_user.uid_local AND tx_test_year_user.uid_foreign = fe_users.uid',
'size' => 1,
Attached are patches for TYPO3 3.8.1, 4.0.4 and 4.1RC1.
The only thing to change is in the function t3lib_befunc::exec_foreign_where_query()
(issue imported from #M5001)
#7 Updated by Joerg Schoppet over 13 years ago
After tagging TYPO3 4.1.0, here is the actual patch for this feature. As of now, you can add two additional fields to TCA-config "foreign_table_additional" and "neg_foreign_table_additional".
Description for doc_core_api:
in the table of section "['columns'][fieldname]['config'] / TYPE: "select""
foreign_table_additional: If the needed records of the foreign_table are only achievable with some inner-joins this property can be set with the list of additional tables separated by comma. Then they can be used in the foreign_table_where statement.
neg_foreign_table_additional: see "foreign_table_additional"