ZF-2925: Problem using join in Zend_Db_Table

Issue Type: Bug Created: 2008-03-19T11:53:57.000+0000 Last Updated: 2009-05-19T10:54:36.000+0000 Status: Resolved Fix version(s): - 1.8.2 (27/May/09)

Reporter: Jacob Oettinger (oetting) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db_Select

  • Zend_Db_Table

Related issues: - ZF-2546



The functionality described in example 10.96 in the reference guide does not work. The code: <?php $table = new Bugs();

$select = $table->select(); $select->where('bug_status = ?', 'NEW') ->join('accounts', 'accounts.account_id = bugs.reported_by') ->where('accounts.account_name = ?', 'Bob');

$rows = $table->fetchAll($select);

Results in a simple select * from the accounts table where account_name = "Bob", the join is ignored.

A possible workaround is to use an explicit ->from() call on the select object. Like this: $select->from('bugs');

The query is now correctly formatted as a join query but it selects all columns from both bugs and accounts. This seems like a reasonable default behavior for the select object, but in the above usage it is required that only columns from bugs is returned to create a writable row object. The ->setIntegrityCheck(false) method to allow creation of read only rows with columns from both tables, but this is not the very useful behavior described in the reference guide.

Possible solution: * Make the select object returned from a table object contain a from part by default. * Alter the documentation-code so that no columns are added from the joined table (how is this is done?)


Posted by Wil Sinclair (wil) on 2008-03-25T20:43:57.000+0000

Please categorize/fix as needed.

Posted by Wil Sinclair (wil) on 2009-01-06T10:47:59.000+0000

No action on this issue for too long. I'm reassigning to Ralph for re-evaluation and categorization.

Posted by Ralph Schindler (ralph) on 2009-01-10T11:36:57.000+0000

Is this still an issue, it appears it might have been solved by ZF-2546

Posted by Jacob Oettinger (oetting) on 2009-01-10T13:27:34.000+0000

Hi, As far as I can see the problem remains. The example in the documentation is now located in example 13.99 and 13.100.

When trying to run the code in example 13.99 on the example database the code fails with: 'Zend_Db_Table_Select_Exception' with message 'Select query cannot join with another table'

I think it is new that it is the select statement that detects that there is a problem. But the original issue, that the code in the reference guide can not be run, remains.

Example 13.100 also fails to run: 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'bug_status' in 'where clause'' This fails be cause the generated query does not select from the bugs table.

Code to reproduce: <?php set_include_path('library'); require 'Zend/Db/Table/Abstract.php';

$db = Zend_Db::factory('pdo_mysql',array('host'=>'localhost','username'=>'dbuser','password'=>'dbpass','dbname'=>'dbname')); Zend_Db_Table_Abstract::setDefaultAdapter($db);

class Bugs extends Zend_Db_Table_Abstract {

} $table = new Bugs(); $select = $table->select()->setIntegrityCheck(false); $select->where('bug_status = ?', 'NEW') ->join('accounts', 'accounts.account_name = bugs.reported_by') ->where('accounts.account_name = ?', 'Bob'); $rows = $table->fetchAll($select);

Posted by Piotr Czachur (zimnyx) on 2009-01-22T06:01:08.000+0000

I'm running ZF 1.7.3.

Code from "Example 13.99. Using a lookup table to refine the results of fetchAll()" yelds "exception 'Zend_Db_Table_Select_Exception' with message 'Select query cannot join with another table'".

Example is wrong, or code is wrong.

Some other folks have found this too:…

This is serious issue, it's very simple JOIN that doesn't work at all.

Posted by David Muir (dmuir) on 2009-03-05T22:14:26.000+0000

Ralph, ZF-2546 was never solved. It was marked as Won't Fix. Using the patch supplied should fix it, but will probably break things that are expecting the buggy behaviour. Could the documentation at least be updated to show a work-around?

Posted by Ralph Schindler (ralph) on 2009-05-18T15:46:03.000+0000

This issue has been resolved by the solution (new feature) put in place by issue ZF-2546.

To use this feature, call $table->select(true); to retrieve a select object that contains the from part

Posted by Mark (lightflowmark) on 2009-05-19T00:09:13.000+0000

Hi Ralph - ZF2546 is marked as "won't fix" and there's no mention of the resolution you outline. Can you clarify which version this new feature appeared / will appear in? Thanks, Mark

Posted by Ralph Schindler (ralph) on 2009-05-19T06:18:38.000+0000

Oh, I am sorry, I marked the wrong issue number, this is related to ZF-2798


Posted by Jacob Oettinger (oetting) on 2009-05-19T07:56:35.000+0000

If this new functionality is to resolve this issue the required changes to the documentation example 15.101 is to add a 'true' param to the call to select() and add a call to setIntegrityCheck(false)

Otherwise the original issue, that the code in, what is now, example 15.101 can not be run.

Posted by Ralph Schindler (ralph) on 2009-05-19T09:25:56.000+0000

I've updated both the manual to reflect the new features, as well as added constants to Zend_Db_Table_Abstract to better identify the features when they are used.

Posted by Jacob Oettinger (oetting) on 2009-05-19T10:54:25.000+0000

Just looked at the altered documentation. Good job.

Nice work on closing a long standing issue.


(I know it wasn't that important - I just reported it because I happened to come across it)

Have you found an issue?

See the Overview section for more details.


© 2006-2018 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.