Issues

ZF-8441: Wrong count of records when using DISTINCT in paginator's select object

Issue Type: Bug Created: 2009-12-02T04:18:41.000+0000 Last Updated: 2011-12-08T12:19:50.000+0000 Status: Resolved Fix version(s): - 1.11.12 (22/Jun/12)

Reporter: Andris Causs (cypher) Assignee: Marco Kaiser (bate) Tags: - Zend_Paginator

Related issues: Attachments: - ContractsController.php

Description

I have a Zend_Db_Select object that does several one-to-many joins on multiple tables. I am passing that select object to view's "paginationControl" helper. The number of rows I get is, for example, 5 but the paginator control's "totalItemCount" property says 11.

Here is some example code:

Zend_Db_Select generation in the controller action Note ->distinct()

<pre class="highlight">
$select = $c->getAdapter()->select()->distinct();
$select->from(array('c' => 'table0'), array(
    '*',
));
$select->joinLeft(array('t' => 'table1'),
    'c.id = t.id',
    array()
);
$select->joinLeft(array('f' => 'table2'),
    'c.id = f.id',
    array()
);

Then I assign the paginator object to the view:

<pre class="highlight">
$paginator = Zend_Paginator::factory($select);
$paginator->setCurrentPageNumber($page);
$paginator->setItemCountPerPage(10);
$this->view->items = $paginator;

After that I'm printing the paginator in the view like this:

<pre class="highlight">
$params = array(...);
print $this->paginationControl($this->items, Options::PAGINATOR_STYLE, '_pagination.phtml', $params);

And just in case, here's how the counts in the _pagination.phtml are printed:

<pre class="highlight">
print 'Showing ' . $this->firstItemNumber . ' - ' . $this->lastItemNumber . ' of ' . $this->totalItemCount . ' results (' . $this->itemCountPerPage . ' per page)';

The output is: "Showing 1 - 5 of 11 results (10 per page)"

To summarize it all - the amount of row objects in the paginator is correct but totalItemCount is not.

Comments

Posted by Marco Kaiser (bate) on 2009-12-02T04:25:48.000+0000

Can you add a ->distinct() to your select?

Posted by Andris Causs (cypher) on 2009-12-02T04:31:43.000+0000

Yes. If you "print $select" the result will be: SELECT DISTINCT c.* FROM table0 AS c LEFT JOIN table1 AS t ON c.id = t.id LEFT JOIN table2 AS f ON c.id = f.id;

Also, here's a link to the documentation: http://framework.zend.com/manual/en/…

Posted by Marco Kaiser (bate) on 2009-12-02T04:50:31.000+0000

I think this isnt a bug. you have to provide example table structure and data. I havnt reproducted it with my Data here.

Posted by Andris Causs (cypher) on 2009-12-02T05:06:29.000+0000

OK, let's say you've got two tables: table0: id | field_a | field_b | field_c

table1: id | field_d

the contents could be like this: table0: 1 | a | a | a 2 | b | b | b 3 | c | c | c

table1: 1 | x 1 | y 1 | z 2 | p 2 | q 3 | f 3 | g 3 | h

So, if you do SELECT t0.* FROM table0 AS t0 LEFT JOIN table1 AS t1 ON t0.id = t1.id; ...you will get 8 rows just because there are 8 rows in table1.

What I infact need is to get unique rows from table0, hence I need to add DISTINCT: SELECT DISTINCT t0.* FROM table0 AS t0 LEFT JOIN table1 AS t1 ON t0.id = t1.id;

In this case you get 3 rows.

Now, I pass this object to Zend_Paginator::factory() it creates the paginator object. When I do foreach ($paginator as $item) {} it will print 4 rows, which is correct but when I print the page numbers it says that there are 8 items. That is the bug that I'm trying to describe.

Sorry, I can't supply you any specific table structures as it's an internal company project.

Posted by Marco Kaiser (bate) on 2009-12-02T05:09:57.000+0000

Can you please attach to this issue your partial view paginator rendering script, a sql dump and other details so that it is more easier to reproduct that all? :)

Posted by Andris Causs (cypher) on 2009-12-02T05:13:59.000+0000

Sorry, there's a typo - instead of "it will print 4 rows" it should say "it will print 3 rows".

Posted by Andris Causs (cypher) on 2009-12-03T03:07:43.000+0000

Just a quick update.

Here is the output of my $select->__toString() (Zend_Db_Select that I pass to the paginator):

<pre class="highlight">
SELECT DISTINCT c.* FROM ctr_details AS c
LEFT JOIN ctr_tags AS t ON c.contract_id = t.contract_id
LEFT JOIN ctr_files AS f ON c.contract_id = f.contract_id
GROUP BY c.contract_id

// I added $select->group('c.contract_id') thinking that it might group all the rows when counting

And here is the output of $paginator->getAdapter()->getCountSelect()->__toString():

<pre class="highlight">
SELECT COUNT(1) AS `zend_paginator_row_count` FROM ctr_details AS c
LEFT JOIN ctr_tags AS t ON c.contract_id = t.contract_id
LEFT JOIN ctr_files AS f ON c.contract_id = f.contract_id

As you can see GROUP BY is gone as well.

The problem seems to be in Zend_Paginator_Adapter_DbSelect::getCountSelect().

Posted by Andris Causs (cypher) on 2009-12-03T03:32:12.000+0000

OK, found a dirty workaround for those that stumble upon this page and want a quick fix. As you probably know there's a function in Zend_Paginator_Adapter_DbSelect called setRowCount (). It allows you to specify a custom Zend_Db_Select object to use in counting the rows.

This is what I did:

<pre class="highlight">
$select = $c->getAdapter()->select()->distinct();
$select_count = $c->getAdapter()->select();

// generate the select object
$select->from(array('c' => $c->getTableName()), array(
    '*',
));
$select->joinLeft(array('t' => $t->getTableName()),
    'c.contract_id = t.contract_id',
    array()
);
$select->joinLeft(array('f' => $f->getTableName()),
    'c.contract_id = f.contract_id',
    array()
);

// generate the count select object
$select_count->from(array('c' => $c->getTableName()), array(
    new Zend_Db_Expr('COUNT(DISTINCT c.contract_id) AS ' . Zend_Paginator_Adapter_DbSelect::ROW_COUNT_COLUMN),
));
$select_count->joinLeft(array('t' => $t->getTableName()),
    'c.contract_id = t.contract_id',
    array()
);
$select_count->joinLeft(array('f' => $f->getTableName()),
    'c.contract_id = f.contract_id',
    array()
);

// ...

$adapter = new Zend_Paginator_Adapter_DbSelect($select);
$adapter->setRowCount($select_count);

$paginator = new Zend_Paginator($adapter);

$this->view->contracts = $paginator;

I know it's not the coolest approach but at least it works.

Posted by Ivan Krechetov (ikr) on 2010-01-07T01:13:24.000+0000

I seem to have a similar problem using Zend_Paginator_Adapter_DbSelect. totalItemCount ?> in my control.phtml is always 10, even though there are 50 pages with 10 items each.

Posted by Ivan Krechetov (ikr) on 2010-01-07T01:22:28.000+0000

Correction: it's not always 10 for me, on the last page it's 8. So, looks like totalItemCount has actually the value of currentItemCount.

Posted by Ivan Krechetov (ikr) on 2010-01-07T02:52:04.000+0000

Forget my comments. Found a bug in my code.

Posted by Marco Kaiser (bate) on 2010-05-31T01:04:36.000+0000

i tested it and i cannot reproduce your bug, i fixed some lines of code in Zend_Paginator, please test with the latest code and if still exists please reopen.

Posted by Jurrien Stutterheim (norm2782) on 2010-05-31T01:18:29.000+0000

Marco,

If the issue cannot be reproduced, please do not "fix some lines of code". If there is an issue with those lines, consider opening a new issue. Also, please mention the revision numbers :)

Cheers,

Jurriën

Posted by Chris MacPherson (chrismacp) on 2011-12-08T12:02:13.000+0000

I can confirm I am seeing the same results when I perform a query with joins that needs a distinct on one table.

My original query looks like this :

<pre class="literal">
SELECT DISTINCT `t1`.* 
FROM `table1` AS `t1` 
INNER JOIN `table2` AS `t2` ON t1.columna = t2.columna
LEFT JOIN `table3` AS `t3` ON t2.columnb = t3.columnb

Inside the dBSelect adapter, at line 260, if I perform __toString() on $this->_countSelect I get the following:

<pre class="literal">
SELECT COUNT(1) AS `zend_paginator_row_count` 
FROM `table1` AS `t1` 
INNER JOIN `table2` AS `t2` ON t1.columna = t2.columna
LEFT JOIN `table3` AS `t3` ON t2.columnb = t3.columnb

This is returning an incorrect count due to the lack of the distinct statement which is need due to multiple rows being created during the join process.

I think it would be hard for the method to dynamically get around this issue and so the solution for me is to alter the check at line 217 to pick up my situation and do the count around a sub-query as it does in other situations. Although probably not the most efficient way to count the rows, it does provide the correct result. With the code I am working on it would be difficult to provide a dynamically created custom query just for the count.

I basically just check there is only one column part and that is uses a wildcard. This then makes the adapter and paginator work perfectly for my query.

My change

<pre class="literal">
Index: Zend/Paginator/Adapter/DbSelect.php
===================================================================
--- Zend/Paginator/Adapter/DbSelect.php (revision 43843)
+++ Zend/Paginator/Adapter/DbSelect.php (working copy)
@@ -215,7 +215,8 @@
              * than one group, or if the query has a HAVING clause, then take
              * the original query and use it as a subquery os the COUNT query.
              */
-            if (($isDistinct && count($columnParts) > 1) || count($groupParts) > 1 || !empty($havingParts)) {
+            if (($isDistinct && ((count($columnParts) == 1 && $columnParts[0][1] == Zend_Db_Select::SQL_WILDCARD) 
+                 || count($columnParts) > 1)) || count($groupParts) > 1 || !empty($havingParts)) {
                 $rowCount->reset(Zend_Db_Select::ORDER);
                 $rowCount = $db->select()->from($rowCount);
             } else if ($isDistinct) {

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