How to use KNPPaginatorBundle to paginate results using Doctrine Repository?

Question

I'm working on a Symfony2 project and I decided to use KNPPaginatorBundle to build an easy pagination system. So I created a Product entity and I want to add the paginator to indexAction action (generated by CRUD command).

// Retrieving products.
$em = $this->getDoctrine()->getManager();

//$entities = $em->getRepository('LiveDataShopBundle:Product')->findAll();

$dql   = "SELECT a FROM LiveDataShopBundle:Product a";
$entities = $em->createQuery($dql);

// Creating pagnination
$paginator  = $this->get('knp_paginator');
$pagination = $paginator->paginate(
    $entities,
    $this->get('request')->query->get('page', 1),
    20
);

It works fine but I want to use the Product's repository instead of creating the query directly in the controller. How can I do that ? In fact, directly add the collection of results to the paginate object is just too slow because its load all products then paginate the ArrayCollection.

Thanks in advance.

K4

Reeducate

I also noticed this performance issue. The problem is that this bundle need the whole array collection to generate the pagination. If you provide a partial result, the pagination will have wrong number of pages since it's calculate from the whole collection – Archespore

Answer 1

I suggest using QueryBuilder in your ProductRepository and then passing that to the paginator:

ProductRepository extends EntityRepository
{
    // This will return a QueryBuilder instance
    public function findAll()
    {
        return $this->createQueryBuilder("p");
    }
}

In the controller:

$products = $productRepository->findAll();

// Creating pagnination
$paginator  = $this->get('knp_paginator');
$pagination = $paginator->paginate(
    $products,
    $this->get('request')->query->get('page', 1),
    20
);

Leda

Thanks ! I did it, and it works fine ! But, what are the fundamental differences between createQueryBuilder() and createQuery() ?! Can I use createNativeQuery() ? – Reeducate

Create query is when you want to write the DQL yourself, normally you would do that when the query is simple enough. Use the queryBuilder when you need to construct complex queries with conditionals. The fluid interface is very convenient for that task. That is why it works great with the paginator, because working with the QueryBuilder is very easy! – Desolation

But if you use find all it will firstly get all in one query. Is it true? – Thurstan

If you call findAll() it will fetch every record. This defeats one of the purposes of pagination; to reduce the number of records you are fetching. – Overpowering

Answer 2

I think in some cases we could use Closure and pass to it a QueryBuilder object.

In your ProductRepository you could do something like this:

ProductRepository extends EntityRepository
{
    public function findAllPublished(callable $func = null)
    {
        $qb = $this->createQueryBuilder('p');

        $qb->where('p.published = 1');

        if (is_callable($func)) {
            return $func($qb);
        }

        return $qb->getQuery()->getResult();
    }
}

and then in ProductController:

public function indexAction(Request $request)
{
    $em = $this->get('doctrine.orm.entity_manager');
    $paginator = $this->get('knp_paginator');

    $func = function (QueryBuilder $qb) use ($paginator, $request) {
        return $paginator->paginate($qb, $request->query->getInt('page', 1), 10);
    };
    $pagination = $em->getRepository('AppBundle:Report')->findAllPublished($func);

    // ...
}

I think it more flexible and you could use findAllPublished method to get both paginated or NOT paginated results if you need.

Also keep in mind that callable type hint work in PHP >=5.4! Please, check docs for more info.

Premier

Smart solution! – Sane

@Sane thanks! But probably is not the best ;) Actually, this solution gets to you the full control on query builder in a controller which is not good I think, i.e. you should use it smart, but probably do something more restricted. – Premier

Answer 3

In our project we want to avoid using Doctrine queries in controllers. We have also separate layers. Controllers must not access the database. So I included pagination in the Repository.

Here my code in controller:

public function indexAction(Request $request)
{
    $userRepository = $this->get('user_repository');
    $page = intval($request->query->get('page', 1));
    $pages = 0;
    $users = $userRepository->findAllPaginated($pages, $page - 1, 10);

    return $this->render('User:index.html.twig', array(
        'users' => $users,
        'page' => $page,
        'pages' => $pages,
    ));
}

And here is the important code in my repository:

use Doctrine\ORM\Tools\Pagination\Paginator;
class UserRepository extends EntityRepository
{
    /**
     * @return User[]
     */
    public function findAllPaginated(&$pages, $startPage = 0, $resultsPerPage = 5)
    {
        $dql = 'SELECT u FROM CoreBundle:User u';
        $query = $this->getEntityManager()->createQuery($dql)
            ->setFirstResult($startPage * $resultsPerPage)
            ->setMaxResults($resultsPerPage);

        $paginator = new Paginator($query);
        $count = $paginator->count();
        $pages = floor($count/$resultsPerPage);

        return $paginator; // on $paginator you can use "foreach", so we can say return value is an array of User
    }
}

Dzoba

Where exactly the $pages = floor($count/$resultsPerPage); is used? – Charleycharlie

Does anyone think having the $paginator instanced like above is a big issue, performance or otherwise - opposed to pulling it as a service, for which you would need to somehow inject the knp_paginator service into the repository class? – Samualsamuel