How to refresh ajax source when not using server-side processing?

rewen

Hi guys,

I have a table that is was originally all server-side from mysql/php in json format, but searching this table is much too slow given that nearly every column is formed from a complex subquery and many joins. This table takes nearly 10 seconds to load and another 10-15 seconds for each search on it, with a new search being requested each time a key is pressed in the search box. Searching for something over a few letters causes the server to bog down and even stop replying sometimes. Even if I were to make a delayed search I would not be happy with it taking 10+ seconds, and the potential of multiple users doing searches at the same time is high.

So, my solution is to make the table load from the source once, and then perform any searches locally (there are only 200-300 rows). This works great except that I have additional custom filters which I send through fnServerData, and these must be done server-side.

My question is what do I need to do when those custom filters (input box and select box) are changed, in order to refresh the data source? fnDraw() doesn't seem to do reload the source and I haven't had luck finding anything in the documentation, this forum, or Google.

Many thanks in advance!

rewen

After some more searching I think I found what I need, fnReloadAjax(), but it's not working for me. It doesn't seem to like my extra aoData variables:

[code]
Uncaught TypeError: Cannot call method 'push' of null
inventory_report_table.$.dataTable.fnServerDatainventory_report.js:66
$.fn.dataTableExt.oApi.fnReloadAjaxinventory_report.js:13
j.fn.dataTable.bjquery.dataTables.min.js:38
(anonymous function)inventory_report.js:97
c.event.handlejquery-1.4.4.min.js:63
c.event.add.h.handle.o
[/code]

Here is my code:

[code]
var inventory_report_table;

$.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;

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

/* Got the data - add it to the table */
for ( var i=0 ; i

rewen

I think I have it working now. I found some other forum posts where people inserted aoData variables into their fnReloadAjax function. I thought that fnReloadAjax would reuse fnServerData as defined with the dataTable, but I guess not.

I noticed that there are a few varying fnReloadAjax functions out there so I used the one from the API plugins page and modified it slightly. Here is what I ended up with:

[code]
$.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;

// this line had to be added in otherwise '=undefined=undefined..' gets sent via GET...
this.fnClearTable( this );

// this is where I added in my custom vars
oSettings.aoData.push(
{ "name": "filter_date", "value": $('#filter_date').val() },
{ "name": "filter_categories", "value": $('#filter_categories').val() }
);

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

/* Got the data - add it to the table */
for ( var i=0 ; i

rewen

OK So I played some more and realized that my aoData variables are getting sent twice, which means that my fnServerData function IS being reused properly. So I took out the aoData.push lines from fnReloadAjax() and everything still works fine. So now my fnReloadAjax() function is basically unmodified (other than this.fnClearTable( this );), so I am stumped as to why it just works now and didn't work at the beginning. Perhaps I used a bad fnReloadAjax() function from someone else's post.

dogmeat78

This was awesome thanks!! the code:

[code]
// this is where I added in my custom vars
oSettings.aoData.push(
{ "name": "filter_date", "value": $('#filter_date').val() },
{ "name": "filter_categories", "value": $('#filter_categories').val() }
);

[/code]

was what I was missing.. and couldn't figure out how to put in new parameters when I refreshed the table. I figure a funtion like oSettings.fnRowCallback would exist to repopulate the parameters. But whatever I'll take this.

So now I just run:

[code]
var oSettings = datatablevariable.fnSettings();
oSettings.aoData.push(
{ "name": "SD", "value": sStartDate },
{ "name": "UID", "value": iUserID }
);
datatablevariable.fnReloadAjax();
datatablevariable.fnDraw();
[/code]

and it works perfectly.. ugh this was killing me for 2 days.. thanks!!