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,
));
}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;
}
}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;
}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 %}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>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">«</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 }}">»</a></li>
{% 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: 10At 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;
}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();
...
}
This should be enough for you application to handle pagination for both NativeQueries and DQL in Doctrine.
Hi ,
First of all thks for the tutorial, so helpful and easy.
But it didn’t worked when I want to use pagination inside a block which render a controller in twig.
Since the route is not shown is the address bar …
please help!
I run into the following error when doing a dump on $pagination variable just to check. “Undefined method ‘getProcessesNativeQuery’. The method name must start with either findBy or findOneBy!”
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);
}