Weenesta | Blog | Symfony : tabulator.js integration

Hello,

I show you how to make an interactive data table, in a Symfony project, using Tabulator.js.

What is Tabulator.js?

Tabulator.js is a Javascript library for interactively managing datas tables.

Data table creation

For the example, we need to create an entity allowing us to store different data in the database, which we have previously created and configured with Doctrine.

We name it Member, it contain, for the example, the following fields:

Id (auto-increment),
Username (string(50) not null),
Firstname (string(255) not null),
Lastname (string(255) not null),
Age (integer null).

The easiest way is to use the Maker component, using the command line :

php bin/console maker:entity

It will create Entity and Repository objects in our project. Then create them in the database using a Doctrine migration.

We need to modify the Repository, adding method that manages the data, with a pagination system:

// ...
class MemberRepository extends ServiceEntityRepository
{
    // ...
    public function getDatas(int $limit, int $offset, array $sorters = [], bool $count = false)
    {
        $q = $this->createQueryBuilder('m');

        if ($count)
        {
            $q->select('count(m.id)');
            return $q->getQuery()->getSingleScalarResult();
        }

        if (0 !== count($sorters))
        {
            foreach($sorters as $sorter)
            {
                $q->orderBy($sorter['field'], $sorter['dir']);
            }
        }

        $q->setMaxResults($limit);
        $q->setFirstResult($offset);

        return $q->getQuery()->getResult();
    }
    // ...
}

Let's go to create the Controller:

php bin/console make:controller MemberController

We adapt it to our needs:

// ...
#Route['/members', name: 'app_members_']
class MemberController extends AbstractController
{
    #[Route('/index', name: 'index')]
    public function index(Request $request)
    {
        return $this->render('member/index.html.twig', []);
    }

    #[Route('/list', name: 'list')]
    public function list(Request $request, MemberRepository $repo): JsonResponse 
    {
        $page = $request->get('page', 1);
        $limit = $request->get('size', null);
        $offset = ($page * $limit) - $limit;

        $sorters = $request->get('sort', []);

        $members = $repo->getDatas($limit, $offset, $sorters);
        $membersTotal = $repo->getDatas($limit, $offset, $sorters, true);

        $datas = [];
        $response = new JsonResponse();

        foreach ($members as $member)
        {
            $datas[] = [
                'm.id' => $member->getId(),
                'm.username' => $member->getUsername(),
                'm.firstname' => $member->getFirstname(),
                'm.lastname' => $member->getLastname(),
                'm.age' => $member->getAge()
            ];
        }

        $final['data'] = $datas;
        $final['last_page'] = ceil($membersTotal / $limit);
        $final['nb_row'] = $membersTotal;
        $final['last_row'] = $membersTotal;
        $final['size'] = sizeof($datas);

        $response->setContent(json_encode($final));
        return $response;
    }
}

We create the templates, starting with the layout:

<!DOCTYPE html>
<html>
    <head>
        <!-- ... -->
        {% block stylesheets %}
        <link rel="stylesheet" href="{{ asset('assets/css/tabulator.min.css') }}">
        {% endblock %}
    </head>
    <body>
        {% block body %}{% endblock %}
        {% block javascripts %}
        <script src="{{ asset('assets/js/tabulator.min.js') }}"></script>
        {% endblock %}
    </body>
</html>

We create the index page:

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

{% block stylesheets %}
    {{ parent() }}
    <style type="text/css">

    </style>
{% endblock %}

{% block body %}
<div class="wrapper">
    <div class="content">
        <h1>Tabulator intégration</h1>
        <hr />
        <div id="tabulator">

        </div>
        <div id="pagination"></div>
        <div id="page-count"></div>
    </div>
</div>
{% endblock %}

{% block javascripts %}
    {{ parent() }}
    <script type="text/javascript">
        var table = new Tabulator("#tabulator", {
            layout: "fitDataFill",
            renderHorizontal: "virtual",
            maxHeight: "85vh",
            sortMode: "remote",
            movableColumns: true,
            columnsHeaderVertAlign: "middle",
            ajaxUrl: "{{ path('app_members_list') }}", 
            initialSort: {
                { column: "m.username", dir: "asc" },
            },
            columnsDefaults: {
                headerWordWrap: true,
                vertAlign: "middle",
                resizable: true,
            },
            pagination: true,
            paginationSize: 15,
            paginationMode: "remote",
            paginationCounter: "rows",
            paginationCounterElement: "#page-count",
            columns: {
                { title: "ID", field: "m.id", sorter: "number", visible: true },
                { title: "Username", field: "m.username", sorter: "string", visible: true},
                { title: "Firstname", field: "m.firstname", sorter: "string", visible:true },
                { title: "Lastname", field: "m.lastname", sorter: "string", visible:true },
                { title: "Age", field: "m.age", sorter: "number", visible:true },
            }
        });
    </script>
{% endblock %}