Issues

ZF-7434: Too many pages with empty content

Description

I use a Paginator with DBSelect for querying the database. See the following code:


        $select = $this->db->select();
        $select->from(array("v" => "table"), 
                      array("DateDayFormat" => new Zend_Db_Expr("DATE_FORMAT(v.FirstVisitTime, '%d.%m.%Y')"),
                            "PageCalls" => new Zend_Db_Expr("SUM(v.PageCalls)"),
                            ))
               ->group("DATE(v.FirstVisitTime)")
               ->order("DATE(v.FirstVisitTime) DESC");
        $paginator = new Zend_Paginator(new Zend_Paginator_Adapter_DbSelect($select));
        $paginator->setItemCountPerPage(20);
        $paginator->setCurrentPageNumber($this->_getParam("page", 1));
        $this->view->paginator = $paginator;

The select should return 6.5 pages, but there are 42 pages. the 7th to the 42nd page are empty. this problem occurs also with other selects, so it seems to be a bug - or did you change something, that would explain this strange behavior?

Comments

Could you paste the result of this snippet?


echo $paginator->getAdapter()->getCountSelect();

The Result is: SELECT COUNT(1) AS zend_paginator_row_count FROM mvc_visitors AS v

Could you try rewriting your code like the following snippet and see if it gives the expected result? Could you also echo the count select again with the following snippet?


$select = $this->db->select();
$select->from(array("v" => "table"), 
              array("DateDayFormat" => new Zend_Db_Expr("DATE_FORMAT(v.FirstVisitTime, '%d.%m.%Y')"),
                    "PageCalls" => new Zend_Db_Expr("SUM(v.PageCalls)"),
                    ))
       ->group(new Zend_Db_Expr("DATE(v.FirstVisitTime)"))
       ->order(new Zend_Db_Expr("DATE(v.FirstVisitTime) DESC"));

My guess is that the lack of Zend_Db_Expr is causing the problems. I'm going to see if this is something Zend_Paginator can do something about.

so, i changes the group and order clauses as you wrote. unfortunately the result is the same. here the count infos:

the count-select-string: string(72) "SELECT COUNT(1) AS zend_paginator_row_count FROM mvc_visitors AS v"

and the result of this query array(1) { [0] => array(1) { ["zend_paginator_row_count"] => string(3) "839" } }

that is the same as the direct query in phpmyadmin. so the sql works correct (why shouldn't it ;-) )

this problem occoured first with 1.9 release. i think the aggregation in the sql is the problem, but in 1.8.x it worked all fine.

Problem object Zend_Db_Expr in class Zend_Paginator_Adapter_DbSelect::getCountSelect() line 231


 !($groupParts[0] instanceof Zend_Db_Expr)

This problem only occur with one column in the group and when the same column is envolved by a Zend_Db_Expr().

Reproduced with:


$select->from('test', 'group')
       ->group(new Zend_Db_Expr('group'));

Fix in Zend_Paginator_Adapter_DbSelect in the line 232 condition current code


} else if (!empty($groupParts) && $groupParts[0] !== Zend_Db_Select::SQL_WILDCARD &&
           !($groupParts[0] instanceof Zend_Db_Expr)) {
               $groupPart = $db->quoteIdentifier($groupParts[0], true);
}

Change to


} else if (!empty($groupParts)) {
    $groupPart = $db->quoteIdentifier($groupParts[0], true);
}

Also was deleted the condition $groupParts[0] !== Zend_Db_Select::SQL_WILDCARD no makes sense group by * until because this already is a error of SQL.

Grettings Ramon

I've added a fix for this issue in r23717 in trunk. Please test your applications against this.

If this works for everyone, I will merge to 1.11.

Thanks since already Ramon

Merged r23855 to release branch 1.11.