jQuery datatables pagination broken after changing to server-side processing

Question

I had a page that was initializing an empty dataTable, and getting the json data with $.getJSON() after a jquery change from a select.It was then adding to the table with .fnAddData.Like this:

oTableDisk = $('#disk_connection_table').dataTable({
        "bJQueryUI": true,
        "iDisplayLength": 30,
        "oLanguage": {
            "sLengthMenu": tableLength
        },
        "fnRowCallback": function(nRow, aData, iDisplayIndex, iDisplayIndexFull){
            $(nRow).attr('id', aData[0] + "|<?php echo $this->hostInfo['name']; ?>|<?php echo $this->hostInfo['id']; ?>");
            return nRow;
        },
        "aoColumns": [{"bSearchable": false, "bVisible": false}, null, null, null, null, null, null]
    });

$('#disk_switch').change(function(){
        $.getJSON('/host/getavailableports?type=disk&switch=' + $('#disk_switch option:selected').val(), function(data){
            if(data[0]){
                data.reverse();
                oTableDisk.fnClearTable();
                $.each(data, function(index){
                //console.log(data[index]);
                    oTableDisk.fnAddData([data[index]['Item1'],
                                        data[index]['Item2'], 
                                        data[index]['Item3'], 
                                        data[index]['Item4'], 
                                        data[index]['Item5'], 
                                        data[index]['Item6'], 
                                        data[index]['Item8']]);

                });
                enableEditable(oTableDisk);
            }
        });
    });

This was working fine until we needed to process more than 500 rows of information and was breaking IE with "script has become unresponsive" errors.

Now, I'm not initializing an empty dataTable at all, but creating one when a select menu changes like this:

 $('#disk_switch').change(function(){
        oTableDisk = $('#disk_connection_table').dataTable({
            "bJQueryUI": true,
            "iDisplayLength": 30,
            "bProcessing": true,
            "bServerSide": true,
            "bDestroy": true,
            "sAjaxSource": '/host/getavailableports?type=disk&switch=' + $('#disk_switch option:selected').val(),
            "aaSorting": [[0, "asc"]],
            "oLanguage": {
                "sLengthMenu": tableLength
            },
            "fnRowCallback": function(nRow, aData, iDisplayIndex, iDisplayIndexFull){
                $(nRow).attr('id', aData[0] + "|<?php echo $this->hostInfo['name']; ?>|<?php echo $this->hostInfo['id']; ?>");
                return nRow;
            },
            "aoColumns": [{"bSearchable": false, "bVisible": false}, null, null, null, null, null, null],

            "fnDrawCallback": function() {
                $( oTableDisk.fnGetNodes() ).click( function () {
                    enableEditable(oTableDisk);
                } );
            }

        });

After rewriting the controller and model to support queries with limit, where and orderby arguments I'm getting data back and it seems like my JSON object is correctly formatted. The problem is that now I don't have pagination functionality. or sorting functionality. The table shows the pagination arrows as sort of greyed out, and clicking the column headers doesn't really do anything. Above the pagination links it says "Showing 1 to 30 of 30 entries (filtered from 483 total entries)" which is correct for the query.

I think this has to do with loading the table after the page has been loaded but I don't know how to fix it.

use browser console to analyze return, should have a start point in it, if it's solid valid json, sounds like server sending same data each time — charlietfl
So it turns out that I didn't need to switch this over to server-side processing. I fixed it by adding the "false" flag to oTableDisk.fnAddData function after the data. and then calling the oTableDisk.fnDraw function after the data was loaded. The other way was redrawing the table after every piece of data was loaded. — Chris Matta

Greg Pettit Greg Pettit · Accepted Answer · 2012-03-08T01:06:40

Your server just isn't sending back the right values for the required server side parameters:

http://datatables.net/usage/server-side

I'm willing to be eleventy dollars that it's here:

iTotalRecords is the absolute total number of records. You probably set a correct value here.

iTotalDisplayRecords - is the total number of records after server side filtering. This is NOT simply the number of records being sent back. The eleventy dollar bet is that you're putting "30" in here with your new controller.

If you're still not sure of the purpose of this second one, remember that there is the option to reduce your query down with searches or other filters. So let's say I have 500 records, and 250 of them have the string "Foobar" in them. If I search for "Foobar" and the server is correctly handling this filter, it should narrow down my available records to 250. Then the server side will actually only send back the records requested according to iDisplayStart and iDisplayLength (which should be automagically provided by DT). In your case, the server would send back the 30 records that it sounds like it's already correctly sending.

jQuery datatables pagination broken after changing to server-side processing

1 Answers