DataTables for Symfony 6.0

DataTables for Symfony 6.0

schwaluckschwaluck Posts: 103Questions: 27Answers: 1

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 :smile:

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']);
                }
            }
        }
    }
}

Replies

  • schwaluckschwaluck Posts: 103Questions: 27Answers: 1

    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
            );
        }
    }
    
  • schwaluckschwaluck Posts: 103Questions: 27Answers: 1

    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;        
        }   
    }
    
  • schwaluckschwaluck Posts: 103Questions: 27Answers: 1

    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 %} 
    
  • schwaluckschwaluck Posts: 103Questions: 27Answers: 1

    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"
          },                       
      } );    
    });
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • paskuale75paskuale75 Posts: 5Questions: 0Answers: 0
    edited July 2023

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

    Thanks

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

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • paskuale75paskuale75 Posts: 5Questions: 0Answers: 0

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

  • svierkantsvierkant Posts: 33Questions: 2Answers: 0
    edited February 19
Sign In or Register to comment.