Doctrine - How to hydrate a collection when using

Question

A previous question I asked was to do with hydrating a result set when using Doctrine and query builder. My issue was how to return an array and their sub-sets:

This was for a single result set and the answer was quite simple:

    $qb = $this->stoneRepository->createQueryBuilder('S');

        $query = $qb->addSelect('A','P','I','C')
            ->leftJoin('S.attribute', 'A')
            ->leftJoin('A.category', 'C')
            ->innerJoin('S.product' , 'P')
            ->innerJoin('S.image' , 'I')
            ->where('S.id = :sid')
            ->setParameter('sid', (int) $stone_id)
            ->getQuery();

        $resultArray = $query->getOneOrNullResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);

        return $resultArray;

My next question is how to do this exact same thing for a collection? This is what I have tried:

public function fetchAll()
    {
        $qb   = $this->stoneRepository->createQueryBuilder('S');

        $qb->addSelect('A','P','I','C')
            ->leftJoin('S.attribute', 'A')
            ->leftJoin('A.category', 'C')
            ->innerJoin('S.product' , 'P')
            ->innerJoin('S.image' , 'I')
            ->where('S.state=:state')
            ->setParameter('state' , 1 );

        $adapter    = new DoctrineAdapter( new ORMPaginator( $qb ) );
        $collection = new StoneCollection($adapter);

        return $collection;
    }

The problem I am facing with this solution is that the join tables are not being populated and I am ending up with a collection of empty results.

The StoneCollection class simply extends paginator:

<?php
namespace Api\V1\Rest\Stone;

use Zend\Paginator\Paginator;

class StoneCollection extends Paginator
{

}

I am thinking that perhaps the best mehod is to get an array and to page the array?

EDIT::

I have this working although I am not keen on it as I hit the DB twice. The first time to build the array (Which is the entire result set which could be very big for some applications) and then the second time to page the results which is then returned to HAL in ApiGility for processing...

Ideally this should be done in one go however I am not sure how to hydrate the results in a single instance...

public function fetchAll( $page = 1 )
{
    $qb   = $this->stoneRepository->createQueryBuilder('S');

    $qb->addSelect('A','P','I','C')
        ->leftJoin('S.attribute', 'A')
        ->leftJoin('A.category', 'C')
        ->innerJoin('S.product' , 'P')
        ->innerJoin('S.image' , 'I')
        ->where('S.state=:state')
        ->setParameter('state' , 1 );

    $resultArray = $qb->getQuery()->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);

    $paginator = new \Zend\Paginator\Paginator(new \Zend\Paginator\Adapter\ArrayAdapter($resultArray));
    $paginator->setCurrentPageNumber($page);

    return $paginator;
}

Answer 1

On the Doctrine documentation for Pagination they state to use $fetchJoinCollection = true, which I believe is the same as the HYDRATE you are trying to use. Doctrine Pagination

On my pagination code for my QueryBuilder I use it like the following:

public function getAllPaginated($page, $limit){
    $query = $this->createQueryBuilder('o')
                ->select('o')
                ->getQuery();

    $paginator = new Paginator($query, $fetchJoinCollection = true);
    $paginator->getQuery()
        ->setFirstResult($limit * ($page - 1)) // Offset
        ->setMaxResults($limit);

    return $paginator;
}

Answer 2

The Answer to this is as I have above:

I have this working although I am not keen on it as I hit the DB twice. The first time to build the array (Which is the entire result set which could be very big for some applications) and then the second time to page the results which is then returned to HAL in ApiGility for processing...

Ideally this should be done in one go however I am not sure how to hydrate the results in a single instance...

public function fetchAll( $page = 1 )
{
    $qb   = $this->stoneRepository->createQueryBuilder('S');

    $qb->addSelect('A','P','I','C')
        ->leftJoin('S.attribute', 'A')
        ->leftJoin('A.category', 'C')
        ->innerJoin('S.product' , 'P')
        ->innerJoin('S.image' , 'I')
        ->where('S.state=:state')
        ->setParameter('state' , 1 );

    $resultArray = $qb->getQuery()->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);

    $paginator = new \Zend\Paginator\Paginator(new \Zend\Paginator\Adapter\ArrayAdapter($resultArray));
    $paginator->setCurrentPageNumber($page);

    return $paginator;
}