Zf2 Pagination fetches all records

Question

I had implemented the zf2 paginator. But now as the data increased I realized it is fetching all records first and then the pagination takes place, which takes more time. I did not realize the speed issue until the records went more than 1k.

Does the paginator really works like this, or I am missing something in my code ?

$photos = $em->getRepository('User\Entity\MyEntity')->findBy(array('id' => $main_id, 'status' => '1', 'is_primary' => '1'));

/** Create Paginator * */
$paginator = new \Zend\Paginator\Paginator(new
                \Zend\Paginator\Adapter\ArrayAdapter($photos)
             );

if (isset($data['pg_id'])) {
      $paginator->setCurrentPageNumber($data['pg_id']);
} else {
      $paginator->setCurrentPageNumber(1);
}
$paginator->setDefaultItemCountPerPage(25);

Answer 1

As @foozy alluded to in the comments, Doctrine provides paginator adapters for ZF2 for this purpose. You can use the Selectable adapter from DoctrineModule directly or the DoctrinePaginator class from DoctrineORMModule. The latter doesn't have it's own documentation but this blog post from Loft Digital explains it well:

use DoctrineORMModule\Paginator\Adapter\DoctrinePaginator as DoctrineAdapter;
use Doctrine\ORM\Tools\Pagination\Paginator as ORMPaginator;
use Zend\Paginator\Paginator;

// .......

$entityManager = $this->getServiceLocator()->get('doctrine.entitymanager.orm_default');
$repository = $entityManager->getRepository('Admin\Entity\SystemUser');
$adapter = new DoctrineAdapter(new ORMPaginator($repository->createQueryBuilder('user')));
$paginator = new Paginator($adapter);

// .......