Server side search

rf1234

This is not a question but rather to share my experience with server side search:

I need to use server side processing for a large table and had some difficulties getting the search for this working properly. The reason for this is that using server side the search is performed on the database values not on the values after get formatting. If you do the search on the client side everything works as expected because the search is performed on the values coming from the server after get formatting.

The only sensible way I found to fix this was to change the search string on the client side so that it matches with the database values. This is highly relevant for dates and for numbers. While the database holds dates in YYYY-MM-DD format or even worse as a timestamp users will want ot search for DD/MM/YYY or DD/MM/ or DD/MM or DD/MM/YY or D/M/Y etc ... In other languages the separator will not be a slash but rather a period. Without manipulating the search string nothing will be found this way.

Same with numbers: If you enter numbers with thousand separators or even worse in continental European style with a decimal comma you'll get nothing because the database doesn't have the separators and doesn't know a decimal comma.

My solution for this is using moment.js for the dates and simple string manipulation for the numbers. moment.js is very tolerant when it comes to date formats if you use moment().isValid(). If you use it in the strict mode it is actually too strict. So I concluded using it in the normal mode with some extra proprietary checking because otherwise moment.js also identified normal numbers as dates. The solution works for English dates and numbers (GB, clearly not US date formats which are MM/DD/YYYY etc.) and for German date and number formats which also work for many other European countries.

This is the documentation of the parameters sent to the server using server side processing: https://datatables.net/manual/server-side#Sent-parameters
I am manipulating search[value]

That's the entire code including the table definition:

var forexTable = $('#tblForex').DataTable( {
    dom: "Bfrltip",
    processing: true,
    serverSide: true,    //server side only works well with type "POST" !!!
    ajax: {
        url: 'actions.php?action=tblForex',
        type: 'POST',
        data: function ( d ) {
    //allow searching for dates with server side processing
            var dFs = ['D/M/YYYY', 'DD/MM/YYYY', 'D/M/YY', 'DD/MM/YY', 'D/M', 'DD/MM', 'D/M/', 'DD/MM/'];
            var toBeFormat = ''; var sepCounter = 0;
    //if German no comma allowed to recognize date, if English no period!
            if ( ( ! ( lang === 'de' && d.search.value.indexOf(',') >= 0 ) ) &&
                 ( ! ( lang !== 'de' && d.search.value.indexOf('.') >= 0 ) )    )   {
                if ( moment(d.search.value, dFs).isValid() ) {
                    toBeFormat = 'MM-DD';
                    for (i=0; i < d.search.value.length; i++) {
                        //counting the separators in the search string
                        if ( isNaN (d.search.value.substring(i, i+1)) ) {
                            sepCounter++;
                    //if we find two separators and the second one is not at the
                    //end of the string we have a long date otherwise only a short one
                            if ( sepCounter === 2 && i < (d.search.value.length-1) ) {
                                toBeFormat = 'YYYY-MM-DD';
                            }
                        }                        
                    }
                    if (sepCounter > 0) { //we need to have found at least one separator
                        d.search.value = moment(d.search.value, dFs).format(toBeFormat);
                    } else {
                        toBeFormat = '';
                    }
                }
            }
    //not a date: we check for a number
            if (toBeFormat <= '') {
                var number;
                if (lang == 'de') {
                    number = d.search.value.toString().replace( /[\.]/g, "" );
                    number = d.search.value.toString().replace( /[\,]/g, "." );
                } else {
                    number = d.search.value.toString().replace( /[\,]/g, "" );
                }
                if ( ! isNaN( parseFloat(number) ) ) {             
                    d.search.value = number;
                }
            }
        }
    },
    pageLength: 20,
    lengthMenu: [5, 10, 20, 50, 100, 200, 500],
    columns: [
        {   data: "forex.currency" },
        {   data: "forex.date" },
        {   data: "forex.rate" },
        {   data: "forex.update_time" }
    ],
    order: [[ 1, 'desc' ]],
    select: {
        style: 'single'
    },            
    buttons: [
        {   extend: "create", editor: forexEditor },
        {   extend: "edit",   editor: forexEditor },
        {   extend: "remove", editor: forexEditor },
                    "colvis"
    ]
} );

allan

Brilliant - thanks for sharing this with us!

Allan