Pagination issue using datatables, JSON url datasource, Jquery

parth0009

Hi I am using JQUERY to load a JSON url data using Server side processing.
The issue I am having is that, all the records are being loaded on the first page of the pagination, whereas it is able to read from the pagination, that how many records it has correctly.

I selected Show 10 entries, and it is displaying 11 records, but it still says Showing 1 to 10 from 11 records. I need help in figuring out the issue. Below is the JQUERY script I am using:

$(document).ready(function() {  
        var searchTable = $('#searchResults').DataTable({
            "processing": true,
            "serverSide": true,
            "resetDisplay": false,
            "stateSave": true,
            "responsive": true,
            "data": [],
            "ajax": {
                "dataType": "json",
                "url": 'http://JSON.URL',
                "type": "POST",

            },`
`           "columns": [
                 { "data": "firstName" },
                 { "data": "lastName" },
                 { "data": "edit"},
                 { "data": "view"},
                 { "data": "create"}                                  
             ],
            "columnDefs": [
               {           
                   "render": function ( data, type, row ) {
                       var value = '';
                       if(row["edit"]) {
                           value = '<a href="#" class="searchResultAction" id="' + row["applicantID"] + '">Review</a>';
                       } else if(row["view"]) {
                           value = '<a href="#" class="searchResultAction" id="' + row["applicantID"] + '">View</a>';
                       }
                       <logic:equal value="Admin" name="selected">
                          value += '<br/><a href="#/></a>';
                       </logic:equal>    
                       return value;
                   }
               }           
            ]
        });  

      });

  // Initialize Datatables

kthorngren

Remove "data": [], from the config. You don't need it when using ajax. I don't think there is a resetDisplay option in Datatables. What are you expecting this option to do? Maybe we can point you to the correct option to use.

I suspect the problem is that your server script is returning 11 records where it should return only 10. Can you post your JSON response?

This technote will help you collect the JSON response:
https://datatables.net/manual/tech-notes/1

Does your server script process the parameters sent when using server side processing?
https://datatables.net/manual/server-side

Kevin

parth0009

@kthorngren I am getting 11 records in my json,I cannot post the response here but I can certainly post the format:
{"draw":"1","recordsFiltered":11,"data":[], "recordsTotal":11}

resetDisplay is nothing, I was just trying something and its of no use. I have removed it. But the thing is that as soon as I set serverSide to false, things start to operate normally.

Do I need some server side code for this in Java, since I am using java for pagination and all?

kthorngren

Your server side script will need to accept and process the parameters sent as described in this doc:
http://live.datatables.net/fecejoca/1/edit

For example your server script would use something like LIMIT 10 and OFFSET 0 (depending on your DB query language) to retrieve the first page of 10 records. Your server script is expected to retrieve only one page of data and to handle the searching and sorting operations within its DB query.

The first question is do you need server side processing? How many records do you expect to have?

Kevin

parth0009

@kthorngren I expect to have around 3000 and more records of data. There is currently a problem with loading of datatables of prod. It is loading all records at once, so initially it is taking around 20 seconds to load the data. So I figured I would need server side processing.

kthorngren

Take a look at the first FAQ here:
https://datatables.net/faqs/index#General-initialisation

deferRender may help. Before deciding server side is the way to go I would try to workout where the delay is. It could be the delay is in something your server script is doing to preprocess the data. Maybe it can be optimized.

Kevin

parth0009

@kthorngren Below is the current actual script I have, do you think anything setting from this can result in a performance issue?

$(document).ready(function() {  
            var searchTable = $('#searchResults').DataTable({ 
                "data": [],             
                "deferRender": true,
                "oLanguage.sSearch": "Filter",
                "drawCallback": function( settings ) {

                    $(".searchAction").click(
                        function(){
                            $('#Id').val($( this ).attr('id')); 
                            $('#searchForm').submit();           
                    });
                },
                "columns": [
                     { "data": "firstName" },
                     { "data": "lastName" },
                     { "data": "nameAcronym", "width": "5%" },
                     { "data": "appID" },
                     { "data": "rType"},
                     { "data": "aType", "defaultContent": ""},
                     { "data": "status", "width":"20%" },
                     { "data": "statusDate", "type": "date"},
                     { "data": "iD" },
                     { "data": "formId"},
                     { "data": "edit"},
                     { "data": "view"},
                     { "data": "create"}                                  
                 ],
                "columnDefs": [
                   {           
                       "render": function ( data, type, row ) {
                           var value = '';
                           if(row["edit"]) {
                               value = '<a href="#" class="searchAction" id="' + row["iD"] + '">Edit</a>';
                           } else if(row["view"]) {
                               value = '<a href="#" class="searchAction" id="' + row["iD"] + '">View</a>';
                           }
                           <logic:equal value="Admin" name="selected">
                              value += '<br/><a href="/download.do?formId=' + row["formId"] + '">Export<img src="/pdf.gif" alt="PDF"/></a>';
                           </logic:equal>    
                           return value;
                       }, 
                       "targets": 12, "width": "5%"
                   },
                   { 
                       "visible": false,  
                       "targets": [8,9,10,11]
                   }               
                ],
                "order": [[ 7, "asc" ]]
            });  


            var url = '/searchJSON?&searchByType=true' + $('.searchForm').serialize();
            searchTable.ajax.url(url).load();
          });

kthorngren

The only thing I see that could cause delays within Datatables is the columns.render function. It doesn't look like what you have would cause much delay but I would remove it just to test.

One thing interesting to learn about Datatables is the render function and how / when it runs. Put the following in your render function just above var value = '';:
console.log('render', type);

When you load your table you will see multiple passes for each row for the different "type"s. These are explained here:
https://datatables.net/manual/data/orthogonal-data

With deferRender on you should see render run for each row for "type", "filter" and "sort" (if you are sorting by that column). For "display" it will run only for those rows being displayed. One way to improve this performance would be to check if type === 'display' to render the column. Similar to the "Show ellipsis for long strings" example in the doc for columns.render. You may not need/want that column processed for the other types.

The other thing I would do is use timers to display how long it takes your sever script from the time it receives the ajax request until it returns the data. Alternatively, as a test, just use jQuery's AJAX function to call the same URL and time how long it takes to collect the info. That could eliminate Datatables as being slow to build the table.

Kevin

parth0009

So if I remove the render function, how can I do the processing that is been done in the render tag?

kthorngren

I suggested removing it as a test to see if its causing the delay issues. Everything I mention above is just to test to see where the delay is coming from.

Kevin