Issues

ZF-4001: Zend_Paginator_Adapter_DbSelect

Description

I have two issues in the Zend_Paginator_Adapter_DbSelect class :

1- The class checks if (!isset($result[self::ROW_COUNT_COLUMN])) then throw excpetion, why? some databases like MySQL when inner joining empty tables returns empty record set which makes the adapter fails. It should not throw exception just set the row count to zero is fair enough.

Example: SELECT COUNT(*) FROM ANY_EMPTY_TABLE GROUP BY TABLE_PRIMARY_KEY; // this will return empty record set

2- The class fetch the count by reseting Zend_Db_Select::COLUMNS, Zend_Db_Select::ORDER and Zend_Db_Select::LIMIT_OFFSET. Also should clear the Zend_Db_Select::GROUP, Because some databases or all! return a count of each group as a recordset. The count function mainly effected by the GROUP BY modifier.

Example: SELECT COUNT(*) from NONE_EMPTY_TABLE GROUP BY TABLE_PRIMARY_KEY; //a count for each pk group

For more information http://dev.mysql.com/doc/refman/…

Unfortunately, Removing the GROUP BY modifier in most cases will give invalid count of rows.

Benjamin Gonzales solution is PERFECT.

Between I HAVE NO COMMENT OPTION :( I only have (Edit, Convert, Vote, Worklog and Google) in the left nav, Its really wired positioning ;)

Thanks

PS: There is no way to comment on the issue just editing it? :)

Comments

Could you perhaps give an example of the first problem? What tables (with how much data) do you have an what query do you have?

As for the second issues.. you're saying a GROUP BY can return multiple rows, even if the query only selectes COUNT(*) AS zend_paginator_row count? Again, an example would be greatly appreciated.

Yes there is a comment option, without editing. It's just well hidden in the left nav-bar ; )

For the non-empty table Benjamin Gonzales suggested the following on the Wiki:


public function count(){    
        if ($this->_rowCount === null){    
            $rowCount   = clone $this->_select;    
            
            $group = implode(",",$rowCount->getPart(Zend_Db_Select::GROUP));    
            $expression =  empty($group) ? "COUNT(*)" : "COUNT(DISTINCT $group)" ;    
            $expression =  new Zend_Db_Expr($expresion .' AS '. self::ROW_COUNT_COLUMN);    
               
               
            $rowCount->reset(Zend_Db_Select::COLUMNS)    
                     ->reset(Zend_Db_Select::ORDER)    
                     ->reset(Zend_Db_Select::GROUP)    
                     ->columns($expression);    
                        
            $this->setRowCount($rowCount);    
        }    
        return $this->_rowCount;    
    } 

I'm going to look at implementing a similar solution.

Fixed in revision 10942