ZF2 Pagination does not work with Union
I am using PHP
with Zend Framework 2.3.3
. I used Paginator
to select using Union
. The code looks like this:
$where = new Where();
$where->like('value', "%$infoToSearch%");
$select = new Select();
$select->columns(array(new Expression('DISTINCT(id)')));
$select->from('products');
$select->where($where);
$select2 = new Select();
$select2->columns(array(new Expression('DISTINCT(id)')));
$select2->from('products_archive');
$select2->where($where);
$select->combine($select2);
$paginatorAdapter = new DbSelect($select, $this->getAdapter());
$paginator = new Paginator($paginatorAdapter);
$paginator->setCurrentPageNumber(1);
$paginator->setItemCountPerPage(10);
foreach($paginator as $product){
var_dump($product);
}
then I am getting the wrong amount of products. I checked the mysql query log and saw this query:
( SELECT DISTINCT(id) AS Expression1 FROM `products` WHERE `value` LIKE '%3%' LIMIT 10 OFFSET 0 ) UNION ( SELECT DISTINCT(id) AS Expression1 FROM `products_archive` WHERE `value` LIKE '%3%' )
as you can see what LIMIT 10 OFFSET 0
is in the wrong place. It should be at the end of the request. Is this a bug or is there a way to fix this problem?
source to share
This is because the selection is passed to the paging adapter for the first part of the join, so the limit clause is applied to that part. To resolve the limit clause on the merge result requires a new SQL \ Select instance, this is very similar to this issue previously discussed by Ralph Schindler https://github.com/zendframework/zf2/issues/5162#issuecomment-36294281 .
To fix this, you need to pass a new select object like this:
$union = (new \Zend\Db\Sql\Select)->from(['sub' => $select]);
$paginatorAdapter = new DbSelect($union, $this->getAdapter());
source to share