Zend Framework

Wrong count of records when using DISTINCT in paginator's select object

Details

  • Type: Bug Bug
  • Status: Resolved Resolved
  • Priority: Major Major
  • Resolution: Fixed
  • Affects Version/s: 1.9.3, 1.9.4, 1.9.5, 1.9.6
  • Fix Version/s: Next Mini Release
  • Component/s: Zend_Paginator
  • Labels:
    None

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()

$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:

$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:

$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:

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.

Attachments

  1. File
    _pagination.phtml
    02/Dec/09 5:24 AM
    2 kB
    Andris Causs
  2. Text File
    ContractsController.php
    02/Dec/09 5:24 AM
    1 kB
    Andris Causs
  3. File
    tables.sql
    02/Dec/09 5:24 AM
    0.9 kB
    Andris Causs
  4. File
    view.phtml
    02/Dec/09 5:24 AM
    0.3 kB
    Andris Causs

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Marco Kaiser added a comment -

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

Show
Marco Kaiser added a comment - Can you add a ->distinct() to your select?
Hide
Permalink
Andris Causs added a comment -

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/zend.db.select.html#zend.db.select.building.distinct

Show
Andris Causs added a comment - 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/zend.db.select.html#zend.db.select.building.distinct
Hide
Permalink
Marco Kaiser added a comment -

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

Show
Marco Kaiser added a comment - I think this isnt a bug. you have to provide example table structure and data. I havnt reproducted it with my Data here.
Hide
Permalink
Andris Causs added a comment -

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.

Show
Andris Causs added a comment - 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.
Hide
Permalink
Marco Kaiser added a comment -

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?

Show
Marco Kaiser added a comment - 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?
Hide
Permalink
Andris Causs added a comment -

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

Show
Andris Causs added a comment - Sorry, there's a typo - instead of "it will print 4 rows" it should say "it will print 3 rows".
Hide
Permalink
Andris Causs added a comment -

Just a quick update.

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

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():

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().

Show
Andris Causs added a comment - Just a quick update. Here is the output of my $select->__toString() (Zend_Db_Select that I pass to the paginator): 
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(): 
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().
Hide
Permalink
Andris Causs added a comment -

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:

$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.

Show
Andris Causs added a comment - 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: 
$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.
Hide
Permalink
Ivan Krechetov added a comment -

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

Show
Ivan Krechetov added a comment - I seem to have a similar problem using Zend_Paginator_Adapter_DbSelect. <?php echo $this->totalItemCount ?> in my control.phtml is always 10, even though there are 50 pages with 10 items each.
Hide
Permalink
Ivan Krechetov added a comment -

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.

Show
Ivan Krechetov added a comment - 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.
Hide
Permalink
Ivan Krechetov added a comment -

Forget my comments. Found a bug in my code.

Show
Ivan Krechetov added a comment - Forget my comments. Found a bug in my code.
Hide
Permalink
Marco Kaiser added a comment -

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.

Show
Marco Kaiser added a comment - 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.
Hide
Permalink
Jurrien Stutterheim added a comment -

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

Show
Jurrien Stutterheim added a comment - 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
Hide
Permalink
Chris MacPherson added a comment -

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 : 

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:

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

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) {
Show
Chris MacPherson added a comment - 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 : 
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: 
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 
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) {

People

Vote (2)
Watch (3)

Dates

  • Created:
    Updated:
    Resolved:
Atlassian JIRA (v4.4.3#663-r165197) | Report a problem
Powered by a free Atlassian JIRA open source license for Zend Framework. Try JIRA - bug tracking software for your team.