Doctrine2.php

<?php
namespace ZfcDatagrid\DataSource;

use ZfcDatagrid\Filter;
use ZfcDatagrid\DataSource\Doctrine2\Paginator as PaginatorAdapter;
use ZfcDatagrid\Column;
use Doctrine\ORM;
use Doctrine\ORM\Query\Expr;

class Doctrine2 extends AbstractDataSource
{

    /**
     *
     * @var ORM\QueryBuilder
     */
    private $qb;

    /**
     * Data source
     *
     * @param mixed $data            
     */
    public function __construct($data)
    {
        if ($data instanceof ORM\QueryBuilder) {
            $this->qb = $data;
        } else {
            $return = $data;
            if (is_object($data)) {
                $return = get_class($return);
            }
            throw new \InvalidArgumentException("Unknown data input..." . $return);
        }
    }

    /**
     *
     * @return ORM\QueryBuilder
     */
    public function getData()
    {
        return $this->qb;
    }

    public function execute()
    {
        $qb = $this->getData();
        

        /**
         * Step 1) Apply sorting
         */
        if (count($this->getSortConditions()) > 0) {
            // Minimum one sort condition given -> so reset the default orderBy
            $qb->resetDQLPart('orderBy');
            
            foreach ($this->getSortConditions() as $sortCondition) {
                $column = $sortCondition['column'];
                $qb->add('orderBy', new Expr\OrderBy($column->getUniqueId(), $sortCondition['sortDirection']), true);
            }
        }
        
        /**
         * Step 2) Apply filters
         */
        $filterColumn = new Doctrine2\Filter($qb);
        foreach ($this->getFilters() as $filter) {
            if ($filter->isColumnFilter() === true) {
                $filterColumn->applyFilter($filter);
            }
        }
        
        /**
         * Step 3) Apply needed columns
         */
        $selectColumns = array();
        foreach ($this->getColumns() as $column) {
            if ($column instanceof Column\Standard && ! $column->hasDataPopulation()) {
                $colString = $column->getSelectPart1();
                if ($column->getSelectPart2() != '') {
                    $colString .= '.' . $column->getSelectPart2();
                }
                $colString .= ' ' . $column->getUniqueId();
        
                $selectColumns[] = $colString;
            }
        }
        
        $qbCount = clone $qb;
        
        $qb->resetDQLPart('select');
        $qb->select($selectColumns);
        
        
        
        /**
         * Step 4) Pagination
         */
        $this->setPaginatorAdapter(new PaginatorAdapter($qb, $qbCount));
    }
}

Paginator.php

<?php
/**
 * This is just a proxy to detect if we can use the "fast" Pagination
 * or if we use the "safe" variant by Doctrine2
 *
 */
namespace ZfcDatagrid\DataSource\Doctrine2;

use Zend\Paginator\Adapter\AdapterInterface;
use Doctrine\ORM\QueryBuilder;
use Doctrine\ORM\Query;
use Doctrine\ORM\Tools\Pagination\Paginator as Doctrine2Paginator;
use ZfcDatagrid\DataSource\Doctrine2\PaginatorFast;
use ZfcDatagrid\DataSource\Doctrine2\PaginatorSlow;

class Paginator implements AdapterInterface
{

    /**
     *
     * @var QueryBuilder
     */
    protected $qb = null;

    /**
     *
     * @var QueryBuilder
     */
    protected $qbCount = null;
    
    /**
     * Total item count
     *
     * @var integer
     */
    protected $rowCount = null;

    /**
     *
     * @var \Doctrine\ORM\Tools\Pagination\Paginator
     */
    private $paginator;

    /**
     *
     * @param QueryBuilder $qb            
     */
    public function __construct(QueryBuilder $qb,QueryBuilder $qbCount = null)
    {
        $this->qb = $qbSelect;
        if($qbCount !== null){
            $this->qbCount = $qbCount;
        }
    }

    /**
     *
     * @return \Doctrine\ORM\QueryBuilder
     */
    public function getQueryBuilder()
    {
        return $this->qb;
    }
    
    /**
     *
     * @return \Doctrine\ORM\QueryBuilder
     */
    public function getQueryBuilderCount()
    {
        return $this->qbCount;
    }

    /**
     * Test which pagination solution to use
     *
     * @return boolean
     */
    private function useCustomPaginator()
    {
        $qb = $this->getQueryBuilder();
        $platform = $qb->getEntityManager()
            ->getConnection()
            ->getDatabasePlatform();
        
        if ($platform->getName() != 'mysql') {
            // Only tested mysql currently so all other i don't know, if my implementation is good...
            return false;
        }
        
        $parts = $qb->getDQLParts();
        
        if ($parts['having'] !== null || $parts['distinct'] === true) {
            // never tried having in such queries...
            return false;
        }
        
        if (count($parts['groupBy']) > 1) {
            return false;
        }
        
        // @todo maybe more detection needed :-/
        
        return true;
    }

    /**
     *
     * @return \Doctrine\ORM\Tools\Pagination\Paginator
     */
    private function getPaginator()
    {
        if ($this->paginator !== null) {
            return $this->paginator;
        }
        
        if ($this->useCustomPaginator() === true) {
            $this->paginator = new PaginatorFast($this->getQueryBuilder());
        } else {
            // Doctrine2Paginator as fallback...they are using 3 queries
            $this->paginator = new PaginatorSlow($this->getQueryBuilder(), $this->getQueryBuilderCount());
        }
        
        return $this->paginator;
    }

    /**
     * Returns an array of items for a page.
     *
     * @param integer $offset            
     * @param integer $itemCountPerPage            
     * @return array
     */
    public function getItems($offset, $itemCountPerPage)
    {
        $paginator = $this->getPaginator();
        if ($paginator instanceof Doctrine2Paginator) {
            $this->getQueryBuilder()
                ->setFirstResult($offset)
                ->setMaxResults($itemCountPerPage);
            
            return $paginator->getIterator()->getArrayCopy();
        } else {
            return $paginator->getItems($offset, $itemCountPerPage);
        }
    }

    /**
     * Returns the total number of rows in the result set.
     *
     * @return integer
     */
    public function count()
    {
        return $this->getPaginator()->count();
    }
}

PaginatorSlow.php

<?php
namespace ZfcDatagrid\DataSource\Doctrine2;

use Zend\Paginator\Adapter\AdapterInterface;
use Doctrine\ORM\QueryBuilder;
use Doctrine\ORM\Tools\Pagination\Paginator as Doctrine2Paginator;

class PaginatorSlow implements AdapterInterface
{

    /**
     *
     * @var QueryBuilder
     */
    protected $qb;

    /**
     *
     * @var QueryBuilder
     */
    protected $qbCount;

    /**
     *
     * @var Doctrine2Paginator
     */
    protected $paginator;

    /**
     *
     * @var Doctrine2Paginator
     */
    protected $paginatorCount;

    /**
     * Total item count
     *
     * @var integer
     */
    protected $rowCount;

    public function __construct(QueryBuilder $qb, QueryBuilder $qbCount)
    {
        $this->qb = $qb;
        $this->qbCount = $qbCount;
        
        $this->paginator = new Doctrine2Paginator($qb);
        $this->paginatorCount = new Doctrine2Paginator($qbCount);
    }

    public function getQueryBuilder()
    {
        return $this->qb;
    }

    public function getQueryBuilderCount()
    {
        return $this->qbCount;
    }

    public function getItems($offset, $itemCountPerPage)
    {
        $this->paginator->getItems($offset, $itemCountPerPage);
    }

    public function count()
    {
        if ($this->rowCount !== null) {
            return $this->rowCount;
        }
        
        $this->rowCount = $this->paginatorCount->count();
        
        return $this->rowCount;
    }
}