Abadis Abadis - 2 months ago 11
MySQL Question

ZF2 Pagination does not work with Union

I am using

PHP
with
Zend Framework 2.3.3
. I used
Paginator
for a Select with a
Union
.
The code is 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 get wrong number 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 the
LIMIT 10 OFFSET 0
is in wrong place. It should be at the end of query. Is it a Bug or is there any way to solve this problem?

Answer

This is occuring because the select is passed to the pagination adapter for the first part of the union, so the limit clause is applied to that part. In order to allow the limit clause to be applied to the result of the union, a fresh SQL\Select instance is required, this is very similar to this issue previously addressed by Ralph Schindler https://github.com/zendframework/zf2/issues/5162#issuecomment-36294281.

In order 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());
Comments