Bug #16283

murky MM self joins by pi_list_query // tslib

Added by jholzmann no-lastname-given over 12 years ago. Updated over 5 years ago.

Should have
Database API (Doctrine DBAL)
Target version:
Start date:
Due date:
% Done:


TYPO3 Version:
PHP Version:
Is Regression:
Sprint Focus:


mm-joining a table on itself via pi_exec_query //-> pi_list_query results an incorrect mysql query.
(But only if you join it on ITSELF!)

This is what pi_list_query does (the crucial part is right in the first line):

if (is_array($mm_cat))    {
$query='FROM '.$table.','.$mm_cat['table'].','.$mm_cat['mmtable'].chr(10).
' WHERE '.$table.'.uid='.$mm_cat['mmtable'].'.uid_local AND '.$mm_cat ...

This makes a query like "FROM fe_users,fe_users,fe_users_tx_extension_mm ...

To get it right you need a uniquely specified table which creates something like

FROM fe_users,fe_users_tx_extension_mm,fe_users AS fe_users_join449d75344c5d1 ...

(have a look in the db class, there mm joins are prefixed correctly).

I hotfixed pi_list_query for myself with

if (is_array($mm_cat))    {
if($table == $mm_cat['table']) 
$as = ' AS '.$mm_cat['unique_table'];
else {
$query= 'FROM '.$table.','.$mm_cat['mmtable'].','.$mm_cat['table_2'].$as.' '.chr(10).
'WHERE '.$table.'.uid='.$mm_cat['mmtable'].'.uid_local AND '.$mm_cat['table'].'.uid='.$mm_cat['mmtable'].'.uid_foreign '.chr(10) ...

But observe that the unique identifier MUST NOT be defined in pi_list_query itself because it would break standard extension behavior of pi_exec_query in freshly created kickstarter extensions. To make it work the unique identifier would have to be defined previously. $this->internal array could be a good place for that .
(issue imported from #M3737)


#1 Updated by elias over 12 years ago

I had the same problem when i use pages tables as category of himself I tried:

$mm_cat["aliasTable"]="$table as rel$table";
if (is_array($mm_cat)) {
$query='FROM '.$table.','.$mm_cat["aliasTable"].','.$mm_cat['mmtable'].chr(10).
' WHERE '.$table.'.uid='.$mm_cat['mmtable'].'.uid_local AND '.$mm_cat['table'].'.uid='.$mm_cat['mmtable'].'.uid_foreign '.chr(10).
(strcmp($mm_cat['catUidList'],'')?' AND '.$mm_cat['table'].'.uid IN ('.$mm_cat['catUidList'].')':'').chr(10).
' AND '.$table.'.pid IN ('.$pidList.')'.chr(10).
$this->cObj->enableFields($table).chr(10); // This adds WHERE-clauses that ensures deleted, hidden, starttime/endtime/access records are NOT selected, if they should not! Almost ALWAYS add this to your queries!

#2 Updated by Alexander Opitz almost 6 years ago

  • Status changed from New to Needs Feedback
  • Target version deleted (0)
  • PHP Version deleted (4)

The issue is very old, does this issue exists in newer versions of TYPO3 CMS (4.5 or 6.1)?

#3 Updated by Alexander Opitz over 5 years ago

  • Status changed from Needs Feedback to Closed

No feedback for over 90 days.

Also available in: Atom PDF