Reloading the data for a datatable, possibly with a different data source, on a timer.

Taylor514ce

The way I did this with the previous version was to:

1. Assign my datatable object to a variable.

  myTable1 = $('#statusLog').dataTable( {

    "bProcessing": false,
    "bJQueryUI": true,
    "sDom": '<"H">t<"F"fT>',
    "sScrollY": $(document).height() - 260,
    "sAjaxSource": "http://<!--SERVERURL-->/Templates",
    "bPaginate": false,
    "bFilter": true, 
    "bAutoWidth": false,
    "aoColumns": [{ "sWidth": "240px"}, { "sWidth": "400px" }, { "sWidth": "300px" }, null, {"sWidth": "40px"} ],
    "aaSorting": [[4,'desc']],
    "oTableTools": {
      "sSwfPath": "http://<!--SERVERURL-->/loadTableTools",
      "aButtons": [ 
      "copy",
      "csv",
      { "sExtends": "pdf",
        "sPdfOrientation": "landscape",
        "sPdfMessage": "PlanetPress Template List"
      },
      "print" ]
    } 
  } );

1. Define a "fnReloadAjax" function to go get the new data.

$.fn.dataTableExt.oApi.fnReloadAjax = function ( oSettings, sNewSource, fnCallback, bStandingRedraw )
{
  if ( typeof sNewSource != 'undefined' && sNewSource != null )
  {
    oSettings.sAjaxSource = sNewSource;
  }

  this.oApi._fnProcessingDisplay( oSettings, true );
  var that = this;
  var iStart = oSettings._iDisplayStart;
  var scrollPos=$(".dataTables_scrollBody").scrollTop();

  oSettings.fnServerData( oSettings.sAjaxSource, [], function(json) {
    /* Clear the old information from the table */
    that.oApi._fnClearTable( oSettings );

    /* Got the data - add it to the table */
    var aData =  (oSettings.sAjaxDataProp !== "") ?
    that.oApi._fnGetObjectDataFn( oSettings.sAjaxDataProp )( json ) : json;

    for ( var i=0 ; i<aData.length ; i++ )
    {
      that.oApi._fnAddData( oSettings, aData[i] );
    }

    oSettings.aiDisplay = oSettings.aiDisplayMaster.slice();
    that.fnDraw();

    if ( typeof bStandingRedraw != 'undefined' && bStandingRedraw === true )
    {
      oSettings._iDisplayStart = iStart;
      that.fnDraw( false );
    }

    $(".dataTables_scrollBody").scrollTop(scrollPos);

    that.oApi._fnProcessingDisplay( oSettings, false );

    /* Callback user function - for event handlers etc */
    if ( typeof fnCallback == 'function' && fnCallback != null )
    {
      fnCallback( oSettings );
    }
  }, oSettings );
}

1. Define a function that calls the fnReloadAjax() function.

function loadLog() {
  myTable1.fnReloadAjax();
}

1. Set a timer to call the "loadLog" function...

setInterval (loadLog, <!--LOGINTERVAL-->);

Is this still the proper approach? My central question is, under the latest/current version of Datatables, how do you set an interval to fetch new data? And to modify the data source url?

kthorngren

And to modify the data source url?

I've not done this before but it looks like you can use ajax.url().load() to update the URL and load the new data.

how do you set an interval to fetch new data?

This is more a Javascript question and how you choose to request the new data. I have a Datatable that shows updated log information every 5 seconds. I don't reload the table I just get the new log entries every 5 seconds. Here is my code:

function get_log() {
    var data = logTable.row(':last').data()  //get the ID from the last row
    pkid = (typeof data === 'undefined') ? 0 : data.pkid || 0;
    $.ajax({
        url: "get_log",   //sql query gets all log entries greater than the current ID
        data: {
            'current_pkid': pkid
        }
    }).done(function (data) {
        append_to_log(data);   //Append new log entries to table
    }).always(function () {
        setTimeout(get_log, 5000);   //Wait 5 seconds 
    })
}

It relies on the table having a hidden column with the auto increment ID for each log entry. It uses the last (highest) ID as a parameter for the ajax request. The SQL query just returns the records with a higher ID or a defined number of rows if the ID parameter is 0. The ID is 0 when the page first loads and there are no log entires displayed.

In my append_to_log function I use rows.add(data).draw(false); to add the new log entires and keep the display (draw(false)) where its at, in case someone is reading the logs.

HTH,

Kevin

Taylor514ce

Kevin,

I love the idea of simply querying for new records. That approach will work very well for one of my tables. Thanks!

A different table might have changed field values, a STATUS value for a given record. This approach of selectively adding/deleting rows I imagine could be extended to replacing specific rows, but that seems much more complex than simply replacing the entire table, somehow. I'm digging through the manual to try to discover if there's a simple API method to call for this...

OFF TOPIC: I'd love to work out a system that would compare two data sets, and update the datatable's data set with any changes to the "core" data set. Then through ajax update the core dataset periodically with calls to the server/database. If there any changes, fire an event, this event would update datatable. Options to replace/redraw the entire table or just update specific rows/fields...

kthorngren

A different table might have changed field values, a STATUS value for a given record

I have a second table on the same page which is a status dashboard. It has around 30 rows. I use a similar function as my get_log function but get all the records then use clear() to clear the tables and rows.add() to add the updated status. Since its a small table i figured this was the most efficient.

Kevin