Zend Paginator displays wrong page count when query has "distinct"

Question

Hi all

I'm using Zf 1.10 on a project, and have been using Zend_Paginator together with Zend_DbTable queries quite successfully until this bit.

The query requires a DISTINCT keyword to remove duplicate rows created by a join, but when I add it, the paginator incorrectly displays navigation for two pages of results when there is only in fact one page of results. Some digging around reveals that it's executing two queries, one for the result set I'm after (77 rows) and another to get the count. But the second query generated by the Zend magic doesn't include the DISTINCT keyword and so the count returns 112 rows instead of 77 rows.

Here's the relevant bit

$select = $this->select()
    ->setIntegrityCheck(false)
    ->from('companies')
    ->distinct()
    ->join('project_team', 'companies.companyID = project_team.companyID', null)
    ->join('project_team_roles', 'project_team.roleID = project_team_roles.roleID', null)
    ->join('projects', 'projects.projectID = project_team.projectID', null)
    ->where('project_team_roles.isArchitect')
    ->where('companies.companyName LIKE ?', '%' . $str . '%')
    ->where('projects.islive AND NOT projects.isDeleted')
    ->order('companies.companyName'); 
 $adapter = new Zend_Paginator_Adapter_DbTableSelect($select);
 $paginator = new Zend_Paginator($adapter);
 $paginator->setCurrentPageNumber($page);
 $paginator->setItemCountPerPage(100);
 return $paginator;

There's nothing wrong with the queries it's generating, other than it's ignoring the DISTINCT clause in the count query. If you remove the ->distinct() bit it all works out great - 112 rows and pagination is all super, except the data has duplicate rows.

I see bug reports going back a fair ways about similar issues but they're marked as fixed in earlier versions of ZF

?Is this is a known gotcha? Is there anything I can do about it without writing my own pagination. It's not that writing pagination is especially challenging, but it means this bit will be inconsistent from the rest of the project

Many thanks

Ian

EDIT - Found a workaround, posted as answer below.

Answer 1

Found a simple workaround... Don't use the DbTable adapter, but get the results into an array and then pass that to the paginator

$results = $this->fetchAll($select)->toArray(); 
$adapter = new Zend_Paginator_Adapter_Array($results);

It's only the DbTable that has this bug, it would seem.

Best of luck!