在Doctrine 2中如何分页本机查询？ [英] How to paginate a native query in Doctrine 2?

问题描述

Doctrine 2具有可用于分页正常DQL查询的Doctrine\ORM\Tools\Pagination\Paginator类。

Doctrine 2 has the Doctrine\ORM\Tools\Pagination\Paginator class which can be used to paginate normal DQL queries.

但是，如果我通过它是一个本机查询，我得到这个错误：

However if I pass it a native query, I get this error:

Catchable fatal error: Argument 1 passed to Doctrine\ORM\Tools\Pagination\Paginator::cloneQuery() must be an instance of Doctrine\ORM\Query, instance of Doctrine\ORM\NativeQuery given

我已经尝试从cloneQuery方法中的paginator类中删除类型提示，但这只是给出进一步的错误，因为paginator类的其他位期望Query中找到的方法是在NativeQuery中。

I've tried removing the type-hinting from the paginator class in the cloneQuery method, but this just gives further errors because other bits of the paginator class expect methods found in Query that aren't in NativeQuery.

有没有任何简单的方法来分页本机查询，而不需要构建一个新的分页器类或将数据库中的每一行从数组中取出到数组？ / p>

Is there any easy way of paginating the native queries without needing to build a new paginator class or fetching every row from the database into an array?

推荐答案

我做了我自己的paginator适配器类兼容w ith Zend_Paginator。

I made my own paginator adapter class compatible with Zend_Paginator.

可能不会是最灵活的，因为它依赖于在查询开始附近有一个FROM（见count（）方法）但是这是一个相对快速和容易的修复。

Probably won't be the most flexible since it relies on there being a " FROM " near the start of the query (see the count() method) but it's a relatively quick and easy fix.

/**
 * Paginate native doctrine 2 queries 
 */
class NativePaginator implements Zend_Paginator_Adapter_Interface
{
    /**
     * @var Doctrine\ORM\NativeQuery
     */
    protected $query;
    protected $count;

    /**
     * @param Doctrine\ORM\NativeQuery $query 
     */
    public function __construct($query)
    {
        $this->query = $query;
    }

    /**
     * Returns the total number of rows in the result set.
     *
     * @return integer
     */
    public function count()
    {
        if(!$this->count)
        {
            //change to a count query by changing the bit before the FROM
            $sql = explode(' FROM ', $this->query->getSql());
            $sql[0] = 'SELECT COUNT(*)';
            $sql = implode(' FROM ', $sql);

            $db = $this->query->getEntityManager()->getConnection();
            $this->count = (int) $db->fetchColumn($sql, $this->query->getParameters());
        }

        return $this->count;
    }

    /**
     * Returns an collection of items for a page.
     *
     * @param  integer $offset Page offset
     * @param  integer $itemCountPerPage Number of items per page
     * @return array
     */
    public function getItems($offset, $itemCountPerPage)
    {
        $cloneQuery = clone $this->query;
        $cloneQuery->setParameters($this->query->getParameters(), $this->query->getParameterTypes());

        foreach($this->query->getHints() as $name => $value)
        {
            $cloneQuery->setHint($name, $value);
        }

        //add on limit and offset
        $sql = $cloneQuery->getSQL();
        $sql .= " LIMIT $itemCountPerPage OFFSET $offset";
        $cloneQuery->setSQL($sql);

        return $cloneQuery->getResult();
    }
}

这篇关于在Doctrine 2中如何分页本机查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！