Issues

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:

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

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

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

<pre class="highlight">
 !($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:

<pre class="highlight">
$select->from('test', 'group')
       ->group(new Zend_Db_Expr('group'));

Fix in Zend_Paginator_Adapter_DbSelect in the line 232 condition current code

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

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

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