DataTables for Symfony 6.0
DataTables for Symfony 6.0
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']);
}
}
}
}
}
Replies
Repository
Controller
View
JS
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
Thanks
p.s.
at line 104 : $countQuery (undefined variable)
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
thanks for reply @colin in the meanwhile I've found a cleaner solution, I think, the omines bundle
You can use https://github.com/stwe/DatatablesBundle .