Feature #16994

TCA: not possible to select over multiple tables

Added by Joerg Schoppet over 12 years ago. Updated over 6 years ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
-
Target version:
-
Start date:
2007-02-16
Due date:
% Done:

0%

PHP Version:
5.0
Tags:
Complexity:
Sprint Focus:

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)

4-1RC1.diff View (1.87 KB) Administrator Admin, 2007-02-16 10:35

4-0-4.diff View (1.87 KB) Administrator Admin, 2007-02-16 10:35

3-8-1.diff View (1.87 KB) Administrator Admin, 2007-02-16 10:35

bug_5001.diff View (1.9 KB) Administrator Admin, 2007-03-06 07:31

History

#1 Updated by Dmitry Dulepov over 12 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.

#2 Updated by Joerg Schoppet over 12 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.

#3 Updated by Dmitry Dulepov over 12 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.

#4 Updated by Joerg Schoppet over 12 years ago

OK,

so, can I simple made a implementation based on 4.1RC1, or should I wait until 4.1 final is out?

#5 Updated by Michael Stucki over 12 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.

#6 Updated by Joerg Schoppet over 12 years ago

Good,

then I will wait for 4.1 final and post my implementation in this bug.

#7 Updated by Joerg Schoppet over 12 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"

#8 Updated by Oliver Hader over 12 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!

#9 Updated by Alexander Opitz over 6 years ago

  • Status changed from New to Closed
  • Target version deleted (0)

No response in over one year => closed.

Also available in: Atom PDF