Examples of DataTable Search enabled with Server Side (using pagination)

rlongoDev

I have been able to set up a DataTable using server side Ajax call and the data returned from a Spring Rest Controller. I've POJOs created that correctly parse out the Request and Response parameters that DataTable sends and expects back. Table loads up with data, the page navigation works well, no issues there.

However, I'd like to set up Search for the table, and not finding very good examples for how to do this with a server side scenario. I'm assuming the search has to be done Server side and then returned back paginated results, but cant find a good example for that..

I've gotten as far as the following to test that I can call the search API and the table redraws. First issue, using 'this.value' did not work as parameter for the table.search() function, I had to use filter's id .val(), and then the draw() does seem to be called with each key entry. But all it's doing is making a call the Ajax call for the current page of data. I admit I am confused about what kind of behavior I should have expected without coding for a server side search.

Also, there is a difference between filter and search. Wouldn't the search API at a minimum filter for the currently displayed page of records?

How would I either 1) modify the current Ajax call, or, 2) add a new Ajax call, that would populate the table when search was invoked? If the search returns more records than the table length, I'd like to ensure pagination for the search results as for the non-searched data results.

Also, how would I design and code to refresh back to the original table state without simply reloading the current page (which I wouldn't want to do as I plan to have form elements that would clear with a page reload)? If I use the same Ajax call for search and initial load, I could imagine simply sending the original Post with no search values. But again, any examples would be very helpful!

$(document).ready(function() {
    var table = $('#attributeTable').DataTable( {
        pageLength : 15,
        "processing": true,
        "searching": true,
        columnDefs:    [
            { className: "dt-body-left", targets: "_all" },
            { className: "dt-head-left", targets: "_all" },
        ],
        "columns": [
            { "data": "id"},
            { "data": "name"},
            { "data": "hasvalue"},
            { "data": "attributeByIdAttribute"}
        ],
        "serverSide": true,
        "ajax": {
            url: "/rest-api/loadAttrDT",
            type: "POST",
            data: function (data) {
                return JSON.stringify(data);
            },
            dataType: "json",
            processData: false,
            contentType: "application/json;charset=UTF-8"
        },
    } );

    $('#attributeTable_filter').on( 'keyup', function () {
        console.log(table);
        table.search( $('#attributeTable_filter').val() ).draw();

    } );
} );

kthorngren

The first question is do you really need Server Side Processing? See this FAQ.

First issue, using 'this.value' did not work as parameter for the table.search() function, I had to use filter's id .val(),

That would be a Javascript issue. Did you valid what this.value contained?

then the draw() does seem to be called with each key entry. But all it's doing is making a call the Ajax call for the current page of data

The Server Side Processing doc explains the parameters send and received. You can see this by looking at one of the Server Side Processing examples. Use the browser's network inspector to see the request. If using Chrome scroll to the bottom of the Headers tab. Here you can see the parameters sent and will something like this:

....
start: 0
length: 10
search[value]: test
search[regex]: false

The Ajax tab of the example shows the response or you can look in the browser's netwwork inspector.

Also, there is a difference between filter and search. Wouldn't the search API at a minimum filter for the currently displayed page of records?

No. It is expected that all the data processing is done by the server script. There is no searching, sorting and paging performed at the client when server side processing is enabled.

How would I either 1) modify the current Ajax call, or, 2) add a new Ajax call, that would populate the table when search was invoked?

The same ajax config is generally used.

If the search returns more records than the table length, I'd like to ensure pagination for the search results as for the non-searched data results.

It is expected that the server script returns only the number of rows requested. This is controlled by either the pegeLength option or using the page length select input. For a SQL type DB I think its recommended to use LIMIT and OFFSET. You can find the Datatables example PHP script here which might give you some ideas of what to do.

Also, how would I design and code to refresh back to the original table state without simply reloading the current page

Use ajax.reload(). See this page for a list of ajax related APIs.

Hope this answers your questions.

Kevin

rlongoDev

Excellent answers thank you, I reviewed the Request object parameters in detail and I do see now where the columns are searchable and the value to search can be set. So to summarize your answer and set up my homework, am I correct with the following plan?

1) Stay with client-side unless need for large record sets - But would that threshold be thousands, hundreds of thousands, millions of record?
2) use same Ajax call when doing a server side call with search
a) set the columns attributes for the correct search SQL
b) search results still pageable, shouldn’t change behavior
3) To remove search filter and reload table use Ajax.reload to redraw table with no search criteria (columns searchable:false)

rlongoDev

Actually rereading the manual, see that for <10k rows, client side fine, >100krows consider the server side option