(Cakephp) Datatable Example - With custom column search and cache pagination

index.ctp

<div class="row">

    <div class="col-md-12">

        <h3><?=__('Users Management')?></h3>

        <div class="panel panel-default">
            <div class="row">
                <div class="col-md-12">

                    <div class="panel-heading">
                        <a href="/aps/users/add" class='btn btn-primary'>Novo Usuário</a>
                        <a href="/aps/users/excel" class='btn btn-default'>Excel</a>
                    </div>

                    <div class="panel-body">
                        <div class="table-responsive">
                            <table id="table" class="table table-striped table-hover" cellpadding="0" cellspacing="0" width="100%">
                                <thead>
                                        <tr>
                                            <th>Id</th>
                                            <th>Nome</th>
                                            <th>E-mail</th>
                                            <th>Cargo</th>
                                            <th>Recurso</th>
                                            <th>Ações</th>
                                        </tr>

                                </thead>
                                <thead class="hidden-xs hidden-sm hidden-md">
                                    <tr>
                                        <th><input type="text" class="search-input-text" data-column="0" style="width: 25px"></th>
                                        <th><input type="text" class="search-input-text" data-column="1"  ></th>
                                        <th><input type="text" class="search-input-text" data-column="2"  ></th>
                                        <th><input type="text" class="search-input-text" data-column="3"  ></th>
                                        <th>
                                            <select data-column="4" class="search-input-select" style="width: 150px" place-holder="Recurso">
                                                <option value=""></option>
                                                <option value="1">Sim</option>
                                                <option value="0">Não</option>
                                            </select>
                                        </th>
                                    </tr>
                                </thead>

                                <tbody>

                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>

            </div>

        </div>

    </div>
</div>



<script type="text/javascript">

    var dataTable;
    var url = "<?php echo BASE_URL ?>";

    //
    // Pipelining function for DataTables. To be used to the `ajax` option of DataTables
    //
    $.fn.dataTable.pipeline = function ( opts ) {
        // Configuration options
        var conf = $.extend( {
            pages: 5,     // number of pages to cache
            url: url + 'users/getUsers',      // script url
            data: null,   // function or object with parameters to send to the server
                          // matching how `ajax.data` works in DataTables
            method: 'POST' // Ajax HTTP method
        }, opts );

        // Private variables for storing the cache
        var cacheLower = -1;
        var cacheUpper = null;
        var cacheLastRequest = null;
        var cacheLastJson = null;

        return function ( request, drawCallback, settings ) {
            var ajax          = false;
            var requestStart  = request.start;
            var drawStart     = request.start;
            var requestLength = request.length;
            var requestEnd    = requestStart + requestLength;

            if ( settings.clearCache ) {
                // API requested that the cache be cleared
                ajax = true;
                settings.clearCache = false;
            }
            else if ( cacheLower < 0 || requestStart < cacheLower || requestEnd > cacheUpper ) {
                // outside cached data - need to make a request
                ajax = true;
            }
            else if ( JSON.stringify( request.order )   !== JSON.stringify( cacheLastRequest.order ) ||
                      JSON.stringify( request.columns ) !== JSON.stringify( cacheLastRequest.columns ) ||
                      JSON.stringify( request.search )  !== JSON.stringify( cacheLastRequest.search )
            ) {
                // properties changed (ordering, columns, searching)
                ajax = true;
            }

            // Store the request for checking next time around
            cacheLastRequest = $.extend( true, {}, request );

            if ( ajax ) {
                // Need data from the server
                if ( requestStart < cacheLower ) {
                    requestStart = requestStart - (requestLength*(conf.pages-1));

                    if ( requestStart < 0 ) {
                        requestStart = 0;
                    }
                }

                cacheLower = requestStart;
                cacheUpper = requestStart + (requestLength * conf.pages);

                request.start = requestStart;
                request.length = requestLength*conf.pages;

                // Provide the same `data` options as DataTables.
                if ( $.isFunction ( conf.data ) ) {
                    // As a function it is executed with the data object as an arg
                    // for manipulation. If an object is returned, it is used as the
                    // data object to submit
                    var d = conf.data( request );
                    if ( d ) {
                        $.extend( request, d );
                    }
                }
                else if ( $.isPlainObject( conf.data ) ) {
                    // As an object, the data given extends the default
                    $.extend( request, conf.data );
                }

                settings.jqXHR = $.ajax( {
                    "type":     conf.method,
                    "url":      conf.url,
                    "data":     request,
                    "dataType": "json",
                    "cache":    false,
                    "success":  function ( json ) {
                        cacheLastJson = $.extend(true, {}, json);

                        if ( cacheLower != drawStart ) {
                            json.data.splice( 0, drawStart-cacheLower );
                        }
                        if ( requestLength >= -1 ) {
                            json.data.splice( requestLength, json.data.length );
                        }

                        drawCallback( json );
                    }
                } );
            }
            else {
                json = $.extend( true, {}, cacheLastJson );
                json.draw = request.draw; // Update the echo for each response
                json.data.splice( 0, requestStart-cacheLower );
                json.data.splice( requestLength, json.data.length );

                drawCallback(json);
            }
        }
    };

    // Register an API method that will empty the pipelined data, forcing an Ajax
    // fetch on the next draw (i.e. `table.clearPipeline().draw()`)
    $.fn.dataTable.Api.register( 'clearPipeline()', function () {
        return this.iterator( 'table', function ( settings ) {
            settings.clearCache = true;
        } );
    } );


    dataTable = $('#table').DataTable({
       "language": {
            "url": 'http://' + window.location.host + "/aps/i18n/Portuguese-Brasil.json"
        },
        responsive: true,
        "bAutoWidth": false,
        "processing": true,
        "serverSide": true,
        ajax: $.fn.dataTable.pipeline( {
            url: url + 'users/getUsers',
            type: 'post',
            pages: 5
        }),
      
        "columns": [
            { "data": "id" },
            { "data": "name" },
            { "data": "email" },
            { "data": "role" },
            { "data": "resource" },
            { "data": "actions" },
        ],
       
    });

    dataTable.on( 'draw', function () {
        console.log('teste');
        $('#table_filter').addClass('hidden-md hidden-lg');
    });



    $('.search-input-text').on( 'keyup click', function () {   // for text boxes
        var i =$(this).attr('data-column');  // getting column index
        var v =$(this).val();  // getting search input value
        dataTable.columns(i).search(v).draw();
        // dataTable.ajax.reload( null, false );
    } );
    $('.search-input-select').on( 'change', function () {   // for select box
        var i =$(this).attr('data-column');
        var v =$(this).val();
        dataTable.columns(i).search(v).draw();
        // dataTable.ajax.reload( null, false );
    } );

</script>

UserController.php

<?php
namespace App\Controller;

use App\Controller\AppController;
use Cake\Network\Email\Email;

/**
 * Users Controller
 *
 * @property \App\Model\Table\UsersTable $Users
 */
class UsersController extends AppController
{
    public function getUsers()
    {

        $this->autoRender = false;

        $params = $this->request->data;

        if (isset($this->request->data['draw'])) {
            $draw = $this->request->data['draw'];
        } else {
            $draw = 1;
        }

        $start = $this->request->data['start'];
        $limit = $this->request->data['length'];

        $total = $this->Users->find('all')
                    ->where(['Users.company_id = ' => $this->Auth->user('company_id')])->count();

        $columns = [
            0 => ['name' => 'id', 'type' => 'integer'],
            1 => ['name' => 'name', 'type' => 'string'],
            2 => ['name' => 'email', 'type' => 'string'],
            3 => ['name' => 'role', 'type' => 'string'],
            4 => ['name' => 'resource', 'type' => 'boolean'],
        ];

        $where = [];

        for ($i = 0; $i < 5; $i++) {
            if ($params['columns'][$i]['search']['value'] != '') {
                if ($params['columns'][$i]['data'] == 'id') {
                    array_push($where, [
                        'Users.' . $params['columns'][$i]['data'] . ' =' => htmlspecialchars($params['columns'][$i]['search']['value']),
                    ]);
                } elseif ($params['columns'][$i]['data'] == 'resource') {

                    if($params['columns'][$i]['search']['value'] == 1){

                        array_push($where, [
                            'Users.' . $params['columns'][$i]['data'] . ' =' => true ,
                        ]);
                    }
                    else if($params['columns'][$i]['search']['value'] == 0){
                        array_push($where, [
                            'Users.' . $params['columns'][$i]['data'] . ' =' => false ,
                        ]);

                    }
                } else {
                    array_push($where, [
                        'Users.' . $params['columns'][$i]['data'] . ' LIKE' => '%' . htmlspecialchars($params['columns'][$i]['search']['value']) . '%',
                    ]);
                }

            }
        }

        if (!empty($params['search']['value'])) {

            foreach ($columns as $column) {
                if ($column['type'] == 'string') {

                    array_push($where, [
                        'Users.' . $column['name'] . ' LIKE' => '%' . htmlspecialchars($params['search']['value']) . '%',
                    ]);
                }
            }

            $where = ['OR' => $where];
        }

        if (!empty($where)) {

            if($start == 0){

                $users = $this->Users->find('all')
                    ->where(
                        [
                            'Users.company_id = ' => $this->Auth->user('company_id'),
                        ])
                    ->andWhere($where)
                    ->order(
                        [
                            $columns[$params['order'][0]['column']]['name'] . ' ' . $params['order'][0]['dir'],
                        ])
                    ->limit($limit);
            }
            else{
                $users = $this->Users->find('all')
                    ->where(
                        [
                            'Users.company_id = ' => $this->Auth->user('company_id'),
                        ])
                    ->andWhere($where)
                    ->order(
                        [
                            $columns[$params['order'][0]['column']]['name'] . ' ' . $params['order'][0]['dir'],
                        ])
                    ->limit($limit)
                    ->offset(intval($start));

            }

        } else {
            if ($start == 0) {
                $users = $this->Users->find('all')
                    ->where(['Users.company_id = ' => $this->Auth->user('company_id')])
                    ->order(
                        [
                            $columns[$params['order'][0]['column']]['name'] . ' ' . $params['order'][0]['dir'],
                        ])
                    ->limit($limit);
            }else{
                $users = $this->Users->find('all')
                    ->where(['Users.company_id = ' => $this->Auth->user('company_id')])
                    ->order(
                        [
                            $columns[$params['order'][0]['column']]['name'] . ' ' . $params['order'][0]['dir'],
                        ])
                    ->limit($limit)
                    ->offset(intval($start));
            }

        }

        $cont = 0;
        $json = [];

        foreach ($users as $user) {
            $json[$cont] = [
                'DT_RowId' => $user->id,
                'id'       => $user->id,
                'name'     => $user->name,
                'email'    => $user->email,
                'role'     => $user->role,
                'resource' => $user->resourceDescription,
                'actions'  =>
                '
                         <a data-toggle="tooltip" title="Consultar" class="btn btn-info" href= "' . BASE_URL . 'users/view/' . $user->id . '"><i class="fa fa-eye" aria-hidden="true"></i></a>
                         <a data-toggle="tooltip" title="Editar" class="btn btn-warning" href= "' . BASE_URL . 'users/edit/' . $user->id . '"><i class="fa fa-pencil" aria-hidden="true"></i></a>
                         <a data-toggle="tooltip" title="Excluir" class="btn btn-danger" onClick=deleteRecord(' . $user->id . ',"' . BASE_URL . '","' . $this->name . '")><i class="fa fa-trash" aria-hidden="true"></i></a>
                         <a data-toggle="tooltip" title="Grupos" class="btn btn-primary" onClick=userGroups(' . $user->id . ',"' . BASE_URL . '","' . $this->name . '")><i class="fa fa-users" aria-hidden="true"></i></a>
                    ',

            ];
            $cont++;

        }

        $data = [
            "draw"            => $draw,
            "recordsTotal"    => $total,
            "recordsFiltered" => $total,
            'data'            => $json,
        ];

        echo json_encode($data);

    }
}