fnAddData("example","row") to a server side processing table

erramun

Hi

I need to add a new row to a server side processed data table which is set up as follows:
[code]
oTable = $('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "/ajax.html",
...
} );
[/code]
In this thread (http://datatables.net/forums/comments.php?DiscussionID=739&page=1) is explained a possible solution for doing the inverse stuff. So I tried to do this:
[code]
//Change temporally the DataTable configuration:
oTable.fnSettings().bProcessing = false;
oTable.fnSettings().bServerSide = false;
oTable.fnSettings().sAjaxSource = null;

/* ****************************************************************
* Add locally new data. Here what I would like to happen is that the
* DataTable adds this new row without asking the server for it, just
* like if a DOM source DataTable would do it.
* **************************************************************** */
oTable.fnAddData([ "example","row" ]);

//Set up again the DataTable as initially configured
oTable.fnSettings().bProcessing = true;
oTable.fnSettings().bServerSide = true;
oTable.fnSettings().sAjaxSource = "/ajax.html";

//And, in a wonderful world, from this point on everything would continue working as a charm...
[/code]
Obviously, this is not working... :( I know that it is quite weird but need this for interaction purposes. Any idea?
Thanks in advance

allan

Pass a second parameter to fnAddData as boolean false ( http://datatables.net/api#fnAddData ) which will stop DataTables redrawing the table (and you don't need to set all the internal variables this way). The reason it doesn't work is that fnAddData will cause a redraw normally, which will effect your adding of a row. Specifically when you redraw the table your row will be lost since the server doesn't know anything about it.

One other option is to use fnOpen and fnClose to open and close rows ( http://datatables.net/examples/server_side/row_details.html ) which has built in support for dealing with server-side processing.

Regards,
Allan

erramun

Ok, don't know why but fnAddData still is not working with bRedraw = false. I'll give more details about the real problem:

What I am really trying to do is to create an endless scrollable DataTable with server side processing, but if possible I don't want to PATCH the library as explained in this other thread: http://datatables.net/forums/comments.php?DiscussionID=1611.

I have a Spring 3 MVC Controller which handles the DataTable ajax requests. Two kind of requests are managed in that Controller:
1- Initial DataTable loading: where first set of 20 rows is loaded with DataTables.
2- Scroll data adding: Every time fnAddAjaxData() is called (scrolling) the Controllers handles a request and returns the next 20 rows for the table. Since the filtering options where initialized in the first call, the params to send to the server are only the iDataStart and iDataLength values.

[code]
var oTable;
var iDefaultDataStart = 0;
var iDefaultDataLength = 20;
var oLoadParams = {
iDataStart: iDefaultDataStart,
iDataLength: iDefaultDataLength,
iLock: false
};

function documentReadyCall(){
oTable = $('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "/ajax.html",
"aaSorting": [ [0,'asc'], [1,'asc'] ],
"sScrollY": "300px",
"bPaginate": false,
"bLengthChange": false,
"bInfo": false,
"fnServerData": function ( sSource, aoData, fnCallback ) {
/* Restart loading data */
fnRestartLoadParams();
/* Add some extra data to the sender */
aoData.push( { "name": "iEndlessScrollStart", "value": oLoadParams.iDataStart } );
aoData.push( { "name": "iEndlessScrollLength", "value": oLoadParams.iDataLength } );
/* */
$.getJSON( sSource, aoData, function (json) {
fnCallback(json);
/* ScrollTop for the server data. */
$('div.dataTables_scrollBody:has(#example)').scrollTop(0);
/* Update the iDataStart value. */
oLoadParams.iDataStart += json.aaData.length;
/* Configure a scroll listener to to the infinite scrolling. */
$('div.dataTables_scrollBody:has(#example)').scroll( function() {
if (!oLoadParams.iLock)
if ( $(this).scrollTop() + $(this).height() > $(oTable.fnSettings().nTable).height() - 100 )
fnAddAjaxData();
} );
} );
}
} );
...
}

function fnAddAjaxData(){
/* Lock infinite scrolling ajax requests. */
oLoadParams.iLock = true;
$.getJSON(
oTable.fnSettings().sAjaxSource + "?iEndlessScrollStart=" + oLoadParams.iDataStart + "&iEndlessScrollLength=" + oLoadParams.iDataLength,
function(json) {
/* whished solution: oTable.fnAddData(json.aaData); */

//temporal PATCH.
fnAddJsonData("example",json.aaData);
//end temporal patch.

/* Update the iDataStart value. */
oLoadParams.iDataStart += json.aaData.length;
/* Unlock infinite scrolling ajax requests */
oLoadParams.iLock = false;
}
);
}
function fnRestartLoadParams(){
oLoadParams.iDataStart = iDefaultDataStart;
oLoadParams.iDataLength = iDefaultDataLength;
}

/* Temporal PATCH for adding JSON data to the table. */
function fnAddJsonData(tableId,aaData){
if ( (tableId != null) && (aaData != null) && (aaData.length > 0) ){
var odd = true;
var tds,td0class, td1class, td2class, td3class;

if ($('#' +tableId + ' > tbody:last > tr:last') != null) odd = !$('#' +tableId + ' > tbody:last > tr:last').hasClass("odd");
if ($('#' +tableId + ' > tbody:last > tr:last') != null){
td0class = $('#' +tableId + ' > tbody:last > tr:last > td:nth-child(1)').attr("class");
td1class = $('#' +tableId + ' > tbody:last > tr:last > td:nth-child(2)').attr("class");
td2class = $('#' +tableId + ' > tbody:last > tr:last > td:nth-child(3)').attr("class");
td3class = $('#' +tableId + ' > tbody:last > tr:last > td:nth-child(4)').attr("class");
}

var tmpTr;
for(i=0;i

allan

Ah - you are trying to add infinite scrolling - very nice :-). It does mean that fnOpen won't be of much use since you'll need to actually add the rows normally.

I wrote an example that used FixedHeader and DataTables 1.6: http://datatables.net/examples/api/infinite_scroll.html . It looks like it works with 1.7 - but doesn't use the new scrolling features, so isn't a particularly great option - but it might give you a few ideas.

I think to be honest, the best way of doing this is not in "user space" as such, but rather in DataTables itself. It's something that I've been considering adding in and this seems like a good prompt to do so. The one thing that is a bit difficult (and is the case in the above example as well) is that each interaction by the user, such as sorting, filtering etc, will need to reset the display start point (for both data index and scrolling) back to zero. This interaction needs to be worked out first I think, before it can be correctly implemented. If you have any thoughts on that, I'd be most interested.

Regards,
Allan

erramun

The example you wrote works like a charm! I took it in account that example and all the considerations pointed by you and dimeg in the "Add data from server while scrolling (Thread ID 1611)". That was very very useful for me.
...
For the DataTables support for this feature, I think the key is to give the internal capability of adding new data redrawing only that new data.
[code]
oTable = $('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "/ajax.html",
"sScrollY": "300px",
"bPaginate": false,
"iDisplayData": 50, //for the first loaded data amount.
/**/ "bEndlessScroll": true,
/**/ "iScrollEventPx": 100, //fire request for new data when 100 pixels table to scroll.
/**/ "iScrollEventIncrement": 40, //add new 40 rows for each scroll event.
} );
...
[/code]
Request to the ajax source always that new filter, sorting... is made, and also each time the requestscrollevent is fired. In the server side, a flag could be added, something like {"bAdd":true}. So in the interla processing of the data this new data is added without removing the old one. (This is just a bit brainstorming)
...
Very important to reset to the start point! The behaviour I'd like to achieve is represented in the code I have posted before. I mean, each sorting, filtering, ... or any event that changes the display of the data should reload the scroll (and the data amount to the initial value) since the user has not asked for all the new given data. In the moment the user starts again scrolling, then more data will be added to the table... and so on till new sorting or filtering is requested.

What I do not want is that, e.g., a user that has been scrolling the table till have downloaded from server 20 + 20 + 20 .... 2000 rows click "sort" and sort only those 2000 rows, or sort requesting other 2000 rows from the server... What I will give him are the first 20 rows for the (filter + sort) he has requested... so if he needs to see more, scroll! My problem is that the way I am showing the new scroll-requested data is quite poor.

//Sorry for my english... I sure you have noticed that it is not my mother language ;)

allan

You've got me thinking about this now - I think it might actually be reasonably easy to add into DataTables. I'll have a bit of a hack later on and let you know what happens... :-)

Regards,
Allan

allan

Here we go - infinite scrolling in DataTables :-). This will be released as part of 1.7.2, but until that time it's available here: http://github.com/DataTables/DataTables/blob/master/media/js/jquery.dataTables.js .

The syntax is quite simple:

[code]
$(document).ready(function() {
$('#example').dataTable( {
"bScrollInfinite": true,
"sScrollY": "200px"
} );
} );
[/code]
There is also a parameter called "iScrollLoadGap" which allows you to define the distance from the end of the scroll before the next page loads (default 100). The number of records which are loaded is the current page size (so that can be controlled using the standard parameters). In fact, the basis essence of this is to use paging, and simply not remove the previously used TR elements - so paging _must_ be enabled, but the controls are not shown (since all the interaction is by scrolling).

This should work with any DataTables' data source, in an unmodified state - all that should be needed is to define bScrollInfinite. I've committed a number of unit tests which check that it is working, but as with all software there could be the odd bug (hopefully not!). If you do find anything odd, please give me a shout so I get fix it before the general release.

Enjoy :-)

Allan

erramun

Great! Thank you very much Allan!

Just having some problems with the iDisplayLength value in the server side. I set iDisplayLength to 20 so the server returns 20 rows each time but always -1 value is given. Am I missing any other parameter? I have solved this in the server side but I am feeling that I am missing some simple concept around this.

Allan, thanks again for the support and integration. cool!

allan

How do you mean -1 is given? Do you mean DataTables sends a value of -1?! It should be that all you need to do is set iDisplayLength :-).

Allan

erramun

That is what I was specting! :-) I have this table, but in the server side I get iDisplayLenthg : -1 value.

[code]
oTable = $('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "/ajax.html",
"aaSorting": [ [0,'asc'], [1,'asc'] ],
"bScrollInfinite": true,
"iDisplayLength": 20, //this is being ignoring don't know why
"sScrollY": "200px",
"bPaginate": false, /* EDIT this was the cause, sorry! */
"bLengthChange": false,
"bInfo": false,
"bAutoWidth": false,
"aoColumns": [
null,
null,
null,
{ "bSearchable": false, "bSortable": false}
]
} );
[/code]


Edit: My fault... "bPaginate": false is not necessary now !!

allan

heh :-). Maybe I should put in a warning about that, or just auto enable it when infinite scrolling. It is possible to use paging with the infinite scroll - but it can go "weird" :-)

Allan

erramun

Hi again Allan

Could it be any hack-possibility to integrate FixedHeader plugin with the Infinite Scroll feature? Tryiend with v2.0.3. but it seems not to be compatible with a scrollable DataTable.

Thanks

allan

Specifically on the left hand column I presume? I've been thinking about that over the last couple of days. I rather suspect that FixedHeader itself isn't suitable for that task, but it could be modified to be so - a new plug-in would come out of if, specifically to give frozen columns when scrolling I think...

Allan

erramun

Indeed, FixedHeader is reporting and alert message telling that it is not compatible with scrolling feature. I am figuring out how to fix this, will try with a custom header div... anyway, I have a question around all this header + scrolling stuff. Have you considered making the tbody scrollable instead the scroll wrapping div? I think that this would make the thead "fixed" for every tables without doing anything more.

As it works now, a full structure of new divs is created for scrolling like:
-dataTables_scroll
·dataTables_scrollHead
·dataTables_scrollBody (here an empty thead and tfoot)
·dataTables_scrollFoot

Wouldn't be more direct to make the tbody style e.g. ="overflow-y: scroll; height: 100px;"?? With this we could let the header and footer as it really is in the original table and avoid to create extra divs for head and foot. What do you think?

allan

Yup - that would make life so much easier. To the point I would start crying if it suddenly became possible, given how painful it's been to getting scrolling working correctly!

The problem that it doesn't work across browsers. In fact I think Firefox is the only one which supports which behaviour. Hence the need to do it the way I've done it.

Allan

erramun

oh... :( you are right about the browsers, I was dreaming...

erramun

Hi Allan

I have a new point I would like to talk with you about scrollinfinite data adding. When the new data is requested and added it seems to be added as a new page, right? So, it happens (or that is what I understand) that the first row of the "to add" data is always considered as "odd". This fact, supposing that the first amount of data rows was odd value, causes two class="odd" rows together to be displayed.

Any quick fix for this? I thought about reseting all the classes again when data is loaded but I am sure there is a more fancy solution possible somewhere...

thanks as always

allan

You are right in how it basically works. This could happen if you have an odd number of row stripe classes, or an odd number of rows being displayed. For even numbers, it should work okay, like this example: http://datatables.net/examples/basic_init/scroll_y_infinite.html . Or perhaps I'm missing something? But yes you are right, there is potential for a bug there.

Allan

erramun

Eco, I have an odd number of rows displaying at the first time. At this moment I am solving it with this... but I suppose the best solution is to manage this inside datatables, maybe reseting the styles of the entire table insted of the new "page" when bScrollInfinite is true.

[code]
...
"fnServerData": function ( sSource, aoData, fnCallback ) {
$.getJSON( sSource, aoData, function (json) {
fnCallback(json);
resetOddsAndEvens();
} );
}
...
function resetOddsAndEvens(){
$("#example tbody tr:odd").attr("class","odd");
$("#example tbody tr:even").attr("class","even");
}
[/code]