Issues

ZF-5165: joinUsing() does not join from the last joined table but instead takes the table used in from()

Description

While looking in to ZF-3309, I noticed that the code does not actually work in the normal flow joining.

Rather than working with the last joined table (as per the SQL USING() clause), it always uses the table used in the from() clause.

This is pretty bizarre as I though the syntax was designed to work in a similar way to the USING() SQL clause... perhaps this is an invalid assumption?

Anyway here is some example code:


<?php

/*
 Create the tables:

echo "
 create table foo(foo_id int(10) unsigned auto_increment primary key);
 create table bar(bar_id int(10) unsigned auto_increment primary key, foo_id int(10) unsigned);
 create table oink(oink_id int(10) unsigned auto_increment primary key, bar_id int(10) unsigned);
" | mysql -u root test

*/

ini_set('include_path', '/path/to/ZendFramewor/library/');

require_once('Zend/Loader.php');
Zend_Loader::registerAutoload();

$params = array(
  'host' => 'localhost',
  'username' => 'root',
  'password' => '',
  'dbname' => 'test',
);

$db = Zend_Db::factory('Pdo_Mysql', $params);

$select =
  $db
  ->select()
  ->from(array('a' => 'foo'), array('foo_id'))
  ->joinUsing(array('b' => 'bar'), 'foo_id', array('bar_id'))
  ->joinUsing(array('c' => 'oink'), 'bar_id', array('oink_id'));

echo (string)$select;
echo "\n";

try
{
  $db->fetchAll($select);
}
catch (Exception $e)
{
  echo 'Exception: '.$e->getMessage()."\n";
}

If you run this (and ZF-3309 is fixed - see my patch there which may fix it sufficiently) you will get the following output:

[colin@jimmy zend]$ php bug.php
SELECT `a`.`foo_id`, `b`.`bar_id`, `c`.`oink_id` FROM `foo` AS `a`
 INNER JOIN `bar` AS `b` ON `b`.foo_id = `a`.foo_id
 INNER JOIN `oink` AS `c` ON `c`.bar_id = `a`.bar_id
Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'a.bar_id' in 'on clause'

Now you can see that both joins refer back to the initial table. When joining on oink, I'd have expected that it would join bar to oink, not foo to oink.

Changing this would obviously break anything implemented with the current behaviour... The docs do not explain this (as the examples only show joining on one table) so if this is the intended behaviour I think it would be wise to put up a big warning in the documentation.

Comments

Actually the "fix" for this is actually very simple.

It seems that adding an element to an array does not advance the array pointer (certianly not in my version of PHP - the hot of the press 5.2.7)

In _joinUsing of Zend_Db_Select simply changing:


        $join  = $this->_adapter->quoteIdentifier(key($this->_parts[self::FROM]), true);

to


        end($this->_parts[self::FROM]);
        $join  = $this->_adapter->quoteIdentifier(key($this->_parts[self::FROM]), true);

seems to address things nicely in my case.

This does however leave the array pointer of $this->_parts[self::FROM] in a different possition and I've not looked to see if this is a problem.

The above quoted query does however work as as indended and there is no exception thrown.

If someone can comment on what the intended behaviour is, I can look at testing more thoroughly if appropriate.

Duplicate of ZF-3792, which I reported 2008-07-29.