Get all data when using server side processing

aaronloes

We're experimenting with data tables on a new project and due to the large amount of data we are working with, we are using the server side processing of data tables and the scroller plugin. You can sort the columns and filter the records. This is working pretty great, but right now we're trying to figure out how to get all the data from the server through data tables. I would have expected that fnGetData() would have made another query to the server to get all the records (filtered by the users input) but it only appears to return the records that are currently being loaded. Is there a way, given our configuration of dataTables, to get it to retrieve all the records from the server. I would think this wouldnt be very difficult. Just recreate the aoData object sent to the server, but with an iDisplayLength parameter missing or set to -1 or size of all records.

Something like this would be very useful; as well as a fnGetAllData() that fetches all data ignoring filter input.

allan

> would have expected that fnGetData() would have made another query to the server to get all the records (filtered by the users input) but it only appears to return the records that are currently being loaded.

That is correct. Perhaps I'm missing something, but if you require the ability to get all data, why not just disable server-side processing, and get all the data? With deferred rendering, its normally the get that is the bottle neck.

Allan

aaronloes

We're doing server-side processing for performance reasons. We were finding to be very slow to process 1000+ records. The performance wasn't necessarily with dataTables but possibly something else; either our client side code or the server side response. In either case, am looking to fetch the data that the dataTables appears to be representing. I'm currently looking at writing my own plugin for this. Just wondering if this was already out there.

allan

No there isn't anything currently available, but as you say, it should just be an Ajax call to the source that DataTables is using itself.

Allan

aaronloes

This seems to be working. I'm also looking for a method to get the data.

UPDATE: original code I posted had some issues, I've replaced it with the updated.

[code]
/**
* fires the call back passing it all data related to the dataTable. This is different
* from fnGetData in that if the dataTable is bound to server side data, it will call
* to get the representative data from the server before returning. If the dataTable is
* not bound to server side data, it will call the call back passing all the data.
*
* @param {Object} oSettings
* @param {Object} callback
*
* @author Aaron Loes
*/
$.fn.dataTableExt.oApi.fnGetAllRecords = function(oSettings, callback) {
var aoDataReplacements = {
"iDisplayStart": 0,
"iDisplayLength": -1,
"sSearch": ""
};

if (oSettings.oFeatures.bServerSide) {
oSettings.iDraw++;

var aoData = this.oApi._fnAjaxParameters(oSettings);
this.oApi._fnServerParams(oSettings, aoData);

aoData = $.grep(aoData, function(entry) {
if (aoDataReplacements.hasOwnProperty(entry.name)) {
entry.value = aoDataReplacements[entry.name];
}

return entry;
});

oSettings.fnServerData.call(
oSettings.oInstance,
oSettings.sAjaxSource,
aoData,
function(json) {
callback(json.aaData);
}, oSettings );
} else {
callback(this.oApi._fnGetDataMaster(oSettings));
}
};
[/code]

aaronloes

to call this, use:
[code]
$("#myDataTable").dataTable().fnGetAllRecords();
[/code]

allan

Nice one - thanks for sharing this with us :-)

Allan

aaronloes

Here is the plugin to get filtered data. There is no existing method for this in dataTables for whatever reason.

UPDATE: fixed bug

[code]
/**
* fires the call back passing it all the filtered data related to the dataTable.If
* the dataTable is bound to server side data, it will call to get the representative
* data from the server before returning. If the dataTable is not bound to server side
* data, it will call the call back passing all the data.
*
* @param {Object} oSettings
* @param {Object} callback
*
* @author Aaron Loes
*/
$.fn.dataTableExt.oApi.fnGetDisplayRecords = function(oSettings, callback) {
var aoDataReplacements = {
"iDisplayStart": 0,
"iDisplayLength": -1
};

if (oSettings.oFeatures.bServerSide) {
oSettings.iDraw++;

var aoData = this.oApi._fnAjaxParameters(oSettings);
this.oApi._fnServerParams(oSettings, aoData);

aoData = $.grep(aoData, function(entry) {
if (aoDataReplacements.hasOwnProperty(entry.name)) {
entry.value = aoDataReplacements[entry.name];
}

return entry;
});

oSettings.fnServerData.call(
oSettings.oInstance,
oSettings.sAjaxSource,
aoData,
function(json) {
callback(json.aaData);
}, oSettings );
} else {
var aData = [];
for (var i = 0 ; i < oSettings.aiDisplay.length; i++) {
aData.push(oSettings.aoData[oSettings.aiDisplay[i]]._aData);
}

callback(aData);
}
};
[/code]

and here's how you call it

[code]
$("#myDataTable").dataTable().fnGetDisplayRecords();
[/code]