Feature #16994
closed
TCA: not possible to select over multiple tables
0%
Description
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.
e.g.
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)
Files
Updated by Dmitry Dulepov almost 18 years ago
I do not like the idea of extracting tables from WHERE statement. It would be much simpler to add another property to $TCA (like 'additional_foreign_tables') and append those to table list.
Updated by Joerg Schoppet almost 18 years ago
Sure,
I just thought of the minimalistic intervention.
But if I get a statement, if this feature/bug/improvement can be included in the core, I would also write an implementation as you suggested.
Updated by Dmitry Dulepov almost 18 years ago
Any new feature has a chance to get into core if it does not break any existing functionality and does a good thing. This idea definitely does a good thing :)
No ne can guarantie anything though.
Updated by Joerg Schoppet almost 18 years ago
OK,
so, can I simple made a implementation based on 4.1RC1, or should I wait until 4.1 final is out?
Updated by Michael Stucki almost 18 years ago
It's a new feature, so it can be at least in 4.2. Means: Wait until 4.1 is out, then we fork 4.1 into a new branch, and start with 4.2 development.
Updated by Joerg Schoppet almost 18 years ago
Good,
then I will wait for 4.1 final and post my implementation in this bug.
Updated by Joerg Schoppet over 17 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"
Updated by Oliver Hader over 17 years ago
Jörg, do you hava a small test extension that you could attach here? This would make it easier to test the patch and the deployment of the new feature. Thanks!
Updated by Alexander Opitz over 11 years ago
- Status changed from New to Closed
- Target version deleted (
0)
No response in over one year => closed.