How to paginate a native query in Doctrine 2 with zend framwork 2

Question

Here is what I tried:

$entityManager = $this->getServiceLocator()->get('doctrine.entitymanager.orm_default');
$rsm = new \Doctrine\ORM\Query\ResultSetMapping;

$rsm->addEntityResult('SchoolAdmin\Entity\Maptechclass', 'Mt');
$rsm->addFieldResult('Mt', 'map_tech_class_id', 'mapTechClassID');
$rsm->addFieldResult('Mt', 'map_tech_id', 'mapTechID');

$rsm->addEntityResult('SchoolAdmin\Entity\Teacher', 'Th');
$rsm->addFieldResult('Th', 'th_id', 'th_id');
$rsm->addFieldResult('Th', 'th_first_name', 'th_first_name');
$rsm->addFieldResult('Th', 'th_last_name', 'th_last_name');

$rsm->addEntityResult('SchoolAdmin\Entity\Classes', 'Cs');
$Q = "SELECT  Mt.map_tech_class_id , Mt.map_tech_id ,
    Th.th_id,
    Th.th_first_name,
    Th.th_last_name,
    Cs.class_name
    FROM maptechclass Mt
    LEFT JOIN teacher Th
    ON Mt.map_tech_id=Th.th_teacher_id
    LEFT JOIN classes Cs
    ON Mt.map_class_id=Cs.class_id
    WHERE Mt.map_sch_id=49";
$query = $entityManager->createNativeQuery($Q, $rsm);


$auctions = $query->getResult();
return new ViewModel(array('paginator' => $auctions));

But I get the following error:

Catchable fatal error: Argument 1 passed to Doctrine\ORM\Tools\Pagination\Paginator::cloneQuery() must be an instance of Doctrine\ORM\Query, array given, called in D:\xampp\htdocs\deltaspiral\vendor\doctrine\orm\lib\Doctrine\ORM\Tools\Pagination\Paginator.php on line 122 and defined in D:\xampp\htdocs\deltaspiral\vendor\doctrine\orm\lib\Doctrine\ORM\Tools\Pagination\Paginator.php on line 205

You can't, how is Doctrine suppose to know how to work with potentially unknown native SQL in order to paginate it? In any case, why are you using native SQL for this query? You can accomplish the same query with normal DQL. — Tomdarkness

dougB dougB · Accepted Answer · 2014-09-29T23:48:29

Return $query instead of returning $qb->result(). $query is an instance of Doctrine\ORM\Query, whereas $qb-result() returns an array of results.

The paginator needs the query, not the result!

How to paginate a native query in Doctrine 2 with zend framwork 2

1 Answers