Issues

ZF-4001: Zend_Paginator_Adapter_DbSelect

Issue Type: Bug Created: 2008-08-19T19:10:44.000+0000 Last Updated: 2008-10-10T19:18:48.000+0000 Status: Resolved Fix version(s): - 1.6.1 (15/Sep/08)

Reporter: Mina R Waheeb (mina) Assignee: Jurrien Stutterheim (norm2782) Tags: - Zend_Paginator

Related issues: Attachments:

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

Posted by Jurrien Stutterheim (norm2782) on 2008-08-20T00:08:35.000+0000

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.

Posted by Jurrien Stutterheim (norm2782) on 2008-08-20T06:28:03.000+0000

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:

<pre class="highlight">
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.

Posted by Jurrien Stutterheim (norm2782) on 2008-08-20T08:14:00.000+0000

Fixed in revision 10942

Have you found an issue?

See the Overview section for more details.

Copyright

© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.

Contacts