Issues

ZF-2925: Problem using join in Zend_Db_Table

Description

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?)

Comments

Please categorize/fix as needed.

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

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

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);}}

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: http://zfforums.com/zend-framework-components-13/…

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

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?

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

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

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

-ralph

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.

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.

Just looked at the altered documentation. Good job.

Nice work on closing a long standing issue.

Thanks

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