ZF-3239: 'From' table not available in $_parts in Zend_Db_Table_Select

Description

As described in #ZF-3235 it would be desirable to have access to the source table information in a Zend_Db_Table_Select instance. The current implementation sets the "from" information in the __toString() method, after which it's available in the $_parts array and thus accessible via getPart(). It would make sense to have the "from" information available in $_parts directly after the Zend_Db_Table_Select object has been instantiated. Attached is a modified Zend_Db_Table_Select which provides an idea to a possible solution. There's a new method _setParts(), which loads some information into the $_parts array. This is copied from __toString().

Comments

Please evaluate and fix/categorize as necessary.

I'd like the priority of this issue to be reconsidered... Because the way Zend_Db_Table_Select works the Zend_Paginator_Adapter_DbSelect can't utilize the existing method of modifying the provided select query. Currently it's done like so:


$expression = new Zend_Db_Expr('COUNT(*) AS ' . self::ROW_COUNT_COLUMN);

$rowCount   = clone $this->_select;
$rowCount->reset(Zend_Db_Select::COLUMNS)
                     ->columns($expression);

However, at this point the FROM part is not yet know and can't be altered. The expression is used as argument for the columns() method, but this gets overwritten again in the assemble method. The only solution at the moment is to modify the query string in case of a Zend_Db_Table_Select. This is absolutely not desirable. Zend_Db_Table_Select should basically function the same way as Zend_Db_Select.

Attached is a patch that sets the FROM clause directly on instantiating the Zend_Db_Table_Select object. This is done in the setTable part:


public function setTable(Zend_Db_Table_Abstract $table)
{
    $this->_adapter = $table->getAdapter();
    $this->_info    = $table->info();
    
    $primary = $this->_info[Zend_Db_Table_Abstract::NAME];
    $schema  = $this->_info[Zend_Db_Table_Abstract::SCHEMA];
    $this->from($primary, self::SQL_WILDCARD, $schema);
    
    return $this;
}

The code used is directly copied from the assemble method, so it could be argued that some code there has become redundant.

I have tested the modified select query with Zend_Paginator and it now works the same as a Zend_Db_Select query. As far as I could find and run a unit test for this class (installing and configuring XDebug kinda messed with my PHPUnit's output) there don't seem to be any problems (at least no failed tests).

This issue should be fixed in time for ZF 1.6 RC1, so people can get the best Paginator experience ;)

This bug also prevents using Zend_Db_Select::columns(), before the select has been converted to a string at least once:

??Uncaught exception 'Zend_Db_Select_Exception' with message 'No table has been specified for the FROM clause' in /home/jaka/sites/iadmin.kubje.org/wcs/trunk/src/library/Zend/Db/Select.php:224??

Even the examples from the manual do not work because of this bug:

http://framework.zend.com/manual/en/…


$table = new Bugs();

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

This would cause an SQL error ??Unknown column 'bug_status' in 'where clause?? since the Bugs table itself wouldn't even be included in the select query, unless echoed out first.

(sorry to double-post) To be more precise: __toString() would have to run before doing the join(), otherwise "accounts" is not joined to bugs, but the only table used for the select.

Jurrien's patch has a minor BC problem (if I understand everything correctly):

Currently, if you select only some columns, then only those would be returned, since * is added before converting to string ONLY if no columns are selected.

With Jurrien's patch, * would always be selected, unless explicitly removed using reset(Zend_Db_Select::COLUMNS).

In addition to Jurrien's change, I propose: - adding a protected variable $_wildcardSelectedByDefault. This would then get set to true in setTable() at the same time as ?$this->from($primary, self::SQL_WILDCARD, $schema)? - override columns() to remove ?self::SQL_WILDCARD? from columns and set _wildcardSelectedByDefault to false if setting columns for the primary table and $_wildcardSelectedByDefault is true.

This way * is selected unless columns() is called explicitly, and the Zend_Db_Table_Select object always reflects the current "state" (columns that will actually be selected).

I will be testing this solution during the next week. If anyone has any other suggestions/worries, please speak :)

Reassigned this issue to myself. I should be able to dig up an email conversation I had with Simon regarding this issue. Together with the comments in this post I should be able to fix this without BC breakage.

Resolved in r. 13530

Reverted in r. 13749 because of BC issues

The solution of ZF-2798 is also a solution for this issue.

Since 1.9 there has been a feature in place that allowed select() to be called on a table with that will load the from part into the object.

Please see here, and the api doc here

I think it might fix this problem.

-ralph

I got this error today on Version 2.10.6. Is this bug fixed or not?