CODEWITHSUNDEEP
phpdoctrine-ormpaginationdql
Gnuffo1
Gnuffo1

Reputation: 3546

How to paginate a native query in Doctrine 2?

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

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.

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?

Upvotes: 4

Views: 9249

Answers (3)

Adil ELbourichi
Adil ELbourichi

Reputation: 21

If you have a dbal query builder that you have constructed with

$yourDbalQueryBuilder = $connection->createQueryBuilder();

then you can use:

$yourDbalQueryBuilder->setFirstResult(0)
                     ->setMaxResults(100000000)
                     ->execute()
                     ->rowCount();

Upvotes: 1

CaduElias
CaduElias

Reputation: 11

public function countTotalRecords($query, string $primaryKey = '*'): int
    {
        if ($query instanceof QueryBuilder) {
            $paginator = new Paginator($query->getQuery());
            return count($paginator);
        } else if ($query instanceof NativeQuery) {
            $rsm = new ResultSetMappingBuilder($query->getEntityManager());
            $rsm->addScalarResult('count', 'count');

            $sqlCount = "select count(".$primaryKey.") as count from (" . $query->getSQL() . ") as item";

            $count = $query->getEntityManager()->createNativeQuery($sqlCount, $rsm);

            if ($query->getParameter('limit')) {
                $query->setParameter('limit', null);
            }

            $count->setParameters($query->getParameters());

            return (int)$count->getSingleScalarResult();
        }
        return 0;
    }

Upvotes: 1

Gnuffo1
Gnuffo1

Reputation: 3546

I made my own paginator adapter class compatible with Zend_Paginator.

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

Upvotes: 8

Related Questions