[#ZF-5319] Zend_Paginator_Adapter_DbSelect->setRowCount() does not behave as expected. - Zend Framework Issue Tracker

Zend Framework

Zend_Paginator_Adapter_DbSelect->setRowCount() does not behave as expected.

Created: 18/Dec/08 04:15 PM Updated: 08/Jun/09 02:31 PM Resolved: 08/Jun/09 02:30 PM

Component/s:

Zend_Paginator

Affects Version/s:

1.7.0, 1.7.1, 1.7.2

Fix Version/s:

None

Time Tracking:

Not Specified

Description

« Hide

// 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.

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

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

Show »

This list of reviews may be incomplete, as errors occurred retrieving data from the following repositories:

Repository Zend_Framework on http://framework.zend.com/code/ failed: Failed to authenticate with FishEye. See logs for more details.

No related reviews found.