ZF2分页不适用于Union(ZF2 Pagination does not work with Union)

我在Zend Framework 2.3.3使用PHP 。我使用Paginator进行选择Union 。代码是这样的：

$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); }

然后我得到错误的产品数量。我检查了mysql查询日志并看到了这个查询：

( 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%' )

正如您所看到的， LIMIT 10 OFFSET 0位于错误的位置。它应该在查询结束时。它是一个Bug还是有任何方法可以解决这个问题？

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?

最满意答案

这是因为select被传递给union的第一部分的分页适配器，所以limit子句应用于该部分。为了允许将limit子句应用于union的结果，需要一个新的SQL \ Select实例，这与之前由Ralph Schindler解决的这个问题非常相似https://github.com/zendframework/zf2/ issue / 5162＃issuecomment-36294281 。

为了解决这个问题，您需要传递一个新的选择对象，如下所示：

$union = (new \Zend\Db\Sql\Select)->from(['sub' => $select]); $paginatorAdapter = new DbSelect($union, $this->getAdapter());

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

更多推荐