Remove row on Server-Side processed table best practice.

majic

I've looked around the forum and a bit in the code, but can't find a good answer. I have a server-side processed table that works great displaying all my data and buttons. I've added a button to each row to remove the row from the result set on the server side with an ajax post. On success I fadeOut the row, and for performance I'd like to update the pager indices and page elements to show one less result WITHOUT issuing another ajax query for all new table data.

Example (the last row remains on page [2] while user deletes rows on page [1], then the user requests next page, an datatable data query is made, and the datatable redraw reconfigures pager):

<10 row table/> Showing 1 to 10 of 11 entries ... Page [prev][1][2][next]

After a successful click of the ajax row delete button, my row fades out and the table appears to have one less element. I want to update the page information elements and oSettings indices without redrawing the whole table:

<9 row table/> Showing 1 to 9 of 10 entries ... Page [prev][1][2][next]

Repeating the row removal on the same page, the pager info should continue to reduce the number of rows on display:

<8 row table/> Showing 1 to 8 of 9 entries ... Page [prev][1][2][next]
...
<1 row table/> Showing 1 of 2 entries ... Page [prev][1][2][next]
<0 row table/> Showing 0 of 1 entries ... Page [prev][1][2][next]

Finally clicking a pager button will query the server for new server-side data and redraw the whole table with new table data & counts. Ideally the current page button will be active and function to reload the current page. It could have a modified style to represent the current page.

Clicking a larger pager number than the current page or pager [next] should send appropriate iDisplayStart to server knowing the current page no longer contains iDisplayLength rows.

Once the user pages, datatables can reconfigure the pager:

<1 row table/> Showing 1 of 1 entries ... Page [prev][1][next]

allan

> WITHOUT issuing another ajax query for all new table data.

If you are using server-side processing, you can't do that (unless you use caching - for example: http://datatables.net/release-datatables/examples/server_side/pipeline.html - but note you would need to invalidate your cache since you've removed a row).

The key thing to remember with server-side processing is that effectively no data is held on the client-side - it is only displayed. If you want to redraw the table, you need an Ajax request. There are ways to address this such as the caching above, but it can get very messy when you need to modify the cache, as you would here.

Allan

majic

Thanks Allan, I'm using datatables with bootstrap v2 as you've shown in the blog, and I think I've hacked what I wanted in successfully without changing dataTables.js. I only have small changes to the dataTables.bootstrap.js and my table registration code. It's as follows for others to try:

Table Setup:

[code]
var dtParams = {
// setup for server-side processing...
// +
fnPreDrawCallback: function( oSettings ) {
// reset rows deleted count on draw
if (oSettings._user) {oSettings._user.iRowsDeleted = 0; }
},
fnInfoCallback: function(oSettings, iStart, iEnd, iMax, iTotal, sPre) {
iEnd -= oSettings._user.iRowsDeleted;
iMax -= oSettings._user.iRowsDeleted;
iTotal -= oSettings._user.iRowsDeleted;

// Tweak as needed - I copied your original with more processing - but basically this...
return oSettings.oLanguage.sInfo.
replace('_START_', oSettings.fnFormatNumber( iStart )).
replace('_END_', oSettings.fnFormatNumber( iEnd )).
replace('_TOTAL_', oSettings.fnFormatNumber( iTotal ))+
oSettings.oLanguage.sInfoPostFix;
}
};

var oTable = $(idSelector).dataTable(dtParams);

var oSettings = oTable.fnSettings();
oSettings._user = { iRowsDeleted: 0 };

[/code]


dataTables.bootstrap.js modifications:

[code]
$.extend( $.fn.dataTableExt.oPagination, {
"bootstrap": {
"fnInit":
[/code]

modify the local fnClickHandler to compute the new oSettings._iDisplayStart and redraw on [Previous] / [Next] pager clicks:

[code]
var fnClickHandler = function ( e ) {
e.preventDefault();

if (e.data.action == "previous") {
oSettings.oApi._fnPageChange(oSettings, e.data.action);
}
else if (e.data.action == "next") {
if ( oSettings._iDisplayLength < 0 ) {
oSettings._iDisplayStart = 0;
}
else {
// Compute iNextStart accounting for deleted rows
var iNextStart = oSettings._iDisplayStart
+ oSettings._iDisplayLength
- oSettings._user.iRowsDeleted;

var iRecordsDisplay = oSettings.fnRecordsDisplay()
- oSettings._user.iRowsDeleted;

/* Make sure we are not over running the display array */
if (iNextStart < iRecordsDisplay ) {
oSettings._iDisplayStart = iNextStart;
}
}
}
fnDraw( oSettings );
};
[/code]


[code]
$.extend( $.fn.dataTableExt.oPagination, {
"bootstrap": {
"fnUpdate":
[/code]

modify the anonymous fnClickHandler for the [1][2] pager anchors to compute the new oSettings._iDisplayStart and redraw:

[quote]This click handler modified from original post to fix pager issue along with fnPagingInfo function update in below post: [/quote]

[code]
.bind('click', function (e) {
e.preventDefault();

var iPageReq = parseInt($('a', this).text(),10) - 1;
var iPageDiff = iPageReq - oPaging.iPage;

if (iPageDiff < 0) { oSettings._iDisplayStart += (iPageDiff * oPaging.iLength); }
if (iPageDiff > 0) { oSettings._iDisplayStart += (iPageDiff * oPaging.iLength) - oSettings._apt.iRowsDeleted; }

if ( oSettings._iDisplayStart < 0 ) { oSettings._iDisplayStart = 0; }

fnDraw( oSettings );
} );
[/code]

Finally in your row delete success callback count the row as deleted and redraw the table info:

[code]
var rowDeleteSuccess = function(data, textStatus) {
var oTable = ...;
var oSettings = oTable.fnSettings();

// Track deleted rows
++oSettings._user.iRowsDeleted;
oTable._fnUpdateInfo();

// Find row and fadeOut
var nTr = $('#dt_row_' + data.id);
nTr.fadeOut('fast');
}
[/code]

majic

found an issue in [1][2] pager click handler. hopefully, back with a fix shortly.

majic

update to above to fix paging issue:

modify the dataTables.bootstrap.js fnPagingInfo from the Blog post on integrating with bootstrap to:

[code]
$.fn.dataTableExt.oApi.fnPagingInfo = function (oSettings)
{
var iPage = Math.ceil( oSettings._iDisplayStart / oSettings._iDisplayLength );
var iRecordsAbove = oSettings.fnRecordsDisplay() - oSettings._iDisplayStart;
var iPagesAbove = Math.ceil( iRecordsAbove / oSettings._iDisplayLength );

return {
iStart: oSettings._iDisplayStart,
iEnd: oSettings.fnDisplayEnd(),
iLength: oSettings._iDisplayLength,
iTotal: oSettings.fnRecordsTotal(),
iFilteredTotal: oSettings.fnRecordsDisplay(),
iPage: iPage,
iTotalPages: iPage + iPagesAbove
};
}
[/code]

majic

This seems to work and covers my initial post about what I wanted. Could you see something like this functioning as a plug-in? If so I might try to write it up as a plugin instead of the hack it currently is.

allan

Plug-ins are alway good (even in your own code since it makes to more modular - although it of course possible to go overboard...) :-). Surprised that fnPagingInfo needs to be updated for bootstrap though - that should be pagination independent.

Allan

majic

Yes the fnPagingInfo is pagination independent. and not tied to bootstrap. It's there for me since I implemented the datatables for bootstrap code from your blog post. I put everything I used from that post in a file I named dataTables.bootstrap.js.

I'm not that familiar with everything here, maybe it should be in something named dataTables.fnPagingInfo.js like the API plugin.