Spinner not hiding and table not updating on first page of new results when using ajax pipeline

daveb1014

Link to test case: https://datatables.net/examples/server_side/pipeline.html

Description of problem: Hi, I'm using the ajax pipeline example shown at https://datatables.net/examples/server_side/pipeline.html and I've encountered an issue whereby I can page through the results, but the first time I hit a new page of results (ie. each 5 pages, by default) the spinner comes up and the ajax request is made and the data loaded, but the data table is not updated and the spinner stays there. Paging to the next page refreshes the table and hides the spinner. Has anybody seen this issue before?

The dataset has ~80,000 records, and I'm using all the default Datatables examples - so 50 records per page, and doing an XHTML request for another 5 pages of json data each 5 pages (which is working - it's just not updating the table, and it's leaving the spinner showing).

Here is my table html:

    <table id="dataTelemetry" class="table table-striped table-bordered dt-responsive nowrap">
        <thead>
            <tr>
                <th class="p-3">Col 1</th>
                <th class="p-3">Col 2</th>
                <th class="p-3">Col 3</th>
                <th class="p-3">Col 4</th>
                <th class="p-3">Col 5</th>
                <th class="p-3">Col 6</th>
                <th class="p-3">Col 7</th>
                <th class="p-3">Col 8</th>
                <th class="p-3">Col 9</th>
                <th class="p-3">Col 10</th>
            </tr>
        </thead>
    </table>

Here is my table javascript:

        <script>
        $(document).ready(function () {
            let objTable = $('#dataTelemetry').DataTable({
                processing: true,
                serverSide: true,
                ajax: $.fn.dataTable.pipeline({
                    url: 'ajax',
                    pages: 5, // number of pages to cache
                }),
            });
        });
    </script>

Here is an example of the initial JSON XHTML request, which runs successfully at page load/table init and gives me 5 pages with a brief showing of the spinner (ie. the expected behaviour):

{
    "draw": 108,
    "recordsTotal": 86300,
    "recordsFiltered": 86300,
    "data": [
        ["10000", "OFF", "OFF", "S3", "None", "None", "2134.533936", "2821.500000", "2.750000", "<a href=\"https://maps.google.com\" target=\"_blank\"></a>"],
        <snip - removed 49 records formatted as above, but with different data>
    ]
}

Here is an example of the JSON which is returned when reaching page 6, which causes the spinner to stay visible and the table not to update (ie. the unexpected behaviour):

{
    "draw": 109,
    "recordsTotal": 86300,
    "recordsFiltered": 86300,
    "data": [
        ["510000", "OFF", "OFF", "S3", "None", "None", "2207.336670", "2896.500000", "140.250000", "<a href=\"\" target=\"_blank\"></a>"],
        <snip - removed 49 records formatted as above, but with different data>
    ]
}

When the issue occurs and the spinner shows up, all I need to do is to navigate to the next page and it refreshes the table with the new data (but the next page's data obviously) and hides the spinner.

Apologies if the format of this question is wrong. Any insight would be much appreciated!

colin

There's nothing obvious there in the code or the responses, it looks like it's behaving as it should. Are you using the most recent DataTables sources?

As it works in the example you referenced, it must be something in your implementation. Are you able to link to your page, please, so we can debug it?

Colin

daveb1014

Thank you @colin for your fast response, and sorry for my slow reply.

This is the first time I have attempted to use datatables in a python project, django at that, and I have had to spend some time extracting the minimum viable code.

I'm developing on a local PC and I can't easily get it up and hosted somewhere sorry.

Here is the full code for my minimum viable test:

Source of initial page (url: /table_test), rendering as expected:

<!DOCTYPE html>
<html>

<script src="https://code.jquery.com/jquery-3.6.1.slim.min.js" integrity="sha256-w8CvhFs7iHNVUtnSP0YKEg00p9Ih13rlL9zGqvLdePA=" crossorigin="anonymous"></script>

<link crossorigin="anonymous" href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" rel="stylesheet">
<script crossorigin="anonymous" integrity="sha384-ka7Sk0Gln4gmtz2MlQnikT1wXgYsOg+OMhuP+IlRH9sENBO0LRn5q+8nbTov4+1p" src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>

<head>
    <script src="https://code.jquery.com/jquery-3.6.1.min.js" integrity="sha256-o88AwQnZB+VDvE9tvIXrMQaPlFFSUTR+nldQm1LuPXQ=" crossorigin="anonymous"></script>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/bs5/dt-1.12.1/datatables.min.css"/>
    <script type="text/javascript" src="https://cdn.datatables.net/v/bs5/dt-1.12.1/datatables.min.js"></script>
    <script>
        //
        // 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: '', // script url
                    data: null, // function or object with parameters to send to the server
                    // matching how `ajax.data` works in DataTables
                    method: 'GET', // 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 (typeof conf.data === 'function') {
                        // 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);
                    }

                    return $.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;
            });
        });

        //
        // DataTables initialisation
        //
        $(document).ready(function () {
            let objTable = $('#dataTelemetry').DataTable({
                processing: true,
                serverSide: true,
                ajax: $.fn.dataTable.pipeline({
                    url: 'ajax_test',
                    pages: 5, // number of pages to cache
                }),
                // columnDefs: [{
                //  "defaultContent": "-",
                //  "targets": "_all"
                // }]
            });
        });
    </script>
    </head>

<body><div class="p-4">
    
    <table id="dataTelemetry" class="table table-striped table-bordered dt-responsive nowrap">
        <thead>
            <tr>
                <th class="p-3">Col1</th>
                <th class="p-3">Col2</th>
            </tr>
        </thead>
    </table>
    
    </div></body>

</html>

Python source of the ajax call (url: /ajax_data_test):

def ajax_data_test(request):
    draw = 1 # appears to just be a count of draw calls for this session
    if request.session.get('draw'):
        draw = request.session['draw']
        draw += 1

    request.session['draw'] = draw

    lstDP = []

    for i in range(int(request.GET['start']), int(int(request.GET['start']) + int(request.GET['length'])), 1):
        lstDP.append([
            'col1', # just some data
            str(i) # just generate some unique data
        ])

    return JsonResponse({
        'draw': draw,
        'recordsTotal': Datapoint.objects.count(),
        'recordsFiltered': Datapoint.objects.count(),
        'data': lstDP
    })

The URL which I am receiving for each of the ajax_data_test requests looks like:

GET /ajax_test?draw=1&columns%5B0%5D%5Bdata%5D=0&columns%5B0%5D%5Bname%5D=&columns%5B0%5D%5Bsearchable%5D=true&columns%5B0%5D%5Borderable%5D=true&columns%5B0%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B0%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B1%5D%5Bdata%5D=1&columns%5B1%5D%5Bname%5D=&columns%5B1%5D%5Bsearchable%5D=true&columns%5B1%5D%5Borderable%5D=true&columns%5B1%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B1%5D%5Bsearch%5D%5Bregex%5D=false&order%5B0%5D%5Bcolumn%5D=0&order%5B0%5D%5Bdir%5D=asc&start=0&length=50&search%5Bvalue%5D=&search%5Bregex%5D=false&_=1667883541903 HTTP/1.1

If I break the above into new lines by the '&' character I get:

draw=1
columns%5B0%5D%5Bdata%5D=0
columns%5B0%5D%5Bname%5D=
columns%5B0%5D%5Bsearchable%5D=true
columns%5B0%5D%5Borderable%5D=true
columns%5B0%5D%5Bsearch%5D%5Bvalue%5D=
columns%5B0%5D%5Bsearch%5D%5Bregex%5D=false
columns%5B1%5D%5Bdata%5D=1
columns%5B1%5D%5Bname%5D=
columns%5B1%5D%5Bsearchable%5D=true
columns%5B1%5D%5Borderable%5D=true
columns%5B1%5D%5Bsearch%5D%5Bvalue%5D=
columns%5B1%5D%5Bsearch%5D%5Bregex%5D=false
order%5B0%5D%5Bcolumn%5D=0
order%5B0%5D%5Bdir%5D=asc
start=0
length=50
search%5Bvalue%5D=
search%5Bregex%5D=false
_=1667883541903

URL decoded version of the above:

draw=1
columns[0][data]=0
columns[0][name]=
columns[0][searchable]=true
columns[0][orderable]=true
columns[0][search][value]=
columns[0][search][regex]=false
columns[1][data]=1
columns[1][name]=
columns[1][searchable]=true
columns[1][orderable]=true
columns[1][search][value]=
columns[1][search][regex]=false
order[0][column]=0
order[0][dir]=asc
start=0
length=50
search[value]=
search[regex]=false
_=1667883541903

Apologies that I don't have a publicly-accessible webserver that can run this at the moment; if it is too much of a problem I can probably organise something temporarily.

Any help is appreciated. I don't understand why it is not loading the results when I hit those multiples of 5 (or click on a page > 5 pages ahead or behind the current page).

daveb1014

Well I'll be.

It appears that it was the 'draw' variable not being incremented correctly. Makes sense I guess, the js state machine probably uses this to help identify a dataset in memory and I guess it was trying to render a dataset that wasn't available? My best guess at what was going on.

Anyway, I seem to have solved my problem by making a small change in the ajax routine:

def ajax_data_test(request):
    draw = 1 # appears to just be a count of draw calls for this session
    if request.session.get('draw'):
        draw = request.session['draw']
        draw += 1

### BEGIN ###
    # enable overriding 'draw' request var if supplied in URL (ie. at initial page load)
    if request.GET.get('draw'):
        draw = int(request.GET['draw'])
### END ###

    request.session['draw'] = draw

    lstDP = []

    for i in range(int(request.GET['start']), int(int(request.GET['start']) + int(request.GET['length'])), 1):
        lstDP.append([
            'col1',
            str(i)
        ])

    return JsonResponse({
        'draw': draw,
        'data': lstDP,
        'recordsTotal': Datapoint.objects.count(),
        'recordsFiltered': Datapoint.objects.count()
    })

I just added a way for the incoming draw variable to inform the value of the draw variable in the server state.

Sorry for the noise everyone, and thank you @colin for taking a look over my mess

colin

Excellent, glad all sorted! Thanks for reporting back,

Colin