Create pagination, sorting and filtering of tabular data display in Symfony2

By Ronald van Belzen | April 26, 2016

The addition of pagination, sorting and filtering to a tabular display of data is a relatively simple task. No doubt there is more then one bundle in Symfony2 that tackles this task for you (e.g. KnpLabs/KnpPaginatorBundle for a SEO friendly paginator). Here I will demonstrate the relative ease in which you can combine pagination, sorting and filtering of tabular data display. For this example I will use the well known User table. In this case the User table created by the FOSUserBundle to which I added the field fullname.

<?php 
namespace AppBundle\Entity;

use FOS\UserBundle\Entity\User as BaseUser;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="app_user")
 */
class User extends BaseUser {
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;
	
    /**
     * @ORM\Column(type="string", length=100, nullable=true)
     */
    private $fullname;

    /**
     * Set fullname
     *
     * @param string $fullname
     *
     * @return User
     */
    public function setFullname($fullname)
    {
        $this->fullname = $fullname;

        return $this;
    }

    /**
     * Get fullname
     *
     * @return string
     */
    public function getFullname()
    {
        return $this->fullname;
    }
}

The controller class function to which I will add pagination looked like this:

<?php 
namespace AppBundle\Controller;

use AppBundle\Entity\User;
use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpKernel\Exception\NotFoundHttpException;

class AdminController extends Controller {
    /**
     * Handle the user listing display
     */
    public function listAction(Request $request) {
        $em = $this->getDoctrine()->getManager();
        
        $users = $em->getRepository('AppBundle:User')
            ->createQueryBuilder('t')
            ->orderBy('t.username', 'ASC')
            ->getQuery()
            ->getResult();
		
        return $this->render('admin/list.html.twig',[
            'users' => $users,
        ]);
    }
    
    // Other functions.
    
}	

And the twig file ("list.html.twig") for the display of the data in a tabular format looked like this:

{% extends '::base.html.twig' %}

{% block body %}
    <h1 class="ui aligned header">{{ 'user.list.title'|trans }}</h1>
    
    {% for flashMessage in app.session.flashbag.get('notice') %}
    <div class="ui green icon message">
        <i class="info circle icon"></i>
        <div class="content">
            <div class="header">The action was succesful!</div>
            <p>{{ flashMessage }}</p>
        </div>
    </div>
    {% endfor %}

    <div style="float:right;">
        <a href="{{ path('user_add') }}" title="{{ 'user.button.add_user'|trans }}">
            <button class="large ui icon button"><i class="add user icon"></i></button>
        </a>
    </div>
	
    <h2 class="ui aligned header">{{ 'user.list.header'|trans }}</h2>
    <table class="ui compact blue sortable table">
        <thead>
            <tr>
                <th>{{ 'user.list.login'|trans }}</th>
                <th>{{ 'user.list.email'|trans }}</th>
                <th>{{ 'user.list.fullname'|trans }}</th>
                <th>{{ 'user.list.roles'|trans }}</th>
                <th>{{ 'user.list.active'|trans }}</th>
                <th style="width:125px;"></th>
            </tr>
        </thead>
        <tbody>
            {% for user in users %}
            <tr>
                <td>{{ user.username }}</td>
                <td>{{ user.email }}</td>
                <td>{{ user.fullname }}</td>
                <td>{{ user.roles|join(', ') }}</td>
                <td>{{ user.enabled == 1 ? "Yes" : "No" }}</td>
                <td>
                    <a href="{{ path('user_edit', {'id': user.id}) }}" title="Edit">
                        <button class="mini ui icon button"><i class="edit icon"></i></button>
                    </a>
                </td>
            </tr>
            {% else %}
            <tr>
                <td colspan="6" class="center align"><h3>{{ 'user.list.notfound'|trans }}</h3>/td>
            </tr>
            {% endfor %}
        </tbody>
    </table>
{% endblock %}

The classes refer to the Semantic UI CSS framework. The flash message is used by delete and update functionality that redirects to this page when successful. There is an add botton for the link to the add user page, which is not part of this article. The rest of the above twig file should be self explaining when you are familiar with Twig.

Pagination

Symfony2 pagination
Tabular display with pagination, sorting and filtering

 

For pagination we need the following information for the display of data in a tabular format:

  • The number of records to be displayed per page
  • The total number of records
  • The current page
  • The number of pages

We create an utility class to contain this information and name it PageUtility:

<?php
namespace AppBundle\Utils;

use Doctrine\ORM\EntityManager;
use Symfony\Component\HttpFoundation\Request;

class PageUtility {
    // Request
    private $request;
    // EntityManager
    private $em;
    // Entity name of the database table
    private $entityName;
    // Number of records per page
    private $pageSize;
    // Current page number
    private $currentPage;
    // Total number of pages
    private $totalPages;
    // Total records
    private $totalRecords;
    // Offset of the current page for db table
    private $offset;
    // Sort field
    private $sortField;
    
    
    public function __construct(Request $request, EntityManager $em, $entity_name = null, $page_size = 20, $default_sort_field = null) {
        $this->request = $request;
        $this->em = $em;
        $this->entityName = $entity_name;
        $this->pageSize = $page_size;
        $this->sortField = $default_sort_field;
        $this->totalRecords = $this->getTotal();
        $this->setCurrentPage();
    }
    
    // ...

The plan is to move all the functionality for the preparation of the display to PageUtility. For this purpose the Request and EntityManager objects are passed to the PageUtility and also the entity name of the database table ('AppBundle:User'). The page size can be set and the sort field will be mandatory for now (will become optional).

The constructor calls two functions that will retrieve the information needed for the pagination. First the total number of records in the database table needs to be determined:

    /**
     * Get the total number of records
     */
    private function getTotal() {
        $total = $this->em->getRepository($this->entityName)
            ->createQueryBuilder('t')
            ->select('count(t.id)')
            ->getQuery()
            ->getSingleScalarResult();
        return $total;
    }

With this information everything else can be calculated in setCurrentPage():

    /**
     * Set the current page and total number of pages
     */
    private function setCurrentPage() {
        $this->currentPage = $this->request->get('page');
        if(empty($this->currentPage)) {
            $this->currentPage = 1;
        }
        $this->totalPages = ceil($this->totalRecords/$this->pageSize);
        if(($this->currentPage * $this->pageSize) > $this->totalRecords) {
            $this->currentPage = $this->totalPages;
        }
        // Offset for db table
        if($this->currentPage > 1) {
            $this->offset = ($this->currentPage - 1) * $this->pageSize;
        } else {
           $this->offset = 0;
        }
    }

Now we need a function that delivers the data to be displayed on the current page and a function to produce the parameters required for the pagination:

    /**
     * Get the records for the current page
     */
    public function getRecords() {
        $records = $this->em->getRepository($this->entityName)
            ->createQueryBuilder('t');
            ->orderBy('t.' . $this->sortField, 'ASC');
            ->setFirstResult($this->offset)
            ->setMaxResults($this->pageSize)
            ->getQuery()
            ->getResult();
        return $records;
    }
    
    /**
     * Get the parameters for the page display
     */
    public function getDisplayParameters() {
        $return = array(
            'current_page' => $this->currentPage,
            'total_pages' => $this->totalPages,
        );
        return $return;
    }

You will notice that pagination display only needs the current page number and the total number of pages.

The controller class function is now changed. It will only create an instance of the PageUtility class and create the response with the help of the PageUtility functions.

// ...
use AppBundle\Utils\PageUtility;

class AdminController extends Controller {
    /**
     * Handle the user listing display (for now only pagination)
     */
    public function listAction(Request $request) {
        $em = $this->getDoctrine()->getManager();
        // Paginator and sort settings
        $pu = new PageUtility($request, $em, 'AppBundle:User', 10, 'username');
        return $this->render('admin/list.html.twig',[
            'users' => $pu->getRecords(),
            'pager' => $pu->getDisplayParameters(),
        ]);
    }
    
    // ...

Finally we can add the actual page selection options to the display in the twig file ("list.html.twig") just below the table:

// ...
        </tbody>
    </table>
    {% if pager.total_pages > 1 %}
  <div style="width:100%;text-align:right;">
    <div class="ui pagination menu">
      <a class="{% if pager.current_page == 1 %}disabled {% endif %}item" 
      {% if pager.current_page > 1 %}
        href="{{ path('user_list', {'page': (pager.current_page - 1)}) }}"
      {% endif %}>
        &lt;
      </a>

      {% for i in 1..pager.total_pages %}
        <a class="{% if pager.current_page == loop.index %}active {% endif %}item"
          href="{{ path('user_list', {'page': loop.index}) }}">
            {{ loop.index }}
        </a>
      {% endfor %}
			
      <a class="{% if pager.current_page == pager.total_pages %}disabled {% endif %}item"
      {% if pager.current_page < pager.total_pages %}
        href="{{ path('user_list', {'page': (pager.current_page + 1)}) }}"
      {% endif %}>
        &gt;
      </a>
    </div>
  </div>
  {% endif %}
{% endblock %}

The pager is only displayed when there is more than a single page ("pager.total_pages > 1"). A previous page and a next page are displayed before and after the loop that displays the page numbers. The current page gets the class value "active". The loop displays all available pages, which might not be a good idea when the table will be very large.

When you want to limit the number of page options to be displayed, you will need to rewrite this part by adding a test whether the number of pages exceeds a certain limit, and in that case write an alternative display of the page options. I will leave it to you to do this.

In the next article of this series I will desribe how sorting and filtering can be added to the tabular display.

Add new comment