I love DataTables library and the infinite possibilities it offers. And I have been happily using it for years for small and medium projects. However, when working with large amounts of data, JavaScript handling of the entire data is just not possible. The official documentation talks of slowdowns when dealing with “many thousands or millions of data rows”. However, in practice, I have found that a few hundred is enough to cast a shadow on that perfect user experience we all aim for. So this is where the “Server-side processing” comes in handy. Instead of handling all data operations (fetch, sort, search, paging) on the client side, the DataTables library offers the possibility to perform these actions on the server side and return to the client only the relevant and visible data. The DataTables library has a server-side processing feature which allows the client to make an ajax call to the server with specific parameters and then render the response. This tutorial explains one way of handling this server processing in Symfony.

Aims of this tutorial :

• Server-side processing of DataTables ajax queries
• Implementation of individual column searching

Resources :

• DataTables.net – Server-side processing
• DataTables.net – Individual column searching

Symfony version: 3.4 (LTS), but should work with all 3+ versions

Let’s get started!

Part 1: Entities

In this tutorial, I will be working with the entity structure described in my article dealing with the French towns and departments organization.

# town (id, name, postalCode, department_id #)
# department (id, name, postalCode, region_id #)
# region (id, name, code)

I will not provide the Entity definitions, as they are pretty straightforward.

• A Town has a ManyToOne relationship with Department (a Department has many Towns)
• A Department has a ManyToOne relationship with Region (a Region has many Departments)

Part 2: Routing

playground_town_list_datatables:
    path: /town/list/datatables
    defaults:
        _controller: PlaygroundCookiejarBundle:Town:listDatatables

playground_town_list:
    path: /town/list
    defaults:
        _controller: PlaygroundCookiejarBundle:Town:list

playground_town_view:
    path: /town/{id}/view
    defaults:
        _controller: PlaygroundCookiejarBundle:Town:view
        requirements:
            id: \d+

Two routes are mandatory: one for displaying the listing template and a second one for handling the Ajax call from the DataTables library.

I have included a third one which allows viewing a town, to show how I include Symfony routes in the Ajax response.

Part 3: Template and Ajax call

<table id="towns_dt_table">
    <thead>
        <tr>
            <th>Postal Code</th>
            <th>Town</th>
            <th>Department</th>
            <th>Region</th>
        </tr>
    </thead>
    <tbody></tbody>
</table>

$(document).ready(function(){
    
    var table = $('#towns_dt_table').DataTable({
        "columnDefs": [
        
            // These are the column name variables that will be sent to the server
            { "name": "postalCode",   "targets": 0 },
            { "name": "name",   "targets": 1 },
            { "name": "department",  "targets": 2 },
            { "name": "region",  "targets": 3 },
        
        ],
        // Server-side parameters
        "processing": true,
        "serverSide": true,
        // Ajax call
        "ajax": {
            "url": "{{ path('playground_town_list_datatables') }}",
            "type": "POST"
        },
        // Classic DataTables parameters
        "paging" : true,
        "info" : true, 
        "searching": true,
        "pageLength": 10,
        "order": [[2, 'asc']]
    });
});

All we need in this part is an empty table and the Ajax call which will fetch the data to be rendered in the table.

The important thing to remember is that the column names we provide in the DataTables initialization will be sent to the server by the Ajax call. They can be called however you like, as there is no actual connection with the Entity data here.

"columnDefs": [

    { "name": "postalCode",   "targets": 0 },
    { "name": "name",   "targets": 1 },
    { "name": "department",  "targets": 2 },
    { "name": "region",  "targets": 3 },
]

If we want to replace the default search box with individual column search boxes, more tweaking needs to be done.

First of all, we need to add a footer to the table, which will hold the input search boxes.

<table id="towns_dt_table">
    {# Add a tfoot which will actually be used to place the individual search boxes #}
    <tfoot>
        <tr>
            <th>Postal Code</th>
            <th>Town</th>
            <th>Department</th>
            <th>Region</th>
        </tr>
    </tfoot>
    <thead>
        <tr>
            <th>Postal Code</th>
            <th>Town</th>
            <th>Department</th>
            <th>Region</th>
        </tr>
        </thead>
    <tbody></tbody>
</table>

Next, the default search box needs to be hidden, and the footer moved to the upper part of the table. This is a question of personal preference, it can also be left in the bottom.

/* Hide default search box */
.dataTables_filter {
	display: none;
}

/* Move tfood in the upper part of the table */
tfoot {
    display: table-header-group;
}

Next, we are transforming the footer cells into input boxes (lines 4-10). I also add a little Font Awesome zoom icon (just remove the space in the placeholder to make it work). The DataTables definition remains unchanged. And finally, we bind an event to the search boxes telling them to do a DataTables search when something is entered in the input box (lines 34-47).

$(document).ready(function(){

    // Add a text input to each footer cell
    var pos = 1;
    $('#towns_dt_table tfoot th').each( function () {
        var title = $(this).text();
        $(this).html( "<input id='input" + pos + "' type='text' class='form-control' placeholder='& #xf002;' style='font-family:Arial, FontAwesome; width:100%'/>" );

        pos ++;
    });

    var table = $('#towns_dt_table').DataTable({
        "columnDefs": [
            { "name": "postalCode",   "targets": 0 },
            { "name": "name",   "targets": 1 },
            { "name": "department",  "targets": 2 },
            { "name": "region",  "targets": 3 },
        ],
        "processing": true,
        "serverSide": true,
        "ajax": {
            "url": '{{ path('playground_town_list_datatables') }}',
            "type": 'POST'
        },
        "paging" : true,
        "info" : true,
        "searching": true,
        "responsive": true,
        "pageLength": 10,
        "order": [[2, 'asc']]
    });

    // Apply the search
    pos = 1;
    table.columns().every( function () {
        var that = this;
    
        $("#input"+pos).on( 'keyup change', function () {
            if ( that.search() !== this.value )
            {
                that
                    .search( this.value )
                    .draw();
            }
        });
        pos++;
    });
});

Part 4: The Controller

The listAction is really simple since we are not fetching any objects from the database yet :

<?php

class TownController extends Controller
{
    public function listAction(Request $request)
    {
    	return $this->render('@CookiejarBundle/Town/list.html.twig');
    }
}

In order to avoid writing the same lines over and over again, I have a quick initialization function that I include in almost all my controllers :

<?php 

class TownController extends Controller
{
	protected $entityManager;
	protected $translator;
	protected $repository;

	// Set up all necessary variable
	protected function initialise()
	{
		$this->entityManager = $this->getDoctrine()->getManager();
		$this->repository = $this->entityManager->getRepository('PlaygroundCookiejarBundle:Town');
		$this->translator = $this->get('translator');
	}
}

All the heavy duty lifting is done in the listDatatablesAction , which is triggered by the Ajax call.

<?php

class TownController extends Controller
{
    public function listDatatablesAction(Request $request)
    {
        // Set up required variables
        $this->initialise();
    
        // Get the parameters from DataTable Ajax Call
        if ($request->getMethod() == 'POST')
        {
            $draw = intval($request->request->get('draw'));
            $start = $request->request->get('start');
            $length = $request->request->get('length');
            $search = $request->request->get('search');
            $orders = $request->request->get('order');
            $columns = $request->request->get('columns');
        }
        else // If the request is not a POST one, die hard
            die;
    
        // Process Parameters
    
        // Orders
        foreach ($orders as $key => $order)
        {
            // Orders does not contain the name of the column, but its number,
            // so add the name so we can handle it just like the $columns array
            $orders[$key]['name'] = $columns[$order['column']]['name'];
        }
    
        // Further filtering can be done in the Repository by passing necessary arguments
        $otherConditions = "array or whatever is needed";
    
        // Get results from the Repository
        $results = $this->repository->getRequiredDTData($start, $length, $orders, $search, $columns, $otherConditions = null);
    
        // Returned objects are of type Town
        $objects = $results["results"];
        // Get total number of objects
        $total_objects_count = $this->repository->count();
        // Get total number of results
        $selected_objects_count = count($objects);
        // Get total number of filtered data
        $filtered_objects_count = $results["countResult"];
    
        // Construct response
        $response = '{
            "draw": '.$draw.',
            "recordsTotal": '.$total_objects_count.',
            "recordsFiltered": '.$filtered_objects_count.',
            "data": [';
    
        $i = 0;
    
        foreach ($objects as $key => $town)
        {
            $response .= '["';
    
            $j = 0; 
            $nbColumn = count($columns);
            foreach ($columns as $key => $column)
            {
                // In all cases where something does not exist or went wrong, return -
                $responseTemp = "-";
    
                switch($column['name'])
                {
                    case 'name':
                    {
                        $name = $town->getName();
    
                        // Do this kind of treatments if you suspect that the string is not JS compatible
                        $name = htmlentities(str_replace(array("\r\n", "\n", "\r"), ' ', $name));
    
                        // View permission ?
                        if ($this->get('security.authorization_checker')->isGranted('view_town', $town))
                        {
                            // Get the ID
                            $id = $town->getId();
                            // Construct the route
                            $url = $this->generateUrl('playground_town_view', array('id' => $id));
                            // Construct the html code to send back to datatables
                            $responseTemp = "<a href='".$url."' target='_self'>".$ref."</a>";
                        }
                        else
                        {
                            $responseTemp = $name;
                        }
                        break;
                    }
    
                    case 'postalCode':
                    {
                        // We know from the class definition that the postal code cannot be null
                        // But if that werent't the case, its value should have been tested
                        // before assigning it to $responseTemp
                        $responseTemp = $town->getPostalCode();
                        break;
                    }
    
                    case 'department':
                    {
                        $department = $town->getDepartment();
                        // This cannot happen if inner join is used
                        // However it can happen if left or right joins are used
                        if ($department !== null)
                        {
                            $responseTemp = $department->getName();
                        }
                        break;
                    }
                    case 'region':
                    {
                        $department = $town->getDepartment();
                        if ($department !== null)
                        {
                            $region = $department->getRegion();
                            if ($region !== null)
                            {
                                $responseTemp = $region->getName();
                            }
                        }
                        break;
                    }
                }
    
                // Add the found data to the json
                $response .= $responseTemp;
    
                if(++$j !== $nbColumn)
                    $response .='","';
            }
    
            $response .= '"]';
    
            // Not on the last item
            if(++$i !== $selected_objects_count)
                $response .= ',';
        }
    
        $response .= ']}';
    
        // Send all this stuff back to DataTables
        $returnResponse = new JsonResponse();
        $returnResponse->setJson($response);
    
        return $returnResponse;
    
    }
}

• Lines 11-19: Fetch the parameters sent through POST
  • $start and $length  are used for pagination
  • $columns contains the columns as defined in the columnDefs. When doing individual column searching, this parameter will also contain the search value.
  • $search contains the search box user input (global search only)
  • $orders  contains the required order column, as well as the sorting direction (ascending or descending)

Here is an example of POST data requiring the first 10 entries containing the word ‘Paris’ and sorted by the third column (2, since numbering starts at 0), which is the postal code:

start = 0
length = 10
search = Array(
    [value] => Paris
    [regex] => false
)

orders = Array(
    [0] => Array(
        [column] => 2
        [dir] => asc
    )
)

columns 
= Array(	
    [0] => Array(
        [data] => 0
        [name] => postalCode
        [searchable] => true
        [orderable] => true
        [search] => Array(
            [value] =>
            [regex] => false
        )
    )
    [1] => Array(
        [data] => 0
        [name] => name
        [searchable] => true
        [orderable] => true
        [search] => Array(
            [value] =>
            [regex] => false
        )
    )
    [2] => Array(
        [data] => 0
        [name] => department
        [searchable] => true
        [orderable] => true
        [search] => Array(
            [value] =>
            [regex] => false
        )
    )
    [3] => Array(
        [data] => 0
        [name] => region
        [searchable] => true
        [orderable] => true
        [search] => Array(
            [value] =>
            [regex] => false
        )
    )
)

• Lines 26-31: The $orders parameter is the only one that does not contain the actual name of the columns as defined in columnDefs. Adding it before going any further makes thing more easy to handle.
• Lines 33-37: I usually need to add further conditions to the database queries. This is the case when I have some restrictions based on user privileges for example and thus I do not want to load all the objects corresponding to my entity. In order to do that, I add one or more optional parameters to my repository call.
• Line 37: Call the repository function which queries the database for all the objects required by the DataTables call
• Lines 39-55: Start building the JSON to send back to the client
• Line 57: We loop through all the objects fetched from the DataBase
• Line 63: For each object, we loop through the columns names
• Lines 64-127: For each object and for each column, we construct the appropriate response
  • Lines 72-92: This is how we can add a generated URL to the JSON response. I have also included some code that verifies user privileges before constructing the URL.

Part 5: The Repository

In order to answer to the Ajax call, the Repository must query the database and find the Town objects which correspond to the custom paging, order and search sent by the client. In order to do this, we will construct a query based on these parameters.

<?php

class TownRepository extends \Doctrine\ORM\EntityRepository
{
    // Get the total number of elements
    public function count()
    {
        return $this
            ->createQueryBuilder('object')
            ->select("count(object.id)")
            ->getQuery()
            ->getSingleScalarResult();
    }

    public function getRequiredDTData($start, $length, $orders, $search, $columns, $otherConditions)
    {
        // Create Main Query
        $query = $this->createQueryBuilder('town');
        
        // Create Count Query
        $countQuery = $this->createQueryBuilder('town');
        $countQuery->select('COUNT(town)');
        
        // Create inner joins
        $query
            ->join('town.department', 'department')
            ->join('department.region', 'region');
        
        $countQuery
            ->join('town.department', 'department')
            ->join('department.region', 'region');
        
        // Other conditions than the ones sent by the Ajax call ?
        if ($otherConditions === null)
        {
            // No
            // However, add a "always true" condition to keep an uniform treatment in all cases
            $query->where("1=1");
            $countQuery->where("1=1");
        }
        else
        {
            // Add condition
            $query->where($otherConditions);
            $countQuery->where($otherConditions);
        }
        
        // Fields Search
        foreach ($columns as $key => $column)
        {
            if ($column['search']['value'] != '')
            {
                // $searchItem is what we are looking for
                $searchItem = $column['search']['value'];
                $searchQuery = null;
        
                // $column['name'] is the name of the column as sent by the JS
                switch($column['name'])
                {
                    case 'name':
                    {
                        $searchQuery = 'town.name LIKE \'%'.$searchItem.'%\'';
                        break;
                    }
                    case 'postalCode':
                    {
                        $searchQuery = 'town.postalCode LIKE \'%'.$searchItem.'%\'';
                        break;
                    }
                    case 'department':
                    {
                        $searchQuery = 'department.name LIKE \'%'.$searchItem.'%\'';
                        break;
                    }
                    case 'region':
                    {
                        $searchQuery = 'region.name LIKE \'%'.$searchItem.'%\'';
                        break;
                    }
                }
        
                if ($searchQuery !== null)
                {
                    $query->andWhere($searchQuery);
                    $countQuery->andWhere($searchQuery);
                }
            }
        }
        
        // Limit
        $query->setFirstResult($start)->setMaxResults($length);
        
        // Order
        foreach ($orders as $key => $order)
        {
            // $order['name'] is the name of the order column as sent by the JS
            if ($order['name'] != '')
            {
                $orderColumn = null;
            
                switch($order['name'])
                {
                    case 'name':
                    {
                        $orderColumn = 'town.name';
                        break;
                    }
                    case 'postalCode':
                    {
                        $orderColumn = 'town.postalCode';
                        break;
                    }
                    case 'department':
                    {
                        $orderColumn = 'department.name';
                        break;
                    }
                    case 'region':
                    {
                        $orderColumn = 'region.name';
                        break;
                    }
                }
        
                if ($orderColumn !== null)
                {
                    $query->orderBy($orderColumn, $order['dir']);
                }
            }
        }
        
        // Execute
        $results = $query->getQuery()->getResult();
        $countResult = $countQuery->getQuery()->getSingleScalarResult();
        
        return array(
            "results" 		=> $results,
            "countResult"	=> $countResult
        );
    }
}

Besides the getRequiredDTData function, the repository implements a second method count which simply returns the total number of lines in the table (the total number of Town objects). This is needed for pagination purposes.

Now let’s take a closer look at the getRequiredDTData method.

• Lines 21-22: I maintain two identical queries, one is the main query and the second one is used to count the total number of found objects. Again, this is needed for pagination. The main query will always return a number of objects smaller or equal to the number of elements allowed per page (line 91 : $query->setFirstResult($start)->setMaxResults($length); ). The count query will return the total number of objects corresponding to the given criteria (notice that we only limit the number of results for the main query at line 91). For example, if  we search for all the towns in the ‘Hautes-Alpes’ department and we require a maximum of 10 entries per page, the main query will return the first 10 towns matching the given department, and the count query will return 189, which is the total number of towns located in the ‘Hautes-Alpes’ department.

• Lines 24-32: The necessary join operations.
• Lines 34-46: Custom conditions are handled here.
• Lines 49-88: After joining the necessary tables, we need to add conditions corresponding to the individual field search. For each column, we add a condition based on the type of data we are looking for.
• Line 91: We have all of the data, it’s time to restrict it and only return the necessary number of entries for a single page. Notice that, as explained above, this restriction is only applied to the main query and not the count query.
• Lines 94-130: The last operation is sorting. Since sorting on a column automatically loads the first page, we only sort the page entries, to speed the process.  Notice that sorting is applied to the main query only since the count query only returns the total number of entries and not the actual entries.
• Lines 133-139: Return the results back to the Controller.

Part 6: Final Words

I believe that this implementation could be further improved. I have recently stumbled upon a discussion regarding custom search on date fields. This is a tricky matter since dates are usually stored in the database as ‘date’ or ‘datetime’, but they can be displayed to the user using other formats. For example, the database entry  ‘2018-05-04 03:14:15’ may be displayed as ‘May, 4 2018’. Therefore the repository method for filtering data should take into account how the data is displayed on the client-side.

I hope this is helpful. And don’t forget to have cookies once in a while