Issue Bug Closed

#16283murky MM self joins by pi_list_query // tslib

jholzmann no-lastname-given 06/24/06, 19:55 updated 09/04/13, 19:09

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)

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.