DataTables for Symfony 6.0

schwaluck

Hello all,

I am currently working on a project for which I am using Symfony 6.0 as the PHP framework. Now I wanted to use DataTables with server side processing, but still make use of the Doctrine engine. Therefore I transferred the core aspects like loading entries, filtering, ordering, etc. into a service that should be reusable for each table.

My starting point for the code was the following post: http://growingcookies.com/datatables-server-side-processing-in-symfony/

Below you can find the code for the service. The code for the controller, repository, view and js will be in the comments.^^

Remark: I have not yet tested the functionality of the function "addJoins" for performing joins. Nevertheless, you can find the current code for this in the service and repository.

I thought I'd share my result with you and maybe it will help one or the other of you. If you have any suggestions for improvement or comments, I'm curious

Best regards
Schwaluck

Service

// src/Service/DataTableService.php
namespace App\Service;

class DataTableService {

    public function getData($request, $repository, $furtherConditions=""): string {
        
        // Get the parameters from the Ajax Call
        if ($request->getMethod() == 'POST') {
            $parameters = $request->request->all();
            $draw = $parameters['draw'];
            $start = $parameters['start'];
            $length = $parameters['length'];
            $search = $parameters['search'];
            $orders = $parameters['order'];
            $columns = $parameters['columns'];
        }
        else
            die;
        
        //Order the Entries for the table
        foreach ($orders as $key => $order){
            $orders[$key]['name'] = $columns[$order['column']]['name'];
        }

        // Get results from the Repository
        $results = $repository->getTableData($start, $length, $orders, $search, $columns, $furtherConditions = null);
        $objects = $results["results"];
        
        // Get total number of objects
        $total_objects_count =  $repository->countObjects();
        
        // 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 => $object) {
            $response .= '["';
            
            $j = 0; 
            $nbColumn = count($columns);
            foreach ($columns as $key => $column) {
                // In all cases where something does not exist or went wrong, return -
                $responseTemp = "-";
                
                $functionName = 'get'.$column['name'];

                if ($functionName != "get")
                    $responseTemp = $object->$functionName();
               
               // 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 .= ']}';

        return $response;
    }
        
    public function countObjectsInTable($countQuery, $table) {
        return $countQuery->select("COUNT($table)");
    }

    public function setLength($countQuery, $length) {
        $countResult = $countQuery->getQuery()->getSingleScalarResult();
        if ($length == -1) {
            $length = $countResult;
        }
        return $countResult;
    }

    public function addJoins($query, $countQuery, $joins) {
        for($i = 0; $i < count($joins); $i++) {
            $query->join($joins[$i].$joins[$i][1], $joins[$i][2]);
            $countQuery->join($joins[$i].$joins[$i][1], $joins[$i][2]);              
        }  
    }

    public function addConditions($query, $conditions) {
        if ($conditions != null) {
            // Add condition
            $query->where($conditions);
            $countQuery->where($conditions);
        }
    }

    public function performSearch($query, $countQuery,$table, $columns, $search) {
    
        $searchItem = $search['value'];
        $searchQuery = "";
        
        for($i = 0; $i < count($columns); $i++) {

            if($i < count($columns)-1) {
                if($columns[$i]['searchable'] == "true" && $columns[$i]['name'] != "")
                    $searchQuery .= $table.'.'.$columns[$i]['name'].' LIKE '.'\'%'.$searchItem.'%\''.' OR ';
            }
            else {
                if($columns[$i]['searchable'] == "true" && $columns[$i]['name'] != "")
                    $searchQuery .= $table.'.'.$columns[$i]['name'].' LIKE '.'\'%'.$searchItem.'%\'';
            }

        }
        $query->andWhere($searchQuery);
        $countQuery->andWhere($searchQuery);
    }    
    
    public function addLimits($query, $start, $length) {
        return $query->setFirstResult($start)->setMaxResults($length);
    }
    
    public function performOrdering($query, $orders, $table) {
        foreach ($orders as $key => $order) {
            if ($order['name'] != '') {
                
                $orderColumn = null;
                
                $orderColumn = "{$table}.{$order['name']}";
                    
                if ($orderColumn !== null) {
                    $query->orderBy($orderColumn, $order['dir']);
                }
            }
        }
    }
}

schwaluck

Repository

namespace App\Repository;

use App\Entity\Example;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use App\Service\DataTableService;

/**
 * @method Example|null find($id, $lockMode = null, $lockVersion = null)
 * @method Example|null findOneBy(array $criteria, array $orderBy = null)
 * @method Example[]    findAll()
 * @method Example[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class ExampleRepository extends ServiceEntityRepository {

    private $service;

    public function __construct(ManagerRegistry $registry, DataTableService $service) {
        parent::__construct($registry, Example::class);
        $this->service = $service;
    }

    // Get the total number of entries
    public function countObjects() {
        return $this
            ->createQueryBuilder('object')
            ->select("count(object.id)")
            ->getQuery()
            ->getSingleScalarResult();
    }

    public function getTableData($start, $length, $orders, $search, $columns, $conditions) {

        /***********************************************************
                            Define Variables
        ************************************************************/
        $table = 'example';

        /***********************************************************
                            Create queries
        ************************************************************/
        // Normal Query
        $query = $this->createQueryBuilder($table);
        
        // Create Count Query
        $countQuery = $this->createQueryBuilder($table);

        $this->service->countObjectsInTable($countQuery,$table);

        /***********************************************************
                            Exception: DataTable show All
        ************************************************************/
        //Execute Count query already here to update length if it is a negative value due to pageLength = All
        $this->service->setLength($countQuery, $length);


        /***********************************************************
                            Create Joins
        ************************************************************/
        //EXAMPLE ARRAY TO PASS
        //  $joins = array (
        //      array("table0","field0","alias0"),
        //      array("table1","field1","alias1")
        //  );

        //$this->service->addJoins($query, $joins);
        
        /***********************************************************
                            Add specific WHERE Clauses
        ************************************************************/  
        $this->service->addConditions($query, $conditions);
       
        /***********************************************************
                            Perform search
        ************************************************************/
        if ($search['value'] != "") {
            $this->service->performSearch($query,$countQuery,$table, $columns, $search);
        }
        
        /***********************************************************
                            Add limits
        ************************************************************/  
        $this->service->addLimits($query, $start, $length);
        
        /***********************************************************
                            Perform ordering
        ************************************************************/
        $this->service->performOrdering($query, $orders, $table);

        /***********************************************************
                            Execute Query
        ************************************************************/
        $results = $query->getQuery()->getResult();
        $countResult = $countQuery->getQuery()->getSingleScalarResult();        
        
        /***********************************************************
                            Return Results
        ************************************************************/
        return array(
            "results"       => $results,
            "countResult"   => $countResult
        );
    }
}

schwaluck

Controller

namespace App\Controller;

use App\Entity\Example;
use App\Repository\ExampleRepository;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Component\HttpFoundation\JsonResponse;
use App\Service\DataTableService;

#[Route('/example')]
class ExampleController extends AbstractController
{

   ...other functions to load the view etc.   

    #[Route('/ajax', name: 'example_ajax', methods: ['GET', 'POST'])]
    public function table(DataTableService $service, Request $request, ExampleRepository $exampleRepository) {

        $response = $service->getData($request, $exampleRepository);

        $returnResponse = new JsonResponse();
        $returnResponse->setJson($response);
        
        return $returnResponse;        
    }   
}

schwaluck

View

{% block page_content %}
    <div class="container_table" style="overflow: hidden;">
        <table id="example" class="display nowrap" style="width: 100%; border-collapse: collapse;">
                <thead>
                    <tr>
                        <th></th>                     
                        <th></th>                                              
                        <th>ID</th>
                        <th>Title</th>
                        <th>Description</th>
                        <th>Stock</th>                                  
                    </tr>
                </thead>
                <tfoot>
                    <tr>
                        <th></th>                       
                        <th></th>                                                                           
                        <th>ID</th>
                        <th>Title</th>
                        <th>Description</th>
                        <th>Stock</th>                                                         
                    </tr>
                </tfoot>
        </table>
    </div>      
{% endblock %}
{% block javascripts %}
    {{ parent() }}
    <script>
    var url_from_twig_ajax = '{{ path("example_ajax") }}';
    </script>
    {{ encore_entry_script_tags('example') }}            
{% endblock %} 

schwaluck

JS

//Imports
import TableConfig from './components/DataTables/TableConfig';

//Document Ready Function
$(function() {
  var table = $('#example').DataTable( {
      responsive: TableConfig.responsive,
      select: TableConfig.select, 
      lengthMenu: TableConfig.lengthMenu,                      
      paging: TableConfig.paging,
      info: TableConfig.info,
      searching: TableConfig.searching,
      language: TableConfig.language,
      processing: TableConfig.searching,
      serverSide: TableConfig.serverSide,
      order: TableConfig.order,
      columnDefs: [ 
          TableConfig.controlSelectbox,
          TableConfig.controlResponsive,              
          // These are the column name variables that will be sent to the server
          { "name": "id",   "targets": 2 },
          { "name": "title",   "targets": 3 },
          { "name": "description",  "targets": 4 },
          { "name": "stock",  "targets": 5 },
      ], 

      // Ajax call
      ajax: {
          url: url_from_twig_ajax,
          type: "POST",
          dataType: "json"
      },                       
  } );    
});

allan

Hi Schwaluck,

This is awesome. Thanks for sharing it! I don't use Symfony myself, but I expect those that do along with server-side processing for DataTables will greatly appreciate this!

Allan

paskuale75

thanks for sharing, where is TableConfig file ? What dt version is it ?

Thanks

p.s.
at line 104 : $countQuery (undefined variable)

colin

It looks like it's an object that just defines the DataTables config. This allows the code to be generic, and the project just has a unique TableConfig setup for how they want the table to be initialised.

Colin

paskuale75

thanks for reply @colin in the meanwhile I've found a cleaner solution, I think, the omines bundle

svierkant

You can use https://github.com/stwe/DatatablesBundle .