README

🚀 Create powerful entity research tables in only a few minutes.

This repository allows to quickly setup DataTables for persisted Symfony entities, with a very powerful ajax filtering solution. It comes bundled with:

Ajax filter for persisted entites - even with relational entities
An "intelligent" default display of entities properties in the table
A production-ready integration template
CDN option for fastest integration

How to use

Install with `composer require loicpennamen/entity-datatables-bundle`.
If your are using Symfony Flex, the Bundle is already configured. Learn more here.

Getting the columns configuration from a service

You will need two Controller methods:

One for displaying the page containing the table
A second one working as an API to retrieve the ajax results.

Since both methods need to access the table's configuration, it is recommended to use a service that provides it. You can create such a method for each DataTable in your project.

For instance, let's have a DataTable display and filter our users. Let's create a UserService with a method that returns our table's configuration:

<?php
// src/Services/UserService.php
namespace App\Services;

// Helper class to configure our table's columns
use LoicPennamen\EntityDataTablesBundle\Entity\DtColumn;

class UserService
{
    public function getDataTableColumns(): array
    {
        $columns = [];
        
        $col = new DtColumn();
        $col->setSlug('id'); // Required: must be unique in each DataTable
        $col->setLabel("User ID"); // The label displayed on top of the DataTable's column 
        $col->setName("User ID in database"); // A longer text to display details about the column 
        $col->setSortingKey('user.id'); // What field will be used for sorting (see repository configuration)
        // Store in the array
        $columns[] = $col;

        $col = new DtColumn();
        $col->setSlug('email');
        $col->setLabel('Email');
        $col->setName("User Email"); 
        $col->setSortingKey('user.email');
        $columns[] = $col;

        // You can also have columns to display arrays, dates or any object
        // For instance here, you can filter by user permissions (roles)
        // The cell's template will handle how to display the data
        $col = new DtColumn();
        $col->setSlug('roles');
        $col->setLabel('Roles');
        $col->setName("User permissions"); 
        $col->setSortingKey('user.roles');
        $columns[] = $col;

        // Here, we define a column that is NOT linked to the User's properties 
        // We can use it as a placeholder for a toolbar: Read, Update, Delete... 
        $col = new DtColumn();
        $col->setSlug('tools');
        $col->setSortable(false); // Don't forget to disable sorting in this case 
        $columns[] = $col;

        return $columns;
    }
}

Displaying the table

Now that we have a method to retrieve our columns, let's display a table. In our Controller:

<?php
// src/Controller/UserController.php
namespace App\Controller;

use App\Services\UserService;
// ...

class UserController extends AbstractController
{
    #[Route('/user/search', name: 'app_user_search')]
    public function search(UserService $userService): Response
    {
        // List of DtColumn objects
        $columns = $userService->getDataTableColumns();

        return $this->render('user/search.html.twig', [
            'columns' => $columns,
        ]);
    }
    
    // We will already need this route for the future API
    #[Route('/api/user/search', name: 'app_user_search_api')]
    public function searchApi(){
       // ...
    }
}

And in your template, include the following snippet where you want your table.

{% include '@LoicPennamenEntityDataTables/table.html.twig' with {config: {
    columns: columns,
    dataUrl: path('app_user_search_api'),
}} %}

The default table template uses parameters that you can override into the config object:

columns: The array of DtColumn objects - required.
dataUrl: Path to the API endpoint - required.
useCdn: Boolean to integrate DataTable's javascripts and CSS file via CDN int the template. Default true.
useJQueryCdn: Boolean to integrate JQuery via CDN int the template. Default true.
additionalData: Additional parameters to POST to the API endpoint.
tableId: The table's DOM ID, generated automatically.
tableClasses: Additionnal class names for the <table> element.
translationFile: Example asset('./datatables.fr.json'). Read more about it here

Tips for geeks: You can also copy/paste the content of the template, for free customization of the table: vendor/loicpennamen/entity-datatables-bundle/Resources/views/table.html.twig

Getting the results

At this point, you should see a blank table with the columns in place. And probably a javascript error, because the ajax API is not yet set.

We need a method that will get DataTables' POST values and return a JSON response containing the content of each row and cell. For this, let's use a special kind of EntityRepository that handles all the filtering and pagination hassle, based on DataTables variables.

Open your entity's repository and have it extend the following class:
LoicPennamen\EntityDataTablesBundle\Repository\DatatablesSearchRepository

<?php
// src/Repository/UserRepository.php
namespace App\Repository;

use App\Entity\User;
use Doctrine\Persistence\ManagerRegistry;
use LoicPennamen\EntityDataTablesBundle\Repository\DatatablesSearchRepository;

// Update the extension 
class UserRepository extends DatatablesSearchRepository
{
    // Configure the search options
    public function __construct(ManagerRegistry $registry)
    {
        $this->setEntityAlias('user');
        $this->addSearchField('user.id');
        $this->addSearchField('user.email');
        $this->addSearchField('user.roles');

        parent::__construct($registry, User::class);
    }
}

The setEntityAlias method is mandatory and will be used in further configurations. Ideally, it is a camelCase string. Keep it simple.
The addSearchField methods set the entity's properties that will be checked for filtering. They must be camelCase.

Fancier configuration, like joining relationing entities comes further in this doc. It's easy.

Let's get back to our Controller to update the API method: Its aim is to filter our users and return the table's data. We will prepare the query configuration, then get the results, and finally formate the returned contents into an array of rows and cells for display.

<?php
// src/Controller/UserController.php
namespace App\Controller;

use App\Services\UserService;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Request;
use LoicPennamen\EntityDataTablesBundle\Services\EntityDataTablesService;
// ...

class UserController extends AbstractController
{
    // ... 
    
    #[Route('/api/user/search', name: 'app_user_search_api')]
    public function searchApi(
            Request $request,
            EntityManagerInterface $em,
            UserService $userService,
            EntityDataTablesService $datatableService
       ): JsonResponse
       {
        // Our custom repository
        $repo = $em->getRepository(User::class);
        // Let's retrieve the column's configuration
        $tableColumns = $userService->getDataTableColumns();
        // And convert POST data to useable options in our custom repository
        $options = $datatableService->getOptionsFromRequest($request, $tableColumns);
        // All the magic happens here, with search, pagination and all...
        $entities = $repo->search($options);
        
        return $this->json([
            // This handles all the data formatting
            'data' => $datatableService->getTableData($entities, $tableColumns),
            // This counts all results in the database
            'recordsTotal' => $repo->countSearchTotal($options),
            // This counts all results within applied search
            'recordsFiltered' => $repo->countSearch($options),
        ]);
    }
}

At this point, you should see a working table, your usernames, and most likely a few cell with an unfitting content. In our example, the "Roles" column might be filled with [roles: Warning: Array to string conversion]. This is because the provided service does not know how it should be formatting some values, like array, or objects.

How to format cells?

By default, EntityDataTablesService->getTableData() creates an HTML string intelligently for each cell. Now, you may want to handle array properties (like user Roles) in a fancy way: Like add some red color to Administrator role, or a comma between each role.

To do this, let's create a twig template for each column. Remember the slug you defined in your Repository - In our example, we used user. In your templates folder, create a user folder (or any other slug you chose for your entity). Then add a file called cell-roles.html.twig. In other words, the file naming convention is:

[entitySlug]/cell-[propertyOfTheEntity].html.twig

Inside this template, you can access your object in a variable under its slug name. In our example, the twig variable user contains the row's User object.

// templates/user/cell-roles.html.twig
{# @var user \App\Entity\User #}
 
{% for role in user.roles %}
    <span>{{ role }}</span>{{ loop.last ? '' : ', ' }}
{% endfor %}

At this point, your roles column should display a list of comma-separated User Roles. It's up to you to translate and formate these however you like.

You can use this method to display content in an arbitraty column. In our example, we defined a tools columns. This is a convenient way to add a menu for instance:

// templates/user/cell-tools.html.twig
{# @var user \App\Entity\User #}

<a href="#">Update</a> | <a href="#">Delete<a>

How to change the cell's templates folder?

If you do not want to use your entity's slug name as template folder name, you can define a different folder name in the third parameter of the `getTableData` method:

<?php
// src/Controller/UserController.php
namespace App\Controller;
// ...

class UserController extends AbstractController
{
    // ... 
    
    #[Route('/api/user/search', name: 'app_user_search_api')]
    public function searchApi(Request $request, EntityManagerInterface $em, UserService $userService, EntityDataTablesService $datatableService): JsonResponse
       {
        // ...
        
        return $this->json([
            'data' => $datatableService->getTableData(
                 $entities,
                 $tableColumns, 
                 // Let's define an arbitrary, 2-level template folder
                 'user-datatables/table-cells'
             ),
            'recordsTotal' => $repo->countSearchTotal($options),
            'recordsFiltered' => $repo->countSearch($options),
        ]);
    }
}

How to define a custom template for a single column?

In addition to the default template naming convention, and the possiblity to change the template folder for the whole table, you can set one specific template path for a column. The DtColumn object has a setTemplate() method that takes as argument the path to a custom template file:

<?php
// src/Services/UserService.php
namespace App\Services;

use LoicPennamen\DataTablesBundle\Entity\DtColumn;

class UserService
{
    public function getDataTableColumns(): array
    {
        $columns = [];
        
        $col = new DtColumn();
        $col->setSlug('id');
        $col->setLabel("User ID"); 
        $col->setName("User ID in database"); 
        $col->setSortingKey('user.id');
        
        // Path to the file in your /templates directory (or any listed Twig directory)
        $col->setTemplate('user/id-with-notifications.html.twig');
        
        $columns[] = $col;

        return $columns;
    }
}

Template configuration precedence

Each of these template-defining methods take precedence over the previous one. That means:

If a per-column template is set, but the file is not found, then the script looks for a template in the custom folder.
If a custom template folder is set, but the file is not found, then the script looks for a template file with the slug-named convention.
If not template file is found with the slug-named convention, then the script guesses the best way to display the cell's value.

Improve assets integration

For quick integration, the bundle includes jQuery and Datatables assets by default via a CDN. This is pretty unreliable since it lacks a fine control of assets loading, and it may interfere with your own JQuery implementation. It is highly recommended to disable the CDN option in your page template:

{% include '@LoicPennamenEntityDataTables/table.html.twig' with {config: {
    columns: columns,
    dataUrl: path('app_user_search_api'),
    useCdn: false,
    useJQueryCdn: false,
}} %}

And then, to integrate the required assets in your own favorite flavour (Read more).

As of March 2023 this is one way to do it. This method applies to Symfony assets with Webpack Encore! The following example assumes you have already set up your Webpack configuration. If not, read this guide.

Note to Bootstrap users: I prefer using the Bootstrap 5 version of DataTables. If you also do, add the -bs5 suffix to each Datatables package name.

Require the assets in your console.

npm install --dev jquery \
expose-loader \
datatables datatables.net \
datatables.net-fixedheader \
datatables.net-responsive \
datatables.net-select

Require these assets in your compiled application:

// assets/js/app.js

// Load Jquery package
import $ from "expose-loader?exposes=$,jQuery!jquery";

// Load DataTables package with plugins
require('datatables.net');
require('datatables.net-fixedheader');
require('datatables.net-responsive');
require('datatables.net-select');

// Import DataTables styles
import 'datatables/media/css/jquery.dataTables.css';

Don't forget to compile your assets, for instance with npm watch.
You are ready to go. The default template launches DataTable javascripts after the DOM is loaded, so the position of your application's script in the DOM should not be an issue.

How to handle relational entities

In our example, we filter User objects. Let's say each User has a relationship to one or several Address entities and we want to display their city and country in the table. If there is an ORM association, it is very easy to implement in the repository:

<?php
// src/Repository/UserRepository.php
namespace App\Repository;

use App\Entity\User;
use Doctrine\Persistence\ManagerRegistry;
use LoicPennamen\EntityDataTablesBundle\Repository\DatatablesSearchRepository;

// Update the extension 
class UserRepository extends DatatablesSearchRepository
{
    // Configure the search options
    public function __construct(ManagerRegistry $registry)
    {
        $this->setEntityAlias('user');
        $this->addSearchField('user.username');
        // ...
        
        // Add a LEFT JOIN query: allow the address to be NULL
        $this->addLeftJoin('user.address', 'address');
        // Add a INNER JOIN query: the User will not be matched if its address is NULL
        $this->addJoin('user.address', 'address');  
        
        parent::__construct($registry, User::class);
    }
}

The second parameter of the addJoin() and addLeftJoin() methods defines a "slug" for the connected entity. This slug can be used in a DtColumn object to create a sortable column.

In our example, the Address entity contains a string property for country and city names. let's create columns to display them:

<?php
// src/Services/UserService.php
namespace App\Services;

use LoicPennamen\DataTablesBundle\Entity\DtColumn;

class UserService
{
    public function getDataTableColumns(): array
    {
        $columns = [];
        
        $col = new DtColumn();
        $col->setSlug('username'); 
        $col->setLabel('Username'); 
        $col->setSortingKey('user.username');
        $columns[] = $col;

        $col = new DtColumn();
        $col->setSlug('city');
        $col->setLabel('City'); 
        $col->setSortingKey('address.city');
        $columns[] = $col;

        $col = new DtColumn();
        $col->setSlug('country');
        $col->setLabel('Country'); 
        $col->setSortingKey('address.country');
        $columns[] = $col;

        return $columns;
    }
}

Note: This works with multiple relations!
For instance, if you user had several Address in a OneToMany relationship your could rename the slug address to addresses for clarity. And then filter by any of the city | country names.

How to add custom POST data to the API?

The provided default template contains an additionalData property to add arbitrary POST values to the controller's API method. In your template:

    {% include '@LoicPennamenEntityDataTables/table.html.twig' with {config: {
        columns: columns,
        dataUrl: path('app_user_search_api'),
        additionalData: {
            propertyOne: 'value 1',
            propertyTwo: 'value 2'
        }
    }} %}

Advanced configuration

More configuration can be applied inside the repository to customize the filtering behaviour on every request made via the Datable "Search" field. These configurations are repository-wide.

Boundaries: Starts with, ends with, contains, exact match...

Boundaries define where to search for the query string inside each of the entity's filterable properties - ie "columns".

<?php
// src/Repository/UserRepository.php
namespace App\Repository;

use App\Entity\User;
use Doctrine\Persistence\ManagerRegistry;
use LoicPennamen\EntityDataTablesBundle\Repository\DatatablesSearchRepository;

class UserRepository extends DatatablesSearchRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        $this->setEntityAlias('user');
        $this->addSearchField('user.username');
        // ...
        
        // By default: one searchable property contains the string anywhere
        $this->setSearchStringBoundaries(self::SEARCH_STRING_BOUNDARIES_CONTAINS);
        // one searchable property contain the exact, full string
        $this->setSearchStringBoundaries(self::SEARCH_STRING_BOUNDARIES_EXACT);
        // one searchable property starts with the string
        $this->setSearchStringBoundaries(self::SEARCH_STRING_BOUNDARIES_STARTS_WITH);
        // one searchable property ends with the string
        $this->setSearchStringBoundaries(self::SEARCH_STRING_BOUNDARIES_ENDS_WITH);
        
        parent::__construct($registry, User::class);
    }
}

Division: Filter with the entire string, with every word, with any word...

Now if you add spaces or commas in the search input, shall the words be independently searched for, or considered as one single string?

Inside the repository, the method setSearchStringDivision() allows for several behaviours:

<?php
// src/Repository/UserRepository.php
namespace App\Repository;

use App\Entity\User;
use Doctrine\Persistence\ManagerRegistry;
use LoicPennamen\EntityDataTablesBundle\Repository\DatatablesSearchRepository;
 
class UserRepository extends DatatablesSearchRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        $this->setEntityAlias('user');
        $this->addSearchField('user.username');
        // ...
        
        // By default: return entities containing all the words in search field (in one single property)
        $this->setSearchStringDivision(self::SEARCH_STRING_DIVISION_EVERY_WORD);
        // Returns entities containing ANY words of the search input value (in one single property)
        $this->setSearchStringDivision(self::SEARCH_STRING_DIVISION_ANY_WORD);
        // Does not divide the string, and filters entities containing the whole input value  
        $this->setSearchStringDivision(self::SEARCH_STRING_DIVISION_FULL_STRING);
        
        parent::__construct($registry, User::class);
    }
}

Let's suppose we have a user named John Doe, and boundaries are set to "CONTAINS". These are the different results per configuration and search query:

	John	Doe	John Doe	Jane Doe
EVERY_WORD	Match	Match	Match
ANY_WORD	Match	Match	Match	Match
FULL_STRING			Match

TODO

Per-column options for search string boundaries + search string division + null values last (already there) + documentation
Allow cross-property filtering: Add an option to allow searching divided filter strings in several properties. Example: "John France".
API to update repository filtering options / columns filtering options on-the-fly via Ajax.

loicpennamen / entity-datatables-bundle

Maintainers

Details