Server-side processing with Open Data Protocol (OData)

Kriil

The project I am working on is using Open Data Protocol (OData) for its web service. The resulting object must therefore be formatted to the specification dictated by the protocol. However, when using server-side processing, DataTables expects the data retrieved from the server to be formatted in a specific fashion as detailed at http://datatables.net/usage/server-side Unfortunately, these two formats are different.

I'm using javascript and making an ajax call to retrieve the data from the server, and I would like to intercept the data and convert it to the proper format before passing it on to DataTables. Can this be accomplished?

allan

If OData provides the components needed by DataTables for server-side processing ( http://datatables.net/usage/server-side ), then you could use fnServerData to transform the data from one format (oData) into another (DataTables).

I'd be surprised if OData does provide everything DataTables needs (happy to find out that is does if that is the case!)...

Do you really need server-side processing? How many rows are you working with?

Allan

Kriil

It's not so much the number of records (which can be in the tens of thousands for some queries), but the amount of data being transferred at any time. We have a constraint to reduce that amount. So server-side processing seems to be solution and since the oData protocol provides for skiptokens, we can use that data to translate the oData object into something that Datatables can understand.

I tried using fnServerData to transform the ajax response as follows:

[code]
oTable = $('.js-bankdatatable').dataTable( {
"iDeferLoading": 0
, "bServerSide": true
, "fnServerData": function( sUrl, aoData, fnCallback, oSettings ) {
oSettings.jqXHR = $.ajax( {
"url": sUrl,
"data": aoData,
"success": fnCallback,
"error" : function(){},
"dataType": "jsonp",
"cache": false,
"dataFilter" : convertJsonptoDatatableObject(data)
} );
}
, "sAjaxSource": "someurl?$callback=resultsCallback"
, "sAjaxDataProp": "aaData"
, "bRetrieve":true
, "bDestroy":true
} );
[/code]

The method used by the dataFilter callback in the ajax call converts the data properly when bServerSide is set to false, but as soon as I set it to true, the javascript stops running somewhere in dataTable(). Breakpoints show that the sAjaxSource is being used, but that is all I can seem to determine.

Does this look like the correct solution or is my understanding of how datatables works off? I'd appreciate any help you could offer.

Kriil

allan

Your `convertJsonptoDatatableObject` method will most certainly need to know the parameters DataTables sent to the server (specifically so it can set sEcho appropriately). What does the `convertJsonptoDatatableObject` method do exactly? Also. do you really mean to assign the result of `convertJsonptoDatatableObject` to dataFilter? Do you not want `convertJsonptoDatatableObject` itself assigned to it?

Allan

Kriil

That is my main problem. I don't know how or where to assign the result of any conversion or where the appropriate place to start the conversion.

dataFilter receives the jsonp object, which I can convert but it's a pain. Is there a better place to do this?

oData is just a specification of JSON, so the .ajax call returns a JSON object with a field called 'results'. All i need to do is take 'results' and put it into a new object that server-side processing recognizes. I put 'results' into 'aaData', and then set 'sEcho', 'iTotalRecords' and 'iTotalDisplayRecords'. I've created the object and inspected it in the Firebug console with server-side processing turned off. It looks well formed and contains the proper data.

The problem is when I set bServerSide to true. Looking through the datatables code I don't see anywhere oSettings.fnServerData.call(...) is called when bServerSide is true. Any ideas?

Thanks again for your help

allan

> dataFilter receives the jsonp object, which I can convert but it's a pain. Is there a better place to do this?

Personally I'd probably do it in the success callback. Transform the object returned and then pass it in to the `fnCallback` function there.

Allan

Kriil

That was one of the first things I tried but it wasn't working. It appears that fnServerData isn't being called when bServerSide is true.

[code]
, "bServerSide": false
, "fnServerData": function( sUrl, aoData, fnCallback, oSettings ) {
oSettings.jqXHR = $.ajax( {
url: sUrl,
dataType: "jsonp",
cache: false,
success: alert("Success"),
complete: alert("Complete"),
error: alert("Error")
} );
}
[/code]

This call works and I see alerts as expected. If the only change I make is setting bServerSide to true, no alerts are seen.

Also in the DataTables code:

[code]
/* if there is an ajax source load the data */
if ( oSettings.sAjaxSource !== null && !oSettings.oFeatures.bServerSide )
{
var aoData = [];
_fnServerParams( oSettings, aoData );
oSettings.fnServerData.call( oSettings.oInstance, oSettings.sAjaxSource, aoData, function(json) {

......

[/code]

This appears to be the only place oSettings.fnServerData is called (except in _fnAjaxUpdate) and this is only called when bServerSide is false

Am I missing something here?

allan

> That was one of the first things I tried but it wasn't working. It appears that fnServerData isn't being called when bServerSide is true.

That's not possible - it must be going through fnServerData. Otherwise this example (along with a raft of my unit tests) wouldn't work: http://datatables.net/release-datatables/examples/server_side/jsonp.html .

This is the function that does the call when server-side processing is enabled ( https://github.com/DataTables/DataTables/blob/master/media/src/core/core.ajax.js#L9 ) - not the code you noted, which as you say is not for server-side processing.

> success: alert("Success"),

That looks really odd to me. Do you actually want:

[code]
success: function () {
alert("Success");
}
[/code]

!

Otherwise the alert is executed when the code is loaded - not on the callback...

Allan

Kriil

Yes, of course. It's been along day and tracing through the code a line at a time was only misleading me.

The culprit turned out to be [code]"iDeferLoading": 0[/code]. Which means DataTables was doing exactly what it was supposed to...load 0 records on the first call.

I will continue trying out your original suggestion of converting the oData to something that DataTables can use inside of the success callback of fnServerData.

Thanks a lot for your help. hopefully I'll have a solution soon.

Kriil

SOLUTION:

I finally managed to get this working. Here is my data table decalaration:

[code]
var oTable = $('#example').dataTable( {
"bProcessing": true
, "bServerSide": true
, "fnServerData": function( sUrl, aoData, fnCallback, oSettings ) {
oSettings.jqXHR = $.ajax({
url: sUrl,
data: "$top=5&$skip=5",
dataType: "jsonp",
//convert the data in success callback
success: function(data) {
var result = resultsCallback(data);
fnCallback(result);
},
error: function(XHR, textStatus, errorThrown){
alert("An error has occured (" + textStatus + " " + errorThrown +")" );
},
});
}
, "sAjaxSource": function(){
var filterStr = buildSearchFilter();
return "www.some.domain.com/projectName/ODataObjectName?$format=json&$inlinecount=allpages$filter=" + filterStr + "&$callback=?";
}
} );
[/code]
Our implementation of oData web service provides $skip and $top variables, which I am passing through the 'data' property in the .ajax call. This will need to be changed to be dynamic (instead of passing in 5)

The oData object is converted into what DataTables expects in the success callback. Note that sEcho is set to a counter based on the number of searches run. I don't know if this will cause any problems, but the oData protocol doesn't have anything like this.

[code]
function resultsCallback(data) {
return {
"sEcho": numberOfSearchesRun++,
"iTotalRecords": +data.d.__count;,
"iTotalDisplayRecords": data.d.results.length,
"aaData": data.d.results
};
}
[/code]