Symfony DataTables-to-mysql query builder

README.md

My symfony project needed to load a DataTables table from an ajax call. I found a few implementations on the interwebs but they didn't work for me:

• the table contained data joined from multiple tables
• the various query builders relied on doctrine DQL, which is sometimes tough to follow

After some brief hacking, I came up with a simple data table query sql query builder, which lets me get the data in a reasonably concise way that I could follow:

Helper class, DataTablesMySqlQuery

This class takes a base query in plain sql (provided by a Repository), and adds the necessary filtering and sorting as passed by the datatables ajax call. The file is given below, and a simple set of unit tests to show how the class modifies the base query according to the parameters.

Twig template

The twig template (example below) sets up datatables as expected, and passes the SQL column names in the columnDefs

The controller

The controller method is quite brief:

    #[Route('/datatables', name: 'app_term_datatables', methods: ['POST'])]
    public function datatables_source(Request $request, TermRepository $repo): JsonResponse
    {
        $parameters = $request->request->all();
        $data = $repo->getDataTablesList($parameters);
        $data["draw"] = $parameters['draw'];
        return $this->json($data);
    }

The repository

The repo has a method that contains the "base query" that is used by DataTablesMySqlQuery:

    /** Returns data for ajax paging. */
    public function getDataTablesList($parameters) {

        $base_sql = "SELECT
        fieldA, fieldB, fieldC, etc etc etc ... etc.
        ";
        $conn = $this->getEntityManager()->getConnection();
        return DataTablesMySqlQuery::getData($base_sql, $parameters, $conn);
    }

The base_sql can be as complicated as you need. The DataTablesMySqlQuery uses this to create and execute sql that returns:

• the total count of objects
• the count of filtered objects
• the subset of records that should be rendered

Files included in the gist:

• DataTablesMySqlQuery.php - the magic file
• DataTablesMySqlQuery_Test.php - some phpunit tests to show how queries are built
• index.html.twig - a twig file from my project showing the datatables config, just for kicks.

Cheers! jz

DataTablesMySqlQuery.php

<?php

namespace App\Repository;

/**
 * Utility repo for Mysql backend for DataTables ajax calls.
 */
class DataTablesMySqlQuery
{

    /**
     * Returns array of sql to be executed.
     * public for unit testing.
     */
    public static function getSql($base_sql, $parameters) {

        $start = $parameters['start'];
        $length = $parameters['length'];
        $search = $parameters['search'];
        $orders = $parameters['order'];
        $columns = $parameters['columns'];
        // dump($columns);
        // dump($orders);

        $findColsWith = function($columns, $attr) {
            $cols = array_filter($columns, fn($c) => ($c[$attr] == "true"));
            $cols = array_map(fn($c) => $c['name'], $cols);
            return array_values($cols);
        };

        $named_cols = array_filter($columns, fn($c) => ($c["name"] != ""));
        $select_fields = array_values(
            array_map(fn($c) => $c['name'], $named_cols)
        );
        $orderablecols = $findColsWith($columns, "orderable");
        $orderby = implode(', ', $orderablecols);
        $searchablecols = $findColsWith($columns, "searchable");

        foreach ($orders as $key => $order) {
            $colindex = intval($order['column']);
            $direction = $order['dir'];

            // Apply the sort in for the indicated field, the rest
            // will be sorted ascending.
            $sortfield = $columns[$colindex]['name'];
            $orderby = "ORDER BY {$sortfield} {$direction}, {$orderby}";
        }
        
        $where = '';
        $searchstring = $search['value'];

        if ($searchstring != null)
            $searchstring = trim($searchstring);
        else
            $searchstring = '';

        $searchparts = mb_split("\s+", $searchstring);
        $testlen = function($p) { return mb_strlen($p) > 0; };
        $searchparts = array_filter($searchparts, $testlen);

        $params = [];
        $dosearch = count($searchablecols) > 0 && count($searchparts) > 0;
        
        if ($dosearch) {
            // Note that while "LIKE CONCAT('%', :s{$i}, '%')" loos
            // very odd, it's the only way to get the params to match
            // correctly.  Using '%?%' with an array of [
            // $searchstring ] fails with "invalid parameter number:
            // number of bound variables does not match number of
            // tokens", and using '%:s0%' just doesn't work.

            // If multiple parts, then every part must be contained in
            // at least one field.
            $partwheress = [];
            for ($i = 0; $i < count($searchparts); $i++) {
                $params["s{$i}"] = $searchparts[$i];

                $colwheres = [];
                for ($j = 0; $j < count($searchablecols); $j++) {
                    $cname = $searchablecols[$j];
                    $colwheres[] = "{$cname} LIKE CONCAT('%', :s{$i}, '%')";
                }
                // Part in at least one field.
                $partwheress[] = '(' . implode(' OR ', $colwheres) . ')';
            }
            $where = "WHERE " . implode(' AND ', $partwheress);
        }


        $recordsTotal_sql = "select count(*) from ({$base_sql}) src";
        $recordsFiltered_sql = "select count(*) from ({$base_sql} {$where}) src";
        $select_field_list = implode(', ', $select_fields);
        $data_sql = "SELECT $select_field_list FROM ({$base_sql} {$where} {$orderby} LIMIT $start, $length) src {$orderby}";
        // dump('TOTAL: ' . $recordsTotal_sql);
        // dump("FILTERED:\n\n" . $recordsFiltered_sql);
        // dump("DATA:\n\n" . $data_sql);

        return [
            'recordsTotal' => $recordsTotal_sql,
            'recordsFiltered' => $recordsFiltered_sql,
            'data' => $data_sql,
            'params' => $params
        ];

    }

    /** Returns data for ajax paging. */
    public static function getData($base_sql, $parameters, $conn) {

        $sqla = DataTablesMySqlQuery::getSql($base_sql, $parameters);
        $recordsTotal = $conn->executeQuery($sqla['recordsTotal'])->fetchNumeric()[0];
        $recordsFiltered = $conn->executeQuery($sqla['recordsFiltered'], $sqla['params'])->fetchNumeric()[0];

        $res = $conn->executeQuery($sqla['data'], $sqla['params']);
        $ret = [];
        while (($row = $res->fetchNumeric())) {
            $ret[] = array_values($row);
        }

        $result = [
            'recordsTotal' => $recordsTotal,
            'recordsFiltered' => $recordsFiltered,
            'data' => $ret
        ];
        return $result;
    }
}

DataTablesMySqlQuery_Test.php

<?php declare(strict_types=1);

require_once __DIR__ . '/../../../src/Repository/DataTablesMySqlQuery.php';

use PHPUnit\Framework\TestCase;
use App\Repository\DataTablesMySqlQuery;


final class DataTablesMySqlQuery_Test extends TestCase
{

    public function setUp(): void
    {
        $this->basesql = "select CatID, Color, Food from Cats";
        $this->columns = [
            0 => [
                "data" => "0",
                "name" => "CatID",
                "searchable" => "false",
                "orderable" => "false"
            ],
            1 => [
                "data" => "1",
                "name" => "Color",
                "searchable" => "true",
                "orderable" => "true"
            ],
            2 => [
                "data" => "2",
                "name" => "Food",
                "searchable" => "true",
                "orderable" => "true"
            ]
        ];

        // The $params sent by DataTables is tedious to set up ...
        $this->parameters = [
            "draw" => "1",
            "columns" => $this->columns,
            "order" => [
                0 => [
                    "column" => "1",
                    "dir" => "asc"
                ]
            ],
            "start" => "10",
            "length" => "50",
            "search" => [
                "value" => "",
                "regex" => "false"
            ]
        ];
    }


    public function test_smoke_test()
    {
        $actual = DataTablesMySqlQuery::getSql($this->basesql, $this->parameters);
        $expected = [
            'recordsTotal' => 'select count(*) from (select CatID, Color, Food from Cats) src',
            'recordsFiltered' => 'select count(*) from (select CatID, Color, Food from Cats ) src',
            'data' => 'SELECT CatID, Color, Food FROM (select CatID, Color, Food from Cats  ORDER BY Color asc, Color, Food LIMIT 10, 50) src ORDER BY Color asc, Color, Food',
            'params' => []
        ];
        $this->assertEquals($actual, $expected, 'sql');
    }


    public function test_sorting()
    {
        $this->parameters["order"][0]["column"] = "2";
        $this->parameters["order"][0]["dir"] = "desc";

        $actual = DataTablesMySqlQuery::getSql($this->basesql, $this->parameters);
        $expected = [
            'recordsTotal' => 'select count(*) from (select CatID, Color, Food from Cats) src',
            'recordsFiltered' => 'select count(*) from (select CatID, Color, Food from Cats ) src',
            'data' => 'SELECT CatID, Color, Food FROM (select CatID, Color, Food from Cats  ORDER BY Food desc, Color, Food LIMIT 10, 50) src ORDER BY Food desc, Color, Food',
            'params' => []
        ];
        $this->assertEquals($actual, $expected, 'sql');
    }

    public function test_single_search()
    {
        $this->parameters["search"]["value"] = "XXX";

        $actual = DataTablesMySqlQuery::getSql($this->basesql, $this->parameters);

        $expectedWHERE = "WHERE (Color LIKE CONCAT('%', :s0, '%') OR Food LIKE CONCAT('%', :s0, '%'))";
        $expected = [
            "recordsTotal" => "select count(*) from (select CatID, Color, Food from Cats) src",
            "recordsFiltered" => "select count(*) from (select CatID, Color, Food from Cats $expectedWHERE) src",
            "data" => "SELECT CatID, Color, Food FROM (select CatID, Color, Food from Cats $expectedWHERE ORDER BY Color asc, Color, Food LIMIT 10, 50) src ORDER BY Color asc, Color, Food",
            'params' => [ 's0' => 'XXX' ]
        ];
        $this->assertEquals($actual, $expected, "sql");
    }

    public function test_multiple_search_terms()
    {
        $this->parameters["search"]["value"] = "XXX YYY";

        $actual = DataTablesMySqlQuery::getSql($this->basesql, $this->parameters);

        $expectedWHERE = "WHERE " .
           "(Color LIKE CONCAT('%', :s0, '%') OR Food LIKE CONCAT('%', :s0, '%'))" .
           " AND " .
           "(Color LIKE CONCAT('%', :s1, '%') OR Food LIKE CONCAT('%', :s1, '%'))";
        $expected = [
            "recordsTotal" => "select count(*) from (select CatID, Color, Food from Cats) src",
            "recordsFiltered" => "select count(*) from (select CatID, Color, Food from Cats $expectedWHERE) src",
            "data" => "SELECT CatID, Color, Food FROM (select CatID, Color, Food from Cats $expectedWHERE ORDER BY Color asc, Color, Food LIMIT 10, 50) src ORDER BY Color asc, Color, Food",
            'params' => [ 's0' => 'XXX', 's1' => 'YYY' ]
        ];
        $this->assertEquals($actual, $expected, "sql");
    }


}

index.html.twig

{% extends 'base.html.twig' %}

{% block title %}Term index{% endblock %}

{% block body %}
<h1>Term index</h1>

<table id="termtable" class="table dataTable stripe" style="table-layout:fixed;" >
  <thead>
    <tr>
      <th>Text</th>
      <th>Language</th>
      <th>Tags</th>
      <th>Status</th>
      <th>Actions</th>
    </tr>
  </thead>
  <tfoot>
    <tr>
      <th>Text</th>
      <th>Language</th>
      <th>Tags</th>
      <th>Status</th>
      <th>Actions</th>
    </tr>
  </tfoot>
</table>

<a href="{{ path('app_term_new') }}">Create new</a>

<script>

  let setup_term_datatable = function() {
    var table = $('#termtable').DataTable({
      responsive: true,
      select: true,
      lengthMenu: [ 10, 25, 50, 100 ],
      paging: true,
      info: true,
      searching: true,
      processing: true,
      serverSide: true,
      columnDefs: [
        // "name" is the column name in your main SQL query.
        // The fields in each row in the data ajax response are ordered as they are declared here.
        // All fields default to "orderable" = true, "searchable" = true.
        { "name": "WoText", "targets": 0 },
        { "name": "LgName", "targets": 1 },
        { "name": "TagList", "targets": 2 },
        { "name": "WoStatus", "targets": 3 },
        {
          "name": "WoID",
          "targets": 4,
          "searchable": false,
          "orderable": false,
          "render": function ( data, type, row, meta ) {
            const href = `/term/${row[4]}/edit`;
            const img = '<img src="/icn/document--pencil.png" title="Edit" alt="Edit" />';
            return `<a href=${href}>${img}</a>`;
          }
        },

      ],

      // Ajax call
      ajax: {
        url: '/term/datatables',
        type: "POST",
        dataType: "json"
      },

    });
  } // end setup_term_datatable
  
  $(document).ready(function () { setup_term_datatable(); });


</script>

{% endblock %}

Update: it turns out that the above doesn't work if the base sql has a WHERE in it already. The updated code is at https://github.com/jzohrab/lwt/blob/master/src/Repository/DataTablesMySqlQuery.php. Cheers, z