How to display large table in twig with symfony?

Question

I have an entity with 3,000 records, and when I render my index.html.twig that information takes about 35 seconds to display the datatable.

My question here is, how can I perform the rendering of the table?

I have looked around but no luck!

Please help me.

Thanks,

Controller

public function indexAction()
{
    if ($this->getUser() == NULL){
        return $this->redirect($this->generateUrl('login_route'));
    }

    $em = $this->getDoctrine()->getManager();

    $session = $this->get('session');
    $id_empresaa = $session->get('idempresa');
    $session->set('viewProd', 1);

    $entities = $em->getRepository('NivalInventarioBundle:InProducto')->findBy(array(
        'idEmpresaa' => $id_empresaa
    ));

    return $this->render('NivalInventarioBundle:InProducto:index.html.twig', array(
        'entities' => $entities,
    ));
}

Twig:

    {% extends 'NivalInventarioBundle:Default:index.html.twig' %}
{% block content %}
    {% block inventario_menu %}
        {{ parent() }}
    {% endblock %}
    <h3>Productos</h3>
    <div class="row" style = margin-bottom:55px;">
        <div class="col-md-12">
            <table id="ftable" class="table table-condensed table-striped table-bordered" cellspacing="0" width="100%">
                <thead>
                <tr>
                    <th>Código</th>
                    <th>Nombre</th>
                    <th>Unidad</th>
                    <th>Costo</th>
                    <th>Sub-Linea</th>
                    <th>Linea</th>
                    <th>Invent.</th>
                    <th>Factura</th>
                    <th>Activo</th>
                    <th width="60px">Opción</th>
                </tr>
                </thead>
                <tbody>
                {% for entity in entities %}
                    <tr>
                        <td>{{ entity.idProducto }}</td>
                        <td>{{ entity.nombre }}</td>
                        <td>{{ entity.unidadMedida.nombre }}</td>
                        <td class="text-right">{{ entity.costoPromedio|number_format(4) }}</td>
                        <td>{{ entity.subLinea.nombre }}</td>
                        <td>{{ entity.subLinea.linea.nombre }}</td>
                        <td>
                            {% if entity.inventariable == 0 %}
                                No
                            {% elseif entity.inventariable == 1 %}
                                Sí
                            {% endif %}
                        </td>
                        <td>
                            {% if entity.facturable == 0 %}
                                No
                            {% elseif entity.facturable == 1 %}
                                Sí
                            {% endif %}
                        </td>
                        <td>
                            {% if entity.activo == 0 %}
                                No
                            {% elseif entity.activo == 1 %}
                                Sí
                            {% endif %}
                        </td>
                        <td class = "actions">
                            <a href="{{ path('inproducto_show', { 'id': entity.idProducto }) }}"
                               class = "btn btn-sm btn-info glyphicon glyphicon-search" data-toggle="tooltip" title="Ver"></a>
                            {% if app.user.nivel > 60 %}
                                <a href="{{ path('inproducto_edit', { 'id': entity.idProducto }) }}"
                                   class = "btn btn-sm btn-primary glyphicon glyphicon-edit" data-toggle="tooltip" title="Editar"></a>
                            {% endif %}
                        </td>
                    </tr>
                {% endfor %}
                </tbody>
            </table>
        </div>
        {% if app.user.nivel > 30 %}
            <div class="col-md-12">
                <a href="{{ path('inproducto_new') }}"
                   class = "btn btn-success glyphicon glyphicon-plus" data-toggle="tooltip" title="Nuevo"></a>
            </div>
        {% endif %}
    </div>
{% endblock %}

Javascript:

<script>
    $(document).ready(function() {
        $('#ftable').DataTable({
            stateSave: true,
            language: {
                "emptyTable":     "No hay datos disponibles en la tabla",
                "info":           "Mostrando _START_ hasta _END_ de _TOTAL_ registros",
                "infoEmpty":      "Mostrando 0 hasta 0 de 0 registros",
                "lengthMenu":     "Mostrar _MENU_ registros",
                "search":         "Buscar:",
                "loadingRecords": "Cargando...",
                "processing":     "Procesando...",
                "paginate": {
                    "first":      "Primero",
                    "last":       "Ultimo",
                    "next":       "Siguiente",
                    "previous":   "Anterior"
                },
                "infoFiltered":   "(filtrados de _MAX_ registros)"
            }
        });
        $('.selectpicker').selectpicker({
            size: 8
        });
        $('.datepicker').datepicker({
            format: 'dd-mm-yyyy',
            autoclose: true
        })
    } );
</script>

Answer 1

Loading an HTML table with 3000 records is heavy, and doing that with a full framework, ORM and templating engine is even heavier.

The best approach for this situation is to load the records dynamically on the table, querying only for what you're displaying and doing a real pagination. You can do it in two ways:

Option 1

You can follow the DataTables documentation on how to do it and then implement the JS calls, the controller actions to grab the data and the HTML templates. That's not hard if you know your way around Symfony, but it can be a lot of work.

Option 2

Use DatatablesBundle and let it handle everything for you. It's pretty straightforward and they have a good documentation and even an example repository.

Answer 2

Here is sample code (working) that I use with Paginator:

$pagination = $paginator->paginate(
        $query, /* query NOT result */
        $request->query->getInt('page', 1)/*page number*/,
        10/*limit per page*/
);

The last parameter 10 sets the number of items per page. I believe this will be a good solution for you (I mean using paginator).

Answer 3

Despite of table with 3000 records is heavy (as mentioned before), 35 seconds is extremely much for the table. Reasonable time should be less than 1-2 second because Twig is pretty fast engine.

Found <td>{{ entity.unidadMedida.nombre }}</td> in your template. Probably you haven't defined EAGER fetch in your entity, so it calls SQL query time when it request unidadMedida field.

Open your Symfony profiler page (in dev mode it's usually /_profiler/) and check how many Doctrine queries you have. Of course if there are thousand of database requests loading time will be inadequate.

Answer 4

I implemented KnpPaginatorBundle, it works very good, I recomended it: https://github.com/KnpLabs/KnpPaginatorBundle