[#ZF-4001] Zend_Paginator_Adapter_DbSelect - Zend Framework Issue Tracker

Details

Type: Bug
Status: Resolved
Priority: Major
Resolution: Fixed
Affects Version/s: 1.6.0
Fix Version/s: 1.6.1
Component/s: Zend_Paginator
Labels:
None

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/5.0/en/counting-rows.html

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?

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Jurrien Stutterheim added a comment - 20/Aug/08 12:08 AM

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.

Show

Jurrien Stutterheim added a comment - 20/Aug/08 12:08 AM 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.

Hide

Permalink

Jurrien Stutterheim added a comment - 20/Aug/08 6:28 AM

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.

Show

Jurrien Stutterheim added a comment - 20/Aug/08 6:28 AM 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.

Hide

Permalink

Jurrien Stutterheim added a comment - 20/Aug/08 8:14 AM

Fixed in revision 10942

Show

Jurrien Stutterheim added a comment - 20/Aug/08 8:14 AM Fixed in revision 10942

Zend Framework

Zend_Paginator_Adapter_DbSelect

Details

Description

Activity

People

Dates