Bug #16283: murky MM self joins by pi_list_query // tslib - TYPO3 Core - TYPO3 Forge

Actions

Copy link

Bug #16283

closed

murky MM self joins by pi_list_query // tslib

Added by jholzmann no-lastname-given over 18 years ago. Updated about 11 years ago.

Status:

Closed

Priority:

Should have

Assignee:

Category:

Database API (Doctrine DBAL)

Target version:

Start date:

2006-06-24

Due date:

% Done:

Estimated time:

TYPO3 Version:

4.0

PHP Version:

Tags:

Complexity:

Is Regression:

Sprint Focus:

Description

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']) 
{
                    $mm_cat['table_2']=$mm_cat['table'];
                    $mm_cat['unique_table']=$mm_cat['table'].$mm_cat['unique_identifier'];
                    $mm_cat['table']=$mm_cat['unique_table'];
                    $as = ' AS '.$mm_cat['unique_table'];
                }
                else
{
                    $as='';
                    $mm_cat['table_2']=$mm_cat['table'];
                }
$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)

Actions

Copy link

Updated by elias about 18 years ago

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

if($mm_cat['table']==$table){
$mm_cat['table']="rel$table";
$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!
}