Building a DataTables Solution Using Craft CMS

Whether you're new to Craft CMS or have been using this incredible framework for some time, extending it can sometimes be challenging.

One of the challenges I recently encountered for a new client's site was building an Ajax-friendly "tables" solution. Since I tend to lean toward custom development over plugins, I decided to tackle this challenge head-on. 

Most web developers are familiar with DataTables, the standard for building an organized, responsive table, typically powered by a database. 

The challenge was whether to build it using Twig templates or through a custom controller. 

I'll dive into both options.

What is DataTables?

First up, though, what is DataTables? In my opinion, it's the most flexible and versatile jQuery plugin that turns standard HTML tables into feature-rich, interactive tables. It provides a straightforward way to implement complex table functionalities without extensive custom coding.

Some of the key features of DataTables include:

• Pagination: Automatically splits data into pages, reducing load times and improving user experience.
• Search: Provides instant search capabilities across all table data, making it easy to find specific information quickly.
• Sorting: Allows column sorting by clicking on the column headers, supporting multiple data types, including text, numbers, and dates.
• Ajax Integration: Enables loading data from various sources, including server-side processing for handling large datasets efficiently.
• Customization: Offers extensive customization options, from styling to advanced configuration settings, allowing developers to tailor the table to specific needs.

If you're opposed to jQuery or looking for a vanilla javascript tables solution, this guide might still help you with some key concepts of accessing Craft entries. I personally love jQuery and still use it today in most projects.

A Simple Twig-based Example

Ok, let's get into the basics.

If you're compiling less than 1,000 rows of data, a custom controller is completely unnecessary. Craft can easily retrieve your data, and DataTables can render your table with some basic structure.

Let's say you have employee data you would like to display. The table might look like this:

It's a fairly straightforward table display.

Now, let's put the same table into a Twig template, retrieve our data, and add DataTables.

{% set entries = craft.entries.section('employees').all() %}
<table id="example" class="display">
    <thead>
        <tr>
            <th>Name</th>
            <th>Position</th>
            <th>Start date</th>
            <th>Salary</th>
        </tr>
    </thead>
    <tbody>
        {% for entry in entries %}
        <tr>
            <td>{{ entry.name }}</td>
            <td>{{ entry.position }}</td>
            <td>{{ entry.startDate|date('m/d/Y') }}</td>
            <td>{{ entry.salary }}</td>
        </tr>
        {% endfor %}
    </tbody>
</table>
<script>
    $(function() {
        $('#example').DataTable();
    });
</script>

That's all it takes! The above example will provide a fully functional DataTables integration for your website.

Of course, you will need to include jQuery and the DataTables scripts. Make sure jQuery is loaded first.

A Controller-based DataTables Solution

Whether you're building a custom Craft CMS plugin or module, you will need a controller to process the logic and fetch your results using Ajax.  

The link above covers some basics for starting a plugin or module. This example below will only focus on the controller component.  

First, the HTML from the example above can be drastically simplified into the following:

<table id="example" class="display"></table>

Invoking DataTables gets a bit more involved. There are two methods to retrieve your data, either using GET or POST.

With GET, you will not need to factor CSFR. Also, since POST is generally more secure and doesn't limit us with URL length, I'll provide that example only, as it's the most comprehensive solution.

<script>
    $(function() {
        new DataTable('#example', {
            language: {
                search: "_INPUT_",
                searchPlaceholder: "Search Employees"
            },
            lengthMenu: [ 10, 25, 50, 100 ],
            responsive: true,
            autoWidth: false,
            serverSide: true,
            processing: true,
            ajax: {
                url: '{{ url("datatable") }}',
                type: 'POST',
                data: function (d) {
                    $.extend(d, {'{{ craft.app.config.general.csrfTokenName|e('js') }}' : '{{ craft.app.request.csrfToken|e('js') }}'});
                },
                error: function (xhr, error, code) {
                    console.log(xhr, code);
                    window.location.reload();
                }
            },
            columns: [
                { title: 'Name' },
                { title: 'Position' },
                { title: 'Start date' },
                { title: 'Salary' }
            ],
            order: [[ 4, "desc" ]]
        });
    });
</script>

The code above is a complete DataTables POST example with extra critical components. Let's discuss.

First, if you're using CSRF (and definitely should), your Ajax call will fail without including it in a key/value pair as outlined.  

I'm also providing a reload() call on error, should the CSRF expire with an additional debugging option for the developer tools console.  

I've also included a custom search placeholder, paging lengths, and ordering in the fourth column.  

Now, let's get into the controller needed to process the data.

namespace yournamespace\yourplugin\controllers;
use craft\web\Controller;
use craft\elements\Entry;
use yii\web\Response;
class DataTableController extends Controller
{
    protected array|int|bool $allowAnonymous = true;
    public function actionEntries(): Response
    {
        $this->requirePostRequest();
        $request = \Craft::$app->getRequest();
        
        $start = $request->getBodyParam('start', 0);
        $length = $request->getBodyParam('length', 10);
        $search = $request->getBodyParam('search');
        $searchValue = isset($search['value']) ? $search['value'] : '';
        // Sorting parameters
        $order = $request->getBodyParam('order', []);
        $orderColumnIndex = $order[0]['column'];
        $orderDir = $order[0]['dir'] == 'desc' ? 'DESC' : 'ASC';
        // Define the column names in the order they appear in the DataTables setup
        // These must match the field names you created in Craft
        $columns = [
            'name',
            'position',
            'startDate',
            'salary'
        ];
        // Base query, with the name of your defined section in Craft
        $baseQuery = Entry::find()->section('employees');
        // Clone the base query for total records without filtering
        // This should be ABOVE any filtering
        $totalRecords = clone $baseQuery;
        $totalRecords = $totalRecords->count();
        
        // Apply search filter if search value is provided
        if ($searchValue) {
            $baseQuery->search('*' . $searchValue . '*');
        }
        // Apply sorting
        if (isset($columns[$orderColumnIndex])) {
            $baseQuery->orderBy("{$columns[$orderColumnIndex]} {$orderDir}");
        } else {
            // Default order if no valid column is specified
            // "third click" on sorting doesn't work and is a known issue
            // adjust this accordingly for your fields
            $baseQuery->orderBy('startDate DESC'); 
        }
        // Fetch the paginated records with search applied
        $employees = $baseQuery
            ->offset($start)
            ->limit($length)
            ->all();
        // Format the data as required by DataTables
        $data = [];
        foreach ($employees as $entry) {
            $data[] = [
                'name' => $entry->name,
                'position' => $entry->position,
                'startDate' => $entry->startDate->format('m/d/Y'),
                'salary' => $entry->salary,
            ];
        }
        return $this->asJson([
            'draw' => $request->getBodyParam('draw'),
            'recordsTotal' => $totalRecords,
            'recordsFiltered' => $filteredRecords,
            'data' => $data
        ]);
    }
}

Quite a bit is happening, so let me briefly explain.

First, I'm going to assume you know how to define a route, either through an event handler or route registration.  

Next, for the search to work properly, you must enable the "Use this field’s values as search keywords" in the field settings. If the search is not working, try updating the search index using: php craft resave/entries --update-search-index at the command line.

This controller assumes you have a section called employees with the appropriate fields. Again, this is only an example -- you can call the section and fields anything you'd like to suit your needs.

The example controller code provides paging and sorting. Most everything is here to get you on the right track!

Conclusion

In this guide, we walked through the process of integrating DataTables with Craft CMS to display dynamic tables. We covered how to set up a basic environment, create a custom controller to handle POST requests and initialize DataTables with server-side processing.

Here are some additional resources you might find helpful:

• Craft CMS Controllers - Official documentation covering various aspects of controller development.
• DataTables Documentation - Comprehensive guide to using DataTables, including examples and API references.

By leveraging the flexibility and power of DataTables, you can enhance your Craft CMS projects with feature-rich tables that support your unique data display needs!

And, if you would like assistance with your Craft project, please visit our Craft CMS development page to start a discussion.