ZF-7434 - Issues - Zend Framework

ZF-7434: Too many pages with empty content

Issue Type:

Bug

Created:

2009-07-31T10:39:29.000+0000

Last Updated:

2011-04-10T19:03:53.000+0000

Status:

Resolved

Fix version(s):

1.11.6 (05/May/11)

Reporter:

Master Surfer (mastersurfer84)

Assignee:

Ramon Henrique Ornelas (ramon)

Tags:

Zend_Paginator

Related issues:

Attachments:

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

Posted by Jurrien Stutterheim (norm2782) on 2009-07-31T10:48:30.000+0000

Could you paste the result of this snippet?


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

Posted by Master Surfer (mastersurfer84) on 2009-07-31T10:51:54.000+0000

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

Posted by Jurrien Stutterheim (norm2782) on 2009-07-31T11:17:43.000+0000

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.

Posted by Master Surfer (mastersurfer84) on 2009-07-31T12:37:32.000+0000

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.

Posted by Ramon Henrique Ornelas (ramon) on 2009-11-19T21:34:40.000+0000

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


 !($groupParts[0] instanceof Zend_Db_Expr)

Posted by Ramon Henrique Ornelas (ramon) on 2011-02-17T19:41:23.000+0000

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

Posted by Ramon Henrique Ornelas (ramon) on 2011-02-17T19:53:43.000+0000

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

Posted by Ramon Henrique Ornelas (ramon) on 2011-04-10T19:03:53.000+0000

Merged r23855 to release branch 1.11.