Details
-
Type:
Bug
-
Status:
Closed
-
Priority:
Minor
-
Resolution: Not an Issue
-
Affects Version/s: 1.7.0, 1.7.1, 1.7.2
-
Fix Version/s: None
-
Component/s: Zend_Paginator
-
Labels:None
Description
// Method A
$rowCount = $db->select()->from( 'table' , array( Zend_Paginator_Adapter_DbSelect::ROW_COUNT_COLUMN => 'COUNT(*)' ) ); print( $rowCount."<br />" ); $paginator->getAdapter()->setRowCount( $rowCount ); // Exception thrown here.
// Method B
$rowCount = $db->select()->from( 'table' )
->reset( Zend_Db_Select::COLUMNS )
->columns( new Zend_Db_Expr( 'COUNT(*) AS '.$db->quoteIdentifier( Zend_Paginator_Adapter_DbSelect::ROW_COUNT_COLUMN ) ) );
print( $rowCount."<br />" );
$paginator->getAdapter()->setRowCount( $rowCount ); // Works fine.
I am using a query for pagination which involves subqueries, unions, grouping and ordering for which I wish to specify a fast row count with the setRowCount method. If I use Method A above, which seems to be the most logical way of specifying the query, I get a 'Row count column not found' Exception thrown from Line 109 of Zend/Paginator/Adapter/DbSelect.php
However, if I use the more convoluted query specification of Method B (which I derived from the source for count() in DbSelect) then no exception is thrown and the pagination works fine.
The results of both the print statements above are identical:
SELECT COUNT(*) AS `zend_paginator_row_count` FROM `table`
SELECT COUNT(*) AS `zend_paginator_row_count` FROM `table`
Therefore, I would expect that both should work without throwing an exception.
Note: I have not tested this in any version before 1.7.0 as I have only recently discovered Zend Framework and started at 1.7.
Edit (2009-01-01):
As there is no (apparent) way for me to add a comment, I've added this additional to the description.
The following 4 examples should all work with Zend_Paginator but only B & D do.
// Example A $rowCount = $db->select()->from('table', array(Zend_Paginator_Adapter_DbSelect::ROW_COUNT_COLUMN => 'COUNT(*)')); // Example B $rowCount = $db->select()->from('table', new Zend_Db_Expr('COUNT(*) AS '.$db->quoteIdentifier(Zend_Paginator_Adapter_DbSelect::ROW_COUNT_COLUMN))); // Example C $rowCount = $db->select()->from('metadata', array(Zend_Paginator_Adapter_DbSelect::ROW_COUNT_COLUMN => 'rowcount'))->where('tablename=?', 'table'); // Example D $rowCount = $db->select()->from('metadata', new Zend_Db_Expr($db->quoteIdentifier('rowcount').' AS '.$db->quoteIdentifier(Zend_Paginator_Adapter_DbSelect::ROW_COUNT_COLUMN)))->where('tablename=?', 'table');
print($rowCount) for the above gives:
// Example A SELECT COUNT(*) AS `zend_paginator_row_count` FROM `table` // Example B SELECT COUNT(*) AS `zend_paginator_row_count` FROM `table` // Example C SELECT `metadata`.`rowcount` AS `zend_paginator_row_count` FROM `metadata` WHERE (tablename='table') // Example D SELECT `rowcount` AS `zend_paginator_row_count` FROM `metadata` WHERE (tablename='table')
The reason that A & C fail is that Zend_Paginator_Adapter_DbSelect doesn't check for column aliases properly. The output below shows how the alias can be stored in two different ways:
1) As Array[0][2]
2) As part of Array[0][1] if it is a Zend_Db_Expr and contains an 'AS' clause
print_r($rowCount->getPart(Zend_Db_Select::COLUMNS)) gives:
// Example A Array ( [0] => Array ( [0] => films [1] => Zend_Db_Expr Object ( [_expression:protected] => COUNT(*) ) [2] => zend_paginator_row_count ) ) // Example B Array ( [0] => Array ( [0] => films [1] => Zend_Db_Expr Object ( [_expression:protected] => COUNT(*) AS `zend_paginator_row_count` ) [2] => ) ) // Example C Array ( [0] => Array ( [0] => metadata [1] => rowcount [2] => zend_paginator_row_count ) ) // Example D Array ( [0] => Array ( [0] => metadata [1] => Zend_Db_Expr Object ( [_expression:protected] => `rowcount` AS `zend_paginator_row_count` ) [2] => ) )
A possible solution is to modify the setRowCount() function of Zend_Paginator_Adapter_DbSelect to something like the following, which works successfully with the four examples above:
public function setRowCount($rowCount) { if ($rowCount instanceof Zend_Db_Select) { $columns = $rowCount->getPart(Zend_Db_Select::COLUMNS); // The select query can contain only one column if ( count($columns) != 1 ) { /** * @see Zend_Paginator_Exception */ require_once 'Zend/Paginator/Exception.php'; throw new Zend_Paginator_Exception('There should only be one column in the SQL provided.'); } $column0 = $columns[0]; $countColumnPart = $column0[1]; if ($countColumnPart instanceof Zend_Db_Expr) { $countColumnPart = $countColumnPart->__toString(); } // The select query can contain only one column, which should be // the row count column aliased as self::ROW_COUNT_COLUMN. This // can appear in $column0 as either $column0[2], or if $column0[1] // is a Zend_Db_Expr then the $countColumnPart as extracted above. if ((false === strpos($countColumnPart, self::ROW_COUNT_COLUMN)) && ($column0[2] != self::ROW_COUNT_COLUMN)) { /** * @see Zend_Paginator_Exception */ require_once 'Zend/Paginator/Exception.php'; throw new Zend_Paginator_Exception('Row count column not found'); } $result = $rowCount->query(Zend_Db::FETCH_ASSOC)->fetch(); $this->_rowCount = count($result) > 0 ? $result[self::ROW_COUNT_COLUMN] : 0; } else if (is_integer($rowCount)) { $this->_rowCount = $rowCount; } else { /** * @see Zend_Paginator_Exception */ require_once 'Zend/Paginator/Exception.php'; throw new Zend_Paginator_Exception('Invalid row count'); } return $this; }
Try something like this: