Zend\Paginator\Adapter\DbSelect DISTINCT issue in ZF2

Question

I am facing an issues while using DISTINCT query in paginator DbSelect adapter in ZF2. My steps are.

First I created Select object.

$select = new Select();
$select->columns(
    array(
          'personId' => new Expression("DISTINCT $this->table.person_id"),
'first_name', 'middle_name', 'last_name'));
$select->from($this->table);

Then I passed this object to Zend\Paginator\Adapter\DbSelect

$result = new DbSelect($select, $this->adapter, $this->resultSetPrototype);

Now issue is that If I print sql query via echo str_replace('"', '', $select->getSqlString()); and run this query on mysql it shows 12 results.

But $result->count(); shows 55 result.

I have tried to figure out the problem and found that perhaps Zend\Paginator\Adapter\DbSelect count() function is buggy.

When I printed sql query in Zend\Paginator\Adapter\DbSelect count() function it removes the DISTINCT clause from query and added its own count column c.

SELECT COUNT(1) AS c FROM myTable;

Please suggest what should I do?

graphicmist graphicmist · Accepted Answer · 2013-06-08T01:17:06

Yes Manish you are right the count function is buggy. it leaves distinct and group by clauses. I also came across the same problem. Didnt found a solution for it though.

As one can see how https://github.com/zendframework/zf2/blob/master/library/Zend/Paginator/Adapter/DbSelect.php

the count function calculates the count might differ if you use distinct or group by clauses.

There are two solutions for it. 1) raise the issue and wait for them to fix the thing 2) write your own adapter

I will go with the second one. One of the most awesome things about ZF2 is that it provides you the flexibility of writing everything according to your needs. SO go ahead and create your own adapter

Zend\Paginator\Adapter\DbSelect DISTINCT issue in ZF2

1 Answers