Datatables y Symfony: cargando tablas con cantidades ingentes de datos

Continuando con un post anterior sobre mostrar tablas de datos interactivas, o este otro relacionado sobre Datatables con botones de descarga.. traigo hoy otro code-kata, hecho en Symfony 4 para probar el Datatables más a fondo. En aquel post empecé a testear el Datatables con pocos datos. Datatables es un plugin de jQuery con el que en poco tiempo, listas, ordenas y filtras tablas de datos de forma muy profesional.

Ahora estoy probando cómo funciona sobre un proyecto con ingentes cantidades de datos, en concreto con una tabla con 622,001 registros. Llegado a éste extremo, no puedes cargar el 100% de los datos en el servidor, enviarlos al navegador, y pintarlos. Si lo haces, el navegador se sobrecargará, no podrá pintar la tabla, o irá muy muy lento.

La solución es ir enviando al navegador los datos poco a poco para no colapsarlo. Así la experiencia de usuario será buena. Es decir, se hará una primera carga de la página en curso, y con cada página, filtrado, ordenación.. se hará una petición al servidor. El servidor devolverá los datos necesarios para pintarlos en navegador sin recargar la página completa, mediante AJAX.

Creando el proyecto de pruebas

Doy por sentado que ya tenemos instaladas las herramientas de desarrollo en nuestro PC. Así que vamos a nuestra carpeta desastre en donde creamos los proyectos, y escribimos desde línea de comandos:

composer create-project symfony/skeleton testing-datatables
cd testing-datatables
composer require --dev server symfony/maker-bundle
composer require twig annotations doctrine

Ahora esperamos un poco, si todo ha ido bien veremos ya instalado el proyecto con los primeros componentes. Ahora, podemos arrancar el servidor local de desarrollo:

php bin/console server:start

..si todo ha ido bien verás en local tu recién proyecto funcionando en http://localhost:8000/

Cargando una cantidad ingente de datos

Lo siguiente para estas pruebas es generar una entidad de Doctrine para guardar en la BD muchos datos. Primero creamos una entidad ejecutando:

php bin/console make:entity

Siguiendo las instrucciones en línea de comandos generamos la entidad llamada SampleData y le ponemos 10 campos de tipo string, por ejemplo. Lo siguiente es generar un comando para guardar muchos datos de prueba en la BD:

php bin/console make:command

Le podemos llamar al comando app:generate-sample-data, y lo creamos con el siguiente código, por ejemplo:

<?php

namespace App\Command;

use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;
use Symfony\Component\Console\Style\SymfonyStyle;
use Symfony\Component\DependencyInjection\ContainerInterface;
use App\Entity\SampleData;

class GenerateSampleDataCommand extends Command
{
    protected static $defaultName = 'app:generate-sample-data';
    private $container;

    public function __construct(ContainerInterface $container)
    {
        parent::__construct();
        $this->container = $container;
    }

    protected function configure()
    {
        $this
            ->setDescription('Command for generating sample data.')
        ;
    }

    protected function execute(InputInterface $input, OutputInterface $output)
    {
        $io = new SymfonyStyle($input, $output);

        $entityManager = $this->container->get('doctrine')->getManager();

        for ($i = 0; $i < 1000000; ++$i) {
            $sampleData = new SampleData();

            $sampleData->setcol1('Datos '.$i.' 1');
            $sampleData->setcol2('Datos '.$i.' 2');
            $sampleData->setcol3('Datos '.$i.' 3');
            $sampleData->setcol4('Datos '.$i.' 4');
            $sampleData->setcol5('Datos '.$i.' 5');
            $sampleData->setcol6('Datos '.$i.' 6');
            $sampleData->setcol7('Datos '.$i.' 7');
            $sampleData->setcol8('Datos '.$i.' 8');
            $sampleData->setcol9('Datos '.$i.' 9');
            $sampleData->setcol10('Datos '.$i.' 10');

            $entityManager->persist($sampleData);
            
            // Sólo hacemos flush a la BD cada 1000 filas
            if (0 == $i % 1000) {
                $entityManager->flush();
            }
        }

        $io->success('All sample data generated!');
    }
}

Éste código de arriba lo único que hace es inyectar en la BD, en la tabla sample_data que corresponde a la entidad SampleData, muchos, muchos datos. En concreto hasta 1 000 000 de líneas si lo dejas terminar.

Lo siguiente es generar la BD para después lanzar el comando de arriba. Para esto tengo que configurar el fichero .env local con los datos de conexión a la BD:

DATABASE_URL=mysql://root@127.0.0.1:3306/testing-datatables

Generando la BD y lanzando el comando:

php bin/console doctrine:database:create
php bin/console doctrine:schema:create
php bin/console app:generate-sample-data

Si todo ha ido bien, un café y algo más después ya tenemos los datos. Yo lo dejaría un par de horas cargando. Así veremos bien qué tal se comporta el Datatables con cantidades ingentes de datos.

Lo siguiente es preparar el frontend con todo el HTML, CSS y Javascript

Para esto que creamos un controlador ejecutando:

php bin/console make:controller

Le he llamado DefaultController. Esto me ha creado el fichero templates/default/index.html.twig en donde voy a cargar todo lo relacionado con Datatables:

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

{% block title %}Hello DefaultController!{% endblock %}

{% block body %}

<div class="table-responsive" id="mydatatable-container">
    <table class="records_list table table-striped table-bordered table-hover w-100" id="mydatatable">
        <thead>
            <tr>
                <th>Column 1</th>
                <th>Column 2</th>
                <th>Column 3</th>
                <th>Column 4</th>
                <th>Column 5</th>
                <th>Column 6</th>
                <th>Column 7</th>
                <th>Column 8</th>
                <th>Column 9</th>
                <th>Column 10</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>Filter..</th>
                <th>Filter..</th>
                <th>Filter..</th>
                <th>Filter..</th>
                <th>Filter..</th>
                <th>Filter..</th>
                <th>Filter..</th>
                <th>Filter..</th>
                <th>Filter..</th>
                <th>Filter..</th>
            </tr>
        </tfoot>
    </table>
</div>

<style>
#mydatatable tfoot input{
    width: 100% !important;
}
#mydatatable tfoot {
    display: table-header-group !important;
}
</style>

<script type="text/javascript">
$(document).ready(function() {
    $('#mydatatable tfoot th').each( function () {
        var title = $(this).text();
        $(this).html( '<input type="text" placeholder="Filtrar.." />' );
    } );

    var table = $('#mydatatable').DataTable({
        dom: 'B<"float-left"i><"float-right"f>t<"float-left"l><"float-right"p><"clearfix">',
        responsive: false,
        language: {
            "url": "https://cdn.datatables.net/plug-ins/1.10.19/i18n/Spanish.json"
        },
        order: [[ 0, "desc" ]],
        processing: true,
        serverSide: true,
        ajax: "server-processing.php",
        columnDefs: [
            { "name": "col1", "targets": 0 },
            { "name": "col2", "targets": 1 },
            { "name": "col3", "targets": 2 },
            { "name": "col4", "targets": 3 },
            { "name": "col5", "targets": 4 },
            { "name": "col6", "targets": 5 },
            { "name": "col7", "targets": 6 },
            { "name": "col8", "targets": 7 },
            { "name": "col9", "targets": 8 },
            { "name": "col10", "targets": 9 },
        ],
        "initComplete": function () {
            this.api().columns().every( function () {
                var that = this;

                $( 'input', this.footer() ).on( 'keyup change', function () {
                    if ( that.search() !== this.value ) {
                        that
                            .search( this.value )
                            .draw();
                        }
                });
            })
        },
        searchDelay: 1000
    });
});
</script>
{% endblock %}

Ésta plantilla de Twig extiende de la plantilla templates/base.html.twig, en la cual he puesto el siguiente código:

<!DOCTYPE html>
<html lang="es">
    <head>
        <meta charset="UTF-8">
        <title>{% block title %}Welcome!{% endblock %}</title>
        {% block stylesheets %}{% endblock %}

        <!-- Required meta tags -->
        <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

        <!-- Bootstrap CSS -->
        <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.0/css/bootstrap.min.css">
        <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"></script>
        <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.0/js/bootstrap.min.js"></script>

        <!-- Datatables -->
        <link rel="stylesheet" href="https://cdn.datatables.net/v/bs4/dt-1.10.18/datatables.min.css">
        <script src="https://cdn.datatables.net/v/bs4/dt-1.10.18/datatables.min.js"></script>
    </head>
    <body class="container-fluid p-5">
        {% block body %}{% endblock %}
        {% block javascripts %}{% endblock %}
    </body>
</html>

Hasta aquí ya tenemos toda la parte de frontend. Esto por si sólo no pintará nada en la tabla, ningún dato. Fíjate en la creación de la tabla, en el Javascript. Tenemos una línea del Datatable en donde le decimos que va a cargar los datos mediante AJAX desde el servidor. Esto es el kit de la cuestión:

ajax: "server-processing.php"

Este sistema de procesamiento que tiene el Datatables es lo que hará posible el mostrar en una tabla una cantidad ingente de datos. Esto es lo que se llama en la documentación de Datatables, el procesamiento en el lado del servidor. Y se hace con la llamada AJAX al fichero server-processing.php.

Este fichero server-processing.php no existe realmente en la parte de servidor, sino que en Symfony lo que hacemos es recibir esta petición al fichero para devolver la respuesta de los datos que tocan en JSON. Ahora viene la parte de backend..

Finalmente la parte de backend en PHP con Symfony

Ahora bien, para comprender cómo funciona Datatables, hay que entender que primero se carga la tabla vacía en el navegador. Luego mediante Javascript, Datatables se encarga de hacer las peticiones AJAX al servidor. Un caso que explica todo es el de la imagen siguiente:

La URL de la petición AJAX que se hace al servidor, es el kit de la cuestión. El código Javascript te procesa cuando escribes en las cajas de búsqueda, y va haciendo peticiones al servidor para pintarte los datos resultantes en la tabla. Estas peticiones son las que tenemos que procesar en el servidor.

En la URL de la imagen de ejemplo vemos los parámetros tenemos:

 draw=27
 columns[0][data]=0
 columns[0][name]=col1
 columns[0][searchable]=true
 columns[0][orderable]=true
 columns[0][search][value]=
 columns[0][search][regex]=false
 columns[1][data]=1
 columns[1][name]=col2
 columns[1][searchable]=true
 columns[1][orderable]=true
 columns[1][search][value]=
 columns[1][search][regex]=false
 columns[2][data]=2
 columns[2][name]=col3
 columns[2][searchable]=true
 columns[2][orderable]=true
 columns[2][search][value]=
 columns[2][search][regex]=false
 columns[3][data]=3
 columns[3][name]=col4
 columns[3][searchable]=true
 columns[3][orderable]=true
 columns[3][search][value]=Datos%201
 columns[3][search][regex]=false
 columns[4][data]=4
 columns[4][name]=col5
 columns[4][searchable]=true
 columns[4][orderable]=true
 columns[4][search][value]=4445
 columns[4][search][regex]=false
 columns[5][data]=5
 columns[5][name]=col6
 columns[5][searchable]=true
 columns[5][orderable]=true
 columns[5][search][value]=
 columns[5][search][regex]=false
 columns[6][data]=6
 columns[6][name]=col7
 columns[6][searchable]=true
 columns[6][orderable]=true
 columns[6][search][value]=
 columns[6][search][regex]=false
 columns[7][data]=7
 columns[7][name]=col8
 columns[7][searchable]=true
 columns[7][orderable]=true
 columns[7][search][value]=
 columns[7][search][regex]=false
 columns[8][data]=8
 columns[8][name]=col9
 columns[8][searchable]=true
 columns[8][orderable]=true
 columns[8][search][value]=
 columns[8][search][regex]=false
 columns[9][data]=9
 columns[9][name]=col10
 columns[9][searchable]=true
 columns[9][orderable]=true
 columns[9][search][value]=
 columns[9][search][regex]=false
 order[0][column]=4
 order[0][dir]=desc
 start=0
 length=10
 search[value]=44
 search[regex]=false
 _=1550410673379

He marcado en negrita los items de la petición que tenemos que trabajar en servidor. Tenemos dos búsquedas por columnas, una búsqueda general, un ordenado por la columna 4 (la col5 realmente por empezar por 0), empieza en página 0, 10 items por página. El draw=27 simplemente es que llevo 27 pruebas mientras que estoy escribiendo este post.

Entonces, trabajando la parte de servidor en el controlador, en Symfony es sencillo. Jugando y jugando, que podemos llegar a algo como lo siguiente:

<?php

namespace App\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\Routing\Annotation\Route;

class DefaultController extends AbstractController
{
    /**
     * @Route("/", name="default")
     */
    public function index()
    {
        $entityManager = $this->getDoctrine()->getManager();

        $data['recordsTotal'] = $entityManager
            ->createQuery('SELECT count(sd) FROM App:SampleData sd')
            ->getSingleScalarResult();

        return $this->render('default/index.html.twig', [
            'data' => $data,
        ]);
    }

    /**
     * @Route("/server-processing.php", name="server_processing")
     */
    public function serverProcessing()
    {
        $entityManager = $this->getDoctrine()->getManager();

        /*var_dump($_GET['search']['value']);
        exit;*/

        $dql = 'SELECT sd FROM App:SampleData sd';
        $dqlCountFiltered = 'SELECT count(sd) FROM App:SampleData sd';

        $sqlFilter = '';

        if (!empty($_GET['search']['value'])) {
            $strMainSearch = $_GET['search']['value'];

            $sqlFilter .= " (sd.col1 LIKE '%".$strMainSearch."%' OR "
                ."sd.col2 LIKE '%".$strMainSearch."%' OR "
                ."sd.col3 LIKE '%".$strMainSearch."%' OR "
                ."sd.col4 LIKE '%".$strMainSearch."%' OR "
                ."sd.col5 LIKE '%".$strMainSearch."%' OR "
                ."sd.col6 LIKE '%".$strMainSearch."%' OR "
                ."sd.col7 LIKE '%".$strMainSearch."%' OR "
                ."sd.col8 LIKE '%".$strMainSearch."%' OR "
                ."sd.col9 LIKE '%".$strMainSearch."%' OR "
                ."sd.col10 LIKE '%".$strMainSearch."%') ";
        }

        // Filter columns with AND restriction
        $strColSearch = '';
        foreach ($_GET['columns'] as $column) {
            if (!empty($column['search']['value'])) {
                if (!empty($strColSearch)) {
                    $strColSearch .= ' AND ';
                }
                $strColSearch .= ' sd.'.$column['name']." LIKE '%".$column['search']['value']."%'";
            }
        }
        if (!empty($sqlFilter)) {
            $sqlFilter .= ' AND ('.$strColSearch.')';
        } else {
            $sqlFilter .= $strColSearch;
        }

        if (!empty($sqlFilter)) {
            $dql .= ' WHERE'.$sqlFilter;
            $dqlCountFiltered .= ' WHERE'.$sqlFilter;
            /*var_dump($dql);
            var_dump($dqlCountFiltered);
            exit;*/
        }

        //var_dump($dql); exit;

        $items = $entityManager
            ->createQuery($dql)
            ->setFirstResult($_GET['start'])
            ->setMaxResults($_GET['length'])
            ->getResult();

        $data = [];
        foreach ($items as $key => $value) {
            $data[] = [
                $value->getCol1(),
                $value->getCol2(),
                $value->getCol3(),
                $value->getCol4(),
                $value->getCol5(),
                $value->getCol6(),
                $value->getCol7(),
                $value->getCol8(),
                $value->getCol9(),
                $value->getCol10(),
            ];
        }

        $recordsTotal = $entityManager
            ->createQuery('SELECT count(sd) FROM App:SampleData sd')
            ->getSingleScalarResult();

        $recordsFiltered = $entityManager
            ->createQuery($dqlCountFiltered)
            ->getSingleScalarResult();

        return $this->json([
            'draw' => 0,
            'recordsTotal' => $recordsTotal,
            'recordsFiltered' => $recordsFiltered,
            'data' => $data,
            'dql' => $dql,
            'dqlCountFiltered' => $dqlCountFiltered,
        ]);
    }
}

Este es el kit de la cuestión, tenemos que filtrar y ordenar los datos en servidor y devolverlos al cliente al recibir las peticiones AJAX en:

http://localhost:8000/server-processing.php

Terminando: depurando, bibliografía y códigos en Github funcionales

Todos estos códigos fuentes que he usado mientras que probaba y escribía el post los he subido a Github en:

https://github.com/jaimenj/symfony-testing-datatables

Sólo me queda remitirme a páginas de los proyectos utilizados, a su documentación:

• https://symfony.com/doc/current/index.html
• https://api.jquery.com/
• https://getbootstrap.com/docs/4.3/getting-started/introduction/
• https://datatables.net/manual/