Adding sorting and filtering to tabular data display in Symfony2

Site has moved

This site has moved to a new location. Visit the new site at http://programsdream.nl.

By Ronald van Belzen | April 28, 2016

In the previous article I described how to add pagination to tabular data display. Here I will describe how to add sorting and filtering, which will result in the following end result:

Symfony2 pagination

Sorting

The request parameter sort will receive a value that both contains the name of the field and the sort direction seperated by a dot. This value is included in the parameters passed to the twig file "list.html.twig" together with a value for the request of the reverse sort direction ("pager.sort_reverse") and the name of the current sort field ("pager.sort_field") and sort direction ("pager.sort_order") seperately. In the twig file we first change the table header to the following:

    <thead>
      <tr>
        {% if pager.sort_field == 'username' %}
          <th class="sorted {% if pager.sort_order == 'ASC' %}ascending{% else %}descending{% endif %}">
            <a href="{{ path('user_list', {'sort': pager.sort_reverse}) }}">{{ 'user.list.login'|trans }}</a>
          </th>
        {% else %}
          <th>
            <a href="{{ path('user_list', {'sort': 'username.asc'}) }}">{{ 'user.list.login'|trans }}</a>
          </th>
        {% endif %}
        {% if pager.sort_field == 'email' %}
          <th class="sorted {% if pager.sort_order == 'ASC' %}ascending{% else %}descending{% endif %}">
            <a href="{{ path('user_list', {'sort': pager.sort_reverse}) }}">{{ 'user.list.email'|trans }}</a>
          </th>
        {% else %}
          <th>
            <a href="{{ path('user_list', {'sort': 'email.asc'}) }}">{{ 'user.list.email'|trans }}</a>
          </th>
        {% endif %}
        {% if pager.sort_field == 'fullname' %}
          <th class="sorted {% if pager.sort_order == 'ASC' %}ascending{% else %}descending{% endif %}">
            <a href="{{ path('user_list', {'sort': pager.sort_reverse}) }}">{{ 'user.list.fullname'|trans }}</a>
          </th>
        {% else %}
          <th>
            <a href="{{ path('user_list', {'sort': 'fullname.asc'}) }}">{{ 'user.list.fullname'|trans }}</a>
          </th>
        {% endif %}
        <th>{{ 'user.list.roles'|trans }}</th>
        <th>{{ 'user.list.active'|trans }}</th>
        <th style="width:125px;"></th>
      </tr>
    </thead>

You will notice that the links in the above do not contain any page information. This will result in the resetting of the page to the first page when the sorting is changed. This is a design decision, but when you do not agree with this you can add the page number to the links as follows:

<a href="{{ path('user_list', {'sort': pager.sort_reverse, 'page': pager.current_page }) }}">{{ 'user.list.login'|trans }}</a>

The field for which the display is sorted shows an up or down button (Sematic UI is being used as CSS framework). The other fields have ascending as default sort direction. There is no reason why the "enabled" field is not sorted, but the "roles" field is not sorted because the value of this field in the database table does lend itself to be sorted in a sensible way.

When we would change the page number the sorting will be lost. To prevent this from happening we will need to add the current sorting ("pager.sort") to the page button links:

  {% 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), 'sort': pager.sort}) }}"
      {% 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, 'sort': pager.sort }) }}">
          {{ 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), 'sort': pager.sort}) }}"
      {% endif %}>
        &gt;
      </a>
    </div>
  </div>
  {% endif %}
{% endblock %}

Next we change the PageUtility class to handle the sorting. In the constructor the call of the function setSorting() is added. This function reads the sort request parameter and prepares the parameters to be passed to the display. In the function getDisplayParameters() these new sort parameters are added, and in the function getRecords() the optional sorting is added.

Important is that the function setSorting() checks whether the field name actually exists in the database table (security).

The sorting is optional, but also the constructor variable $default_sort_field has become optional. Also this constructor variable may now either contain the default sort field name or the sort field name followed by a dot and the sort direction (e.g. "fullname.desc").

class PageUtility {

    // ...

    // Sort field
    private $defaultSortField;
    private $sortField;
    private $sortDirection;
    private $sortReverse;
    
    // ...
    
    public function __construct(Request $request, EntityManager $em, $entity_name, $page_size = 20, $default_sort_field = null) {
        $this->request = $request;
        $this->em = $em;
        $this->entityName = $entity_name;
        $this->pageSize = $page_size;
        $this->totalRecords = $this->getTotal();
        $this->setCurrentPage();
        $this->defaultSortField = $default_sort_field;
        $this->setSorting();
    }
    
    // ...
    
    /**
     * Get the parameters for the page display
     */
    public function getDisplayParameters() {
        $return = array(
            'current_page' => $this->currentPage,
            'total_pages' => $this->totalPages,
            'sort_field' => $this->sortField,
            'sort_order' => $this->sortDirection,
            'sort_reverse' => $this->sortReverse,
        );
        if(empty($this->sortField)) {
            $return['sort'] = '';
        } else {
            $return['sort'] = $this->sortField . '.' . strtolower($this->sortDirection);
        }
        return $return;
    }


    /**
     * Get the records for the current page
     */
    public function getRecords() {
        $records = $this->em->getRepository($this->entityName)
            ->createQueryBuilder('t');
        if(!empty($this->sortField)) {
            $records = $records->orderBy('t.' . $this->sortField, $this->sortDirection);
        }
        $records = $records->setFirstResult($this->offset)
            ->setMaxResults($this->pageSize)
            ->getQuery()
            ->getResult();
        return $records;
    }
        
    /**
     * Set the sorting fields
     */
    private function setSorting() {
        $sort = $this->request->get('sort');
        if(empty($sort) && empty($this->defaultSortField)) {
            $this->sortField = '';
            $this->sortDirection = '';
        } else {
            if(empty($sort)) {
                $arr = explode('.', $this->defaultSortField);
            } else {
                $arr = explode('.', $sort);
            }
            if(empty($arr[0])) {
                $this->sortField = '';
                $this->sortDirection = '';
            } elseif(count($arr) == 1 || empty($arr[1])) {
                $this->sortField = $arr[0];
                $this->sortDirection = 'ASC';
                $this->sortReverse = $this->sortField . '.desc';
            } else {
                $this->sortField = $arr[0];
                if(strtolower($arr[1]) == 'desc') {
                    $this->sortDirection = 'DESC';
                    $this->sortReverse = $this->sortField . '.asc';
                } else {
                    $this->sortDirection = 'ASC';
                    $this->sortReverse = $this->sortField . '.desc';
                }
            }
            // Validate sort field
            if( !$this->em->getClassMetadata($this->entityName)->hasField($this->sortField) ) {
                $this->sortField = '';
                $this->sortDirection = '';
            }
        }
    }
}

No changes need to be made to the controller class function listAction(). Sorting and pagination are fully functional at this point.

Now let's add filtering to the mix.

Filtering

The filter request parameter will be an array with the field name as key. We can implement this in the twig file ("list.html.twig") by adding a form at the end of the header:

      <form method="get">
        <tr>
          <th>
            <input type="text" name="filter[username]" 
            value="{% if pager.filter.username is defined %}{{ pager.filter.username }}{% endif%}" 
            onchange="this.form.submit();" style="width:100%" />
          </th>
          <th>
            <input type="text" name="filter[email]" 
            value="{% if pager.filter.email is defined %}{{ pager.filter.email }}{% endif%}" 
            onchange="this.form.submit();" style="width:100%" />
          </th>
          <th>
            <input type="text" name="filter[fullname]" 
            value="{% if pager.filter.fullname is defined %}{{ pager.filter.fullname }}{% endif%}" 
            onchange="this.form.submit();" style="width:100%" />
          </th>
          <th></th>
          <th>
            <select name="filter[enabled]" onchange="this.form.submit();">
              <option value=""></option>
              <option value="1" {% if pager.filter.enabled is defined and pager.filter.enabled == 1 %}
                {{ 'selected="selected"' }}
              {% endif%}>{{ 'user.select.yes'|trans }}</option>
              <option value="0" {% if pager.filter.enabled is defined and pager.filter.enabled == 0 %}
                {{ 'selected="selected"' }}
              {% endif%}>{{ 'user.select.no'|trans }}</option>
            </select>
          </th>
          <th></th>
        </tr>
        <input type="hidden" name="sort" value="{{ pager.sort }}" />
      </form>
    </thead>

A hidden input field is added for retaining the sorting when changing a filter field. A hidden field can be added when the current page number also needs to be retained here.

Now we also need to change page button links in the twig file to retain the filtering settings:

  {% 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), 'sort': pager.sort, 'filter': pager.filter }) }}"
      {% 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, 'sort': pager.sort, 'filter': pager.filter }) }}">
          {{ 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), 'sort': pager.sort, 'filter': pager.filter }) }}"
      {% endif %}>
        &gt;
      </a>
    </div>
  </div>
  {% endif %}
{% endblock %}

In the PageUtility class the $filter array is added and in the constructor the new function setFilter() needs to be called before the determination of the total number of records:

class PageUtility {

    // ....

    // Filter array
    private $filter;
    
    public function __construct(Request $request, EntityManager $em, $entity_name, $page_size = 20, $default_sort_field = null) {
        $this->request = $request;
        $this->em = $em;
        $this->entityName = $entity_name;
        $this->pageSize = $page_size;
        $this->setFilter();
        $this->totalRecords = $this->getTotal();
        $this->setCurrentPage();
        $this->defaultSortField = $default_sort_field;
        $this->setSorting();
    }
 

setFilter() is just a sanitizing of the array:

    /**
     * Set Filter (remove keys for empty values)
     */
    private function setFilter() {
        $this->filter = array();
        $filters = $this->request->get('filter');
        if(is_array($filters)) {
            foreach($filters as $key => $value) {
                if(!empty($value) || $value == '0') {
                    $this->filter[$key] = $value;
                }
            }
        }
    }

The real work is done in the functions getTotal() and getRecords():

    /**
     * Get the total number of records
     */
    private function getTotal() {
        $total = $this->em->getRepository($this->entityName)
            ->createQueryBuilder('t')
            ->select('count(t.id)');
        if(!empty($this->filter)) {
            foreach($this->filter as $key => $value) {
                if( $this->em->getClassMetadata($this->entityName)->hasField($key) ) {
                    $total = $total->andWhere("t.{$key} LIKE :{$key}");
                    if( $this->em->getClassMetadata($this->entityName)->getTypeOfField($key) === 'string' || 
                        $this->em->getClassMetadata($this->entityName)->getTypeOfField($key) === 'text' ) {
                        $total = $total->setParameter($key, '%' . $value . '%');
                    } else {
                        $total = $total->setParameter($key, $value);
                    }
                }
            }
        }
        $total = $total->getQuery()->getSingleScalarResult();
        return $total;
    }

    // ...

    /**
     * Get the records for the current page
     */
    public function getRecords() {
        $records = $this->em->getRepository($this->entityName)
            ->createQueryBuilder('t');
        if(!empty($this->sortField)) {
            $records = $records->orderBy('t.' . $this->sortField, $this->sortDirection);
        }
        if(!empty($this->filter)) {
            foreach($this->filter as $key => $value) {
                if( $this->em->getClassMetadata($this->entityName)->hasField($key) ) {
                    $records = $records->andWhere("t.{$key} LIKE :{$key}");
                    if( $this->em->getClassMetadata($this->entityName)->getTypeOfField($key) === 'string' || 
                        $this->em->getClassMetadata($this->entityName)->getTypeOfField($key) === 'text' ) {
                        $records = $records->setParameter($key, '%' . $value . '%');
                    } else {
                        $records = $records->setParameter($key, $value);
                    }
                }
            }
        }
        $records = $records->setFirstResult($this->offset)
            ->setMaxResults($this->pageSize)
            ->getQuery()
            ->getResult();
        return $records;
    }

Both functions check whether the field name actually exists in the database table, and when it exists (and has a value) checks whether the field has a string format in the database or not.

Again, no changes need to be made to the controller class function listAction().

We have now added pagination, sorting and filtering to a tabular display of data with the help of an utility class that can be used for any tabular display of data from a single database table. The next step would be to change the UtilityClass into a Service that is part of the Service Container of Symfony2.

We will do that in the next article of this series.