Bug #16283

murky MM self joins by pi_list_query // tslib

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

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2006-06-24
Due date:
% Done:

0%

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)

History

#1 Updated by elias about 12 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!
}

#2 Updated by Alexander Opitz over 5 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 about 5 years ago

  • Status changed from Needs Feedback to Closed

No feedback for over 90 days.

Also available in: Atom PDF