Search API not working while using server-side processing

SKC

I was initially using client-side processing and had implemented the Search API mentioned in the following link :
https://datatables.net/examples/api/regex.html
The search filters for each column worked fine back then.

Now I have switched to server-side processing. But now the search-api doesn't seems to work.
Currently I'm fetching data from SQL DB. While searching through the seach-api filters the data remains unaffected.
The default Search box is working fine.

Is there any way to solve this issue?

tangerine

With server-side processing, searching would be carried out by your server-side processing code - i.e. the code which is fetching data from the db.

Check the "server-side" code in the "Examples".

SKC

Can anyone share how to implement Search API ( similar to this https://datatables.net/examples/api/regex.html) while using server-side processing.

Just as I've mentioned above my data is stored in SQL Server DB.

SKC

Thanks for the reply tangerine... But I couldn't get it done

tangerine

But I couldn't get it done

What does that mean? Are you learning from the example I pointed to? How far have you got?

You have to provide a lot more information if you expect some help.

allan

I'm not aware of anyone having implemented regular expression searching with MySQL for DataTables server-side processing. I don't doubt that has been done, but I don't have the code for it. The main problem with it is that when you are dealing with enough records to warrant server-side processing, performance of regex becomes an issue.

You would need to refer to the MySQL regex manual and update your server-side processing script to support that.

Allan

SKC

Actually I'm using SQL Server DB.

I've implemented a MVC architecture.

Here's my JS code :

$(document).ready(function () {

    $('#searchResult').DataTable(
         {
               "processing": true,    // to show progress bar
             "serverside": true,    // enables server-side processing
             "filter": true,            // this is for disable filter (search box)
             "ordermulti": false, // disables multiple column at once
             "ajax":
             {
                 "url": "/ChopRequest.Web/Search/GetSearchResults",
                 "type": "POST",
                 "datatype": "json"
             },

             "columns":
             [
                     { "data": "ChopRequestNo" },
                     { "data": "Description" },
                     { "data": "CreatedBy" },
                     { "data": "CompanyCodeName" },
                     { "data": "DocTypeName" },
                     { "data": "DocTypeDescription" },
                     { "data": "ReferenceNo" },
                     { "data": "Status" },
  ]
         });

SKC

Here's my script which gets data from the UI :

function searchFilters() {
    var search = {
        ChopRequestNo: $('#txtChopRequestNo').val(),
        ReferenceNo: $('#txtReferenceNo').val(),
        CreatedBy: $('#txtCreatedBy').val(),
        Chopper: $('#txtChopper').val(),
        Manager: $('#txtManager').val(),
        LeagalApprover: $('#txtLeagalApprover').val(),
        FinanceApprover: $('#txtFinanceApprover').val(),
        SystemAdmin: $('#txtSystemAdmin').val(),
        VP: $('#ddlVP').val(),
        DocumentType: $('#ddlDocumentType').val(),
        CompanyCode: $('#ddlCompanyCode').val(),
        CreatedOn: $('#createdOn').val(),
        CompletedOn: $('#completedOn').val(),
    };

    $.post(postTo,
        {
            obj: JSON.stringify(search),
        },
        function (data) {
            if (data.Result == "OK") {
                alert("Search Successfull!");
            } else {
                alert("Error While Searching");
            }
        }, "json");

}

Now how do I proceed further??

allan

Here is the Microsoft documentation for SQL server's regex.

Since you are using server-side processing, you would need to modify your GetSearchResults script to do the regex search.

Allan

ganderson

I see a lot of people struggling with ServerSideProcessing and the need for alterhnatives to (option1|option2|option3) type regular expressions. Could you possible show an example of how one would build a result table from successive table.column().search() results before issueing a table.draw() command?

allan

Do you mean like a WHERE ... OR ...? The built in server-side processing for the Editor scripts won't do that I'm afraid, but you could use a custom condition (Where() and OrWhere()) to do exactly that based on a split string.

But I'm not certain that is what you are looking for?

Allan

ganderson

Hmmmm, interesting. I will try this over the weekend and summarize the application for others thereafter. Thanks Allan - I hope you enjoy your weekend.