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…<\/p>\n
Anyway, here it is in a nutshell, until I find some time to turn this into a bundle.<\/p>\n
Goal: paginate records that are obtained both with NativeQuery and QueryBuilder in Doctrine.<\/p>\n
What do we have:<\/p>\n
\n
- entity called Process<\/li>\n<\/ul>\n
What should we achieve at the end of this post:<\/p>\n
\n
- one listing page with records resulted from a NativeQuery – paginated<\/li>\n
- one listing page with records resulted from QueryBuilder – paginated<\/li>\n<\/ul>\n<\/blockquote>\n
First, let’s take care of Controller.<\/p>\n
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:<\/p>\n
\/\/ Ibw\/BlogBundle\/Controller\/ProcessController.php\r\n\r\nuse IbwBlogBundleLibPaginator;\r\n\r\n...\r\n\r\npublic function indexNativeAction($filter)\r\n{\r\n \/\/ get the Process repository - this is where you'll store your queries, etc\r\n $repository = $this->getDoctrine()->getRepository('IbwBlogBundle:Process');\r\n\r\n \/\/ our custom paginator\r\n $paginator = new Paginator();\r\n\r\n \/\/ this is the query for listing\r\n $queryProcesses = $repository->getProcessesNativeQuery();\r\n\r\n \/\/ paginating \r\n $pagination = $paginator->paginate($queryProcesses,\r\n \/\/ page, default 1\r\n $this->get('request')->query->get('page', 1),\r\n \/\/ how many results per page, taken from parameters (so we won't hardcode)\r\n $this->container->getParameter('pagination_limit_page')); \r\n\r\n \/\/ finally, preparing the view \r\n return $this->render('IbwBlogBundle:Process:indexNative.html.twig', array( \r\n 'pagination' => $pagination, \r\n 'paginator' => $paginator, \r\n )); \r\n}<\/pre>\nThen, 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).<\/p>\n
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.<\/p>\n
<?php\r\n\/\/ Ibw\/BlogBundle\/Lib\/Paginator.php \r\n\r\nnamespace IbwBlogBundleLib;\r\n\r\nuse DoctrineORMQueryResultSetMappingBuilder;\r\nuse DoctrineORMToolsPaginationPaginator as DoctrinePaginator;\r\n\r\nclass Paginator\r\n{\r\n private $count;\r\n private $currentPage;\r\n private $totalPages;\r\n\r\n \/**\r\n * paginate results\r\n *\r\n * @param $query - naming is a bit off as it can be a NativeQuery OR QueryBuilder, we'll survive eventually\r\n * @param int $page\r\n * @param $limit\r\n * @return array\r\n *\/\r\n public function paginate($query, $page = 1, $limit)\r\n {\r\n \/\/ setting current page \r\n $this->currentPage = $page;\r\n \/\/ set the limit \r\n $limit = (int)$limit;\r\n\r\n \/\/ this covers the NativeQuery case\r\n if (is_a($query, 'DoctrineORMNativeQuery'))\r\n {\r\n \/\/ do a count for all query, create a separate NativeQuery only for that\r\n $sqlInitial = $query->getSQL();\r\n\r\n $rsm = new ResultSetMappingBuilder($query->getEntityManager());\r\n $rsm->addScalarResult('count', 'count');\r\n\r\n $sqlCount = 'select count(*) as count from (' . $sqlInitial . ') as item';\r\n $qCount = $query->getEntityManager()->createNativeQuery($sqlCount, $rsm);\r\n $qCount->setParameters($query->getParameters());\r\n\r\n $resultCount = (int)$qCount->getSingleScalarResult();\r\n $this->count = $resultCount;\r\n\r\n \/\/ then, add the limit - paginate for current page\r\n $query->setSQL($query->getSQL() . ' limit ' . (($page - 1) * $limit) . ', ' . $limit);\r\n } \r\n \/\/ this covers the QueryBuilder case, turning it into Query\r\n elseif(is_a($query, 'DoctrineORMQueryBuilder'))\r\n {\r\n \/\/ set limit and offset, getting the query out of queryBuilder\r\n $query = $query->setFirstResult(($page -1) * $limit)->setMaxResults($limit)->getQuery();\r\n\r\n \/\/ using already build Doctrine paginator to get a count\r\n \/\/ for all records. Saves load.\r\n $paginator = new DoctrinePaginator($query, $fetchJoinCollection = true);\r\n $this->count = count($paginator);\r\n }\r\n\r\n \/\/ set total pages\r\n $this->totalPages = ceil($this->count \/ $limit);\r\n\r\n return $query->getResult();\r\n }\r\n\r\n \/**\r\n * get current page\r\n *\r\n * @return int\r\n *\/\r\n public function getCurrentPage()\r\n {\r\n return $this->currentPage;\r\n }\r\n\r\n \/**\r\n * get total pages\r\n *\r\n * @return int\r\n *\/\r\n public function getTotalPages()\r\n {\r\n return $this->totalPages;\r\n }\r\n\r\n \/**\r\n * get total result count\r\n *\r\n * @return int\r\n *\/\r\n public function getCount()\r\n {\r\n return $this->count;\r\n }\r\n}<\/pre>\nNow, 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):<\/p>\n
\/\/ Ibw\/BlogBundle\/Entity\/ProcessRepository.php\r\n\r\n\/**\r\n* query for all processes\r\n*\r\n* @return DoctrineORMNativeQuery\r\n*\/\r\npublic function getProcessesNativeQuery()\r\n{\r\n $rsm = new ResultSetMappingBuilder($this->getEntityManager());\r\n $rsm->addRootEntityFromClassMetadata('IbwBlogBundleEntityProcess', 'p');\r\n\r\n $q = $this->getEntityManager()\r\n ->createNativeQuery('select * from Process p order by p.created_at desc', $rsm);\r\n\r\n return $q;\r\n}<\/pre>\nI think we have all setup for the final step: view . This is where all takes shape.<\/p>\n
Let’s create the actual listing page, indexNative.html.twig.<\/p>\n
{# Ibw\/BlogBundle\/Resources\/views\/Process\/indexNative.html.twig #}\r\n\r\n{% block content %} {# or whatever your content block is called #}\r\n\r\n{# throw some title #}\r\n<h1>Processes<\/h1>\r\n\r\n{# this is an attempt to make a header - replace with your own #}\r\n<div class=\"row-fluid\">\r\n<div class=\"span8 text-left\"><strong>Process<\/strong><\/div>\r\n<div class=\"span2 text-center\"><strong>Approved?<\/strong><\/div>\r\n<div class=\"span2 text-right\"><strong>Manage<\/strong><\/div>\r\n<\/div>\r\n\r\n{# the actual Process listing - this is where Pagination kicks in #}\r\n{% for process in pagination %}\r\n {# we have a separate template for Process listing #}\r\n {{ include('IbwPtoolBundle:Process:_item_listing.html.twig', {'process': process}) }}\r\n{% endfor %}\r\n\r\n{# display navigation for pages - paginator_navigator #}\r\n{% if pagination|length > 0 and paginator.getTotalPages() > 1 %}\r\n {{ include('IbwBlogBundle:Paginator:paginator_navigator.html.twig', {'paginator' : paginator}) }}\r\n{% endif %}\r\n\r\n{% endblock %}<\/pre>\nLet’s see how the template for one Process entity listing looks like:<\/p>\n
{# Ibw\/BlogBundle\/Resources\/views\/Process\/_item_listing.html.twig #}\r\n\r\n<div class=\"row-fluid\">\r\n <div class=\"span8\">\r\n {{ process.name }}\r\n <\/div>\r\n <div class=\"span2 text-center\">\r\n {{ process.isApproved }} \r\n <\/div>\r\n <div class=\"span2\">\r\n <a href=\"#\" title=\"Edit\">Edit process<\/a>\r\n <\/div>\r\n<\/div><\/pre>\nAnd the pagination navigator:<\/p>\n
{# Ibw\/BlogBundle\/Resources\/views\/Paginator\/paginator_navigator.html.twig #}\r\n\r\n{% set currentPath = path(app.request.get('_route'), app.request.get('_route_params')) %}\r\n{% set currentPage = paginator.getCurrentPage() %}\r\n{% set totalPages = paginator.getTotalPages() %}\r\n\r\n<div class=\"pagination pagination-centered\">\r\n<ul>\r\n{% if currentPage > 1 %}\r\n <li><a href=\"{{ currentPath }}?page=1\">«<\/a><\/li>\r\n <li><a href=\"{{ currentPath }}?page={{ currentPage > 1 ? currentPage - 1 : 1 }}\"><<\/a><\/li>\r\n{% endif %}\r\n\r\n{% if (currentPage - 3) > 0 %}\r\n <li><a href=\"#\">...<\/a><\/li>\r\n{% endif %}\r\n{% if (currentPage - 2) > 0 %}\r\n <li><a href=\"{{ currentPath }}?page={{ currentPage - 2 }}\">{{ currentPage - 2 }}<\/a><\/li>\r\n{% endif %}\r\n{% if (currentPage - 1) > 0 %}\r\n <li><a href=\"{{ currentPath }}?page={{ currentPage - 1 }}\">{{ currentPage - 1 }}<\/a><\/li>\r\n{% endif %}\r\n<li class=\"active\"><a href=\"#\">{{ currentPage }}<\/a><\/li>\r\n{% if (currentPage + 1) <= totalPages %}\r\n <li><a href=\"{{ currentPath }}?page={{ currentPage + 1 }}\">{{ currentPage + 1 }}<\/a><\/li>\r\n{% endif %}\r\n{% if (currentPage + 2) <= totalPages %}\r\n <li><a href=\"{{ currentPath }}?page={{ currentPage + 2 }}\">{{ currentPage + 2 }}<\/a><\/li>\r\n{% endif %}\r\n{% if (currentPage + 2) < totalPages %}\r\n <li><a href=\"#\">...<\/a><\/li>\r\n{% endif %}\r\n\r\n{% if currentPage < totalPages %}\r\n <li><a href=\"{{ currentPath }}?page={{ currentPage < totalPages ? currentPage + 1 : currentPage }}\">><\/a><\/li>\r\n <li><a href=\"{{ currentPath }}?page={{ totalPages }}\">»<\/a><\/li>\r\n{% endif %}\r\n\r\n<\/ul>\r\n<\/div><\/pre>\nAll 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<\/p>\n
# Ibw\/BlogBundle\/Resources\/config\/parameters.yml\r\n\r\nparameters:\r\n pagination_limit_page: 10<\/pre>\nAt this point, you have everything working with NativeQuery.<\/p>\n
What about QueryBuilder?<\/h3>\n
Basically it’s all the same, except one thing:\u00a0repository should return a QueryBuilder (instead of NativeQuery).<\/p>\n
For that, create a new method in repository called getProcessesQueryBuilder(), like this:<\/p>\n
\/\/ Ibw\/BlogBundle\/Entity\/ProcessRepository.php\r\n\r\n\/**\r\n* query for all processes\r\n*\r\n* @return DoctrineORMQueryBuilder\r\n*\/\r\npublic function getProcessesQueryBuilder()\r\n{\r\n $q = $this->createQueryBuilder('p')\r\n ->select('p')\r\n ->orderBy('p.createdAt', 'desc');\r\n\r\n return $q;\r\n}<\/pre>\nAfter that, of course you need to adjust the controller to call this for getting the results. Change the controller as follows:<\/p>\n
\/\/ \/Ibw\/BlogBundle\/Controller\/ProcessController.php\r\n\r\npublic function indexNativeAction($filter)\r\n{\r\n ...\r\n\r\n \/\/ this is the query for listing\r\n $queryProcesses = $repository->getProcessesQueryBuilder();\r\n\r\n ...\r\n}<\/pre>\n<\/p>\n
This should be enough for you application to handle pagination for both NativeQueries and DQL in Doctrine.<\/p>\n","protected":false},"excerpt":{"rendered":"
Symfony2.3 pagination I’ve allocated quite a reasonable amount of time to find a good bundle to achieve pagination AND use […]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[82],"tags":[123,124,177,190,195,238,240],"yst_prominent_words":[6,320,334,798,1133,1395,1398],"post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/posts\/510"}],"collection":[{"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/comments?post=510"}],"version-history":[{"count":7,"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/posts\/510\/revisions"}],"predecessor-version":[{"id":133249,"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/posts\/510\/revisions\/133249"}],"wp:attachment":[{"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/media?parent=510"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/categories?post=510"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/tags?post=510"},{"taxonomy":"yst_prominent_words","embeddable":true,"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/yst_prominent_words?post=510"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}