Simple Symfony2.3 pagination with both NativeQuery and QueryBuilder (Doctrine)

I’ve allocated quite a reasonable amount of time to find a good bundle to achieve pagination AND use NativeQuery in the same time. I didn’t. Maybe I should have searched more…

Anyway, here it is in a nutshell, until I find some time to turn this into a bundle.

Goal: paginate records that are obtained both with NativeQuery and QueryBuilder in Doctrine.

What do we have:

entity called Process

What should we achieve at the end of this post:

one listing page with records resulted from a NativeQuery – paginated

one listing page with records resulted from QueryBuilder – paginated

First, let’s take care of Controller.

File/class called ProcessController should have an indexNativeAction where we will display a list of Process entities using NativeQuery. We’ll go for a simple approach:

// Ibw/BlogBundle/Controller/ProcessController.php

use IbwBlogBundleLibPaginator;

...

public function indexNativeAction($filter)
{
    // get the Process repository - this is where you'll store your queries, etc
    $repository = $this->getDoctrine()->getRepository('IbwBlogBundle:Process');

    // our custom paginator
    $paginator = new Paginator();

    // this is the query for listing
    $queryProcesses = $repository->getProcessesNativeQuery();

    // paginating 
    $pagination = $paginator->paginate($queryProcesses,
        // page, default 1
        $this->get('request')->query->get('page', 1),
        // how many results per page, taken from parameters (so we won't hardcode)
        $this->container->getParameter('pagination_limit_page')); 

    // finally, preparing the view 
    return $this->render('IbwBlogBundle:Process:indexNative.html.twig', array( 
        'pagination' => $pagination, 
        'paginator' => $paginator, 
    )); 
}

// Ibw/BlogBundle/Controller/ProcessController.php

use IbwBlogBundleLibPaginator;

...

public function indexNativeAction($filter)

{

// get the Process repository - this is where you'll store your queries, etc

$repository = $this->getDoctrine()->getRepository('IbwBlogBundle:Process');

// our custom paginator

$paginator = new Paginator();

// this is the query for listing

$queryProcesses = $repository->getProcessesNativeQuery();

// paginating

$pagination = $paginator->paginate($queryProcesses,

// page, default 1

$this->get('request')->query->get('page', 1),

// how many results per page, taken from parameters (so we won't hardcode)

$this->container->getParameter('pagination_limit_page'));

// finally, preparing the view

return $this->render('IbwBlogBundle:Process:indexNative.html.twig', array(

'pagination' => $pagination,

'paginator' => $paginator,

));

}

Then, to fill all the gaps, we’ll continue with creating the Paginator custom class, needed for doing almost all the work. Go ahead and create a Paginator.php file somewhere (I prefer a “Lib” folder inside the bundle).

This class will contain the most important method : paginate() but also a bunch of getters for the class params like count of all records in query, current page #, how many pages are in total, etc.

<?php
// Ibw/BlogBundle/Lib/Paginator.php 

namespace IbwBlogBundleLib;

use DoctrineORMQueryResultSetMappingBuilder;
use DoctrineORMToolsPaginationPaginator as DoctrinePaginator;

class Paginator
{
    private $count;
    private $currentPage;
    private $totalPages;

    /**
    * paginate results
    *
    * @param $query - naming is a bit off as it can be a NativeQuery OR QueryBuilder, we'll survive eventually
    * @param int $page
    * @param $limit
    * @return array
    */
    public function paginate($query, $page = 1, $limit)
    {
        // setting current page 
        $this->currentPage = $page;
        // set the limit 
        $limit = (int)$limit;

        // this covers the NativeQuery case
        if (is_a($query, 'DoctrineORMNativeQuery'))
        {
            // do a count for all query, create a separate NativeQuery only for that
            $sqlInitial = $query->getSQL();

            $rsm = new ResultSetMappingBuilder($query->getEntityManager());
            $rsm->addScalarResult('count', 'count');

            $sqlCount = 'select count(*) as count from (' . $sqlInitial . ') as item';
            $qCount = $query->getEntityManager()->createNativeQuery($sqlCount, $rsm);
            $qCount->setParameters($query->getParameters());

            $resultCount = (int)$qCount->getSingleScalarResult();
            $this->count = $resultCount;

            // then, add the limit - paginate for current page
            $query->setSQL($query->getSQL() . ' limit ' . (($page - 1) * $limit) . ', ' . $limit);
        } 
        // this covers the QueryBuilder case, turning it into Query
        elseif(is_a($query, 'DoctrineORMQueryBuilder'))
        {
            // set limit and offset, getting the query out of queryBuilder
            $query = $query->setFirstResult(($page -1) * $limit)->setMaxResults($limit)->getQuery();

            // using already build Doctrine paginator to get a count
            // for all records. Saves load.
            $paginator = new DoctrinePaginator($query, $fetchJoinCollection = true);
            $this->count = count($paginator);
        }

        // set total pages
        $this->totalPages = ceil($this->count / $limit);

        return $query->getResult();
    }

    /**
    * get current page
    *
    * @return int
    */
    public function getCurrentPage()
    {
        return $this->currentPage;
    }

    /**
    * get total pages
    *
    * @return int
    */
    public function getTotalPages()
   {
       return $this->totalPages;
   }

   /**
   * get total result count
   *
   * @return int
   */
   public function getCount()
   {
   return $this->count;
   }
}

<?php

// Ibw/BlogBundle/Lib/Paginator.php

namespace IbwBlogBundleLib;

use DoctrineORMQueryResultSetMappingBuilder;

use DoctrineORMToolsPaginationPaginator as DoctrinePaginator;

class Paginator

{

private $count;

private $currentPage;

private $totalPages;

/**

* paginate results

* @param $query - naming is a bit off as it can be a NativeQuery OR QueryBuilder, we'll survive eventually

* @param int $page

* @param $limit

* @return array

public function paginate($query, $page = 1, $limit)

{

// setting current page

$this->currentPage = $page;

// set the limit

$limit = (int)$limit;

// this covers the NativeQuery case

if (is_a($query, 'DoctrineORMNativeQuery'))

{

// do a count for all query, create a separate NativeQuery only for that

$sqlInitial = $query->getSQL();

$rsm = new ResultSetMappingBuilder($query->getEntityManager());

$rsm->addScalarResult('count', 'count');

$sqlCount = 'select count(*) as count from (' . $sqlInitial . ') as item';

$qCount = $query->getEntityManager()->createNativeQuery($sqlCount, $rsm);

$qCount->setParameters($query->getParameters());

$resultCount = (int)$qCount->getSingleScalarResult();

$this->count = $resultCount;

// then, add the limit - paginate for current page

$query->setSQL($query->getSQL() . ' limit ' . (($page - 1) * $limit) . ', ' . $limit);

}

// this covers the QueryBuilder case, turning it into Query

elseif(is_a($query, 'DoctrineORMQueryBuilder'))

{

// set limit and offset, getting the query out of queryBuilder

$query = $query->setFirstResult(($page -1) * $limit)->setMaxResults($limit)->getQuery();

// using already build Doctrine paginator to get a count

// for all records. Saves load.

$paginator = new DoctrinePaginator($query, $fetchJoinCollection = true);

$this->count = count($paginator);

}

// set total pages

$this->totalPages = ceil($this->count / $limit);

return $query->getResult();

}

/**

* get current page

* @return int

public function getCurrentPage()

{

return $this->currentPage;

}

/**

* get total pages

* @return int

public function getTotalPages()

{

return $this->totalPages;

}

/**

* get total result count

* @return int

public function getCount()

{

return $this->count;

}

Now, let’s build the result in Repository. We’re going to create method getProcessesNativeQuery() that returns the NativeQuery which will collect whatever we’re interested in. In this case : all the Process entities (you can be more creative):

// Ibw/BlogBundle/Entity/ProcessRepository.php

/**
* query for all processes
*
* @return DoctrineORMNativeQuery
*/
public function getProcessesNativeQuery()
{
    $rsm = new ResultSetMappingBuilder($this->getEntityManager());
    $rsm->addRootEntityFromClassMetadata('IbwBlogBundleEntityProcess', 'p');

    $q = $this->getEntityManager()
        ->createNativeQuery('select * from Process p order by p.created_at desc', $rsm);

    return $q;
}

// Ibw/BlogBundle/Entity/ProcessRepository.php

/**

* query for all processes

* @return DoctrineORMNativeQuery

public function getProcessesNativeQuery()

{

$rsm = new ResultSetMappingBuilder($this->getEntityManager());

$rsm->addRootEntityFromClassMetadata('IbwBlogBundleEntityProcess', 'p');

$q = $this->getEntityManager()

->createNativeQuery('select * from Process p order by p.created_at desc', $rsm);

return $q;

}

I think we have all setup for the final step: view . This is where all takes shape.

Let’s create the actual listing page, indexNative.html.twig.

{# Ibw/BlogBundle/Resources/views/Process/indexNative.html.twig #}

{% block content %} {# or whatever your content block is called #}

{# throw some title #}
<h1>Processes</h1>

{# this is an attempt to make a header - replace with your own #}
<div class="row-fluid">
<div class="span8 text-left"><strong>Process</strong></div>
<div class="span2 text-center"><strong>Approved?</strong></div>
<div class="span2 text-right"><strong>Manage</strong></div>
</div>

{# the actual Process listing - this is where Pagination kicks in #}
{% for process in pagination %}
    {# we have a separate template for Process listing #}
    {{ include('IbwPtoolBundle:Process:_item_listing.html.twig', {'process': process}) }}
{% endfor %}

{# display navigation for pages - paginator_navigator #}
{% if pagination|length > 0 and paginator.getTotalPages() > 1 %}
    {{ include('IbwBlogBundle:Paginator:paginator_navigator.html.twig', {'paginator' : paginator}) }}
{% endif %}

{% endblock %}

{# Ibw/BlogBundle/Resources/views/Process/indexNative.html.twig #}

{% block content %} {# or whatever your content block is called #}

{# throw some title #}

<h1>Processes</h1>

{# this is an attempt to make a header - replace with your own #}

<div class="span8 text-left"><strong>Process</strong></div>

<div class="span2 text-center"><strong>Approved?</strong></div>

<div class="span2 text-right"><strong>Manage</strong></div>

</div>

{# the actual Process listing - this is where Pagination kicks in #}

{% for process in pagination %}

{# we have a separate template for Process listing #}

{{ include('IbwPtoolBundle:Process:_item_listing.html.twig', {'process': process}) }}

{% endfor %}

{# display navigation for pages - paginator_navigator #}

{% if pagination|length > 0 and paginator.getTotalPages() > 1 %}

{{ include('IbwBlogBundle:Paginator:paginator_navigator.html.twig', {'paginator' : paginator}) }}

{% endif %}

{% endblock %}

Let’s see how the template for one Process entity listing looks like:

{# Ibw/BlogBundle/Resources/views/Process/_item_listing.html.twig #}

<div class="row-fluid">
    <div class="span8">
        {{ process.name }}
    </div>
    <div class="span2 text-center">
        {{ process.isApproved }}    
    </div>
    <div class="span2">
        <a href="#" title="Edit">Edit process</a>
    </div>
</div>

{# Ibw/BlogBundle/Resources/views/Process/_item_listing.html.twig #}

</div>

</div>

<a href="#" title="Edit">Edit process</a>

</div>

And the pagination navigator:

{# Ibw/BlogBundle/Resources/views/Paginator/paginator_navigator.html.twig #}

{% set currentPath = path(app.request.get('_route'), app.request.get('_route_params')) %}
{% set currentPage = paginator.getCurrentPage() %}
{% set totalPages = paginator.getTotalPages() %}

<div class="pagination pagination-centered">
<ul>
{% if currentPage > 1 %}
    <li><a href="{{ currentPath }}?page=1">&laquo;</a></li>
    <li><a href="{{ currentPath }}?page={{ currentPage > 1 ? currentPage - 1 : 1 }}"><</a></li>
{% endif %}

{% if (currentPage - 3) > 0 %}
    <li><a href="#">...</a></li>
{% endif %}
{% if (currentPage - 2) > 0 %}
    <li><a href="{{ currentPath }}?page={{ currentPage - 2 }}">{{ currentPage - 2 }}</a></li>
{% endif %}
{% if (currentPage - 1) > 0 %}
    <li><a href="{{ currentPath }}?page={{ currentPage - 1 }}">{{ currentPage - 1 }}</a></li>
{% endif %}
<li class="active"><a href="#">{{ currentPage }}</a></li>
{% if (currentPage + 1) <= totalPages %}
    <li><a href="{{ currentPath }}?page={{ currentPage + 1 }}">{{ currentPage + 1 }}</a></li>
{% endif %}
{% if (currentPage + 2) <= totalPages %}
    <li><a href="{{ currentPath }}?page={{ currentPage + 2 }}">{{ currentPage + 2 }}</a></li>
{% endif %}
{% if (currentPage + 2) < totalPages %}
    <li><a href="#">...</a></li>
{% endif %}

{% if currentPage < totalPages %}
    <li><a href="{{ currentPath }}?page={{ currentPage < totalPages ? currentPage + 1 : currentPage }}">></a></li>
    <li><a href="{{ currentPath }}?page={{ totalPages }}">&raquo;</a></li>
{% endif %}

</ul>
</div>

{# Ibw/BlogBundle/Resources/views/Paginator/paginator_navigator.html.twig #}

{% set currentPath = path(app.request.get('_route'), app.request.get('_route_params')) %}

{% set currentPage = paginator.getCurrentPage() %}

{% set totalPages = paginator.getTotalPages() %}

<ul>

{% if currentPage > 1 %}

<li><a href="{{ currentPath }}?page={{ currentPage > 1 ? currentPage - 1 : 1 }}"><</a></li>

{% endif %}

{% if (currentPage - 3) > 0 %}

{% endif %}

{% if (currentPage - 2) > 0 %}

<li><a href="{{ currentPath }}?page={{ currentPage - 2 }}">{{ currentPage - 2 }}</a></li>

{% endif %}

{% if (currentPage - 1) > 0 %}

<li><a href="{{ currentPath }}?page={{ currentPage - 1 }}">{{ currentPage - 1 }}</a></li>

{% endif %}

<li class="active"><a href="#">{{ currentPage }}</a></li>

{% if (currentPage + 1) <= totalPages %}

<li><a href="{{ currentPath }}?page={{ currentPage + 1 }}">{{ currentPage + 1 }}</a></li>

{% endif %}

{% if (currentPage + 2) <= totalPages %}

<li><a href="{{ currentPath }}?page={{ currentPage + 2 }}">{{ currentPage + 2 }}</a></li>

{% endif %}

{% if (currentPage + 2) < totalPages %}

{% endif %}

{% if currentPage < totalPages %}

{% endif %}

</ul>

</div>

All you need to do is setup the number of items per page (the “limit”) as parameter and all this should work just fine. We’ll set it to 10 by adding it in paramaters.yml file

# Ibw/BlogBundle/Resources/config/parameters.yml

parameters:
    pagination_limit_page: 10

# Ibw/BlogBundle/Resources/config/parameters.yml

parameters:

pagination_limit_page: 10

At this point, you have everything working with NativeQuery.

What about QueryBuilder?

Basically it’s all the same, except one thing: repository should return a QueryBuilder (instead of NativeQuery).

For that, create a new method in repository called getProcessesQueryBuilder(), like this:

// Ibw/BlogBundle/Entity/ProcessRepository.php

/**
* query for all processes
*
* @return DoctrineORMQueryBuilder
*/
public function getProcessesQueryBuilder()
{
    $q = $this->createQueryBuilder('p')
                ->select('p')
                ->orderBy('p.createdAt', 'desc');

    return $q;
}

// Ibw/BlogBundle/Entity/ProcessRepository.php

/**

* query for all processes

* @return DoctrineORMQueryBuilder

public function getProcessesQueryBuilder()

{

$q = $this->createQueryBuilder('p')

->select('p')

->orderBy('p.createdAt', 'desc');

return $q;

}

After that, of course you need to adjust the controller to call this for getting the results. Change the controller as follows:

// /Ibw/BlogBundle/Controller/ProcessController.php

public function indexNativeAction($filter)
{
    ...

    // this is the query for listing
    $queryProcesses = $repository->getProcessesQueryBuilder();

    ...
}

// /Ibw/BlogBundle/Controller/ProcessController.php

public function indexNativeAction($filter)

{

...

// this is the query for listing

$queryProcesses = $repository->getProcessesQueryBuilder();

...

}

This should be enough for you application to handle pagination for both NativeQueries and DQL in Doctrine.

3 Comments

knut

April 13, 2014

I made one small modification in class Paginator for support query in DoctrineORMQuery format:
elseif (is_a($query, 'DoctrineORMQuery')) { // set limit and offset, getting the query out of queryBuilder $query->setFirstResult(($page -1) * $limit)->setMaxResults($limit);

// using already build Doctrine paginator to get a count // for all records. Saves load. $paginator = new DoctrinePaginator($query, $fetchJoinCollection = true); $this->count = count($paginator); }

Simple Symfony2.3 pagination with both NativeQuery and QueryBuilder (Doctrine)

What about QueryBuilder?

3 Comments

Add comment Cancel reply

Need help with your next project?

Services

Careers

Simple Symfony2.3 pagination with both NativeQuery and QueryBuilder (Doctrine)

What about QueryBuilder?

3 Comments

Add comment Cancel reply

Need help with your next project?

Services

Careers

Privacy Policy