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

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/…

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

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.

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

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

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

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.

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.

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.

Forget my comments. Found a bug in my code.

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.

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

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