Server-side processing, having to SQL query some returned data

connorplumb

Hi,

While using server-side processing my script returns the data in the table and can be searched no problem. There is a status field in my database which is numeric, however the status codes stored in that database link to the status names in another.

Is there anyway once the data is returned, I can then lookup the status names from another database from the returned ID's.

E.g column 2 is status. In the database the table returns the numeric value '2', however that 2 actually means 'Open'.

I hope this makes sense and it would be great if someone could provide some assistance with this issue.

I'm just using the default server side scripting file included on the server-side processing documentation page.

Also, would then you be able to search for the words 'Open' or will you have to use '2' as its querying straight back to the database?

Or would it be easier to build up my own searching script form the sent sever-side variables?

Many thanks,
Connor

rf1234

Sounds very much like your other question. If your Editor instance contains a left join to the status text table (the one that contains "Open" for example) and you select that "Open" field in your query and return it to the client it should work.

rf1234

In some instances when your client side rendering is very different from what is stored in the database you would need to do a transformation of the search string that is passed to the server if you use server side. That can be cumbersome. Hence you should think twice about using server side. In most cases it is simply not required.

Here is an example of a transformed search string to search for dates that are stored in the database as "YYYY-MM-DD" while I have two completely different formats on the client side depending on user language. I also allow to search for short and long dates. In addition I need to transform formatted numbers (e.g. using thousand separators and decimal commas instead of points) into the database number format.

var bondRateTable = $('#tblBondRate').DataTable( {
    dom: "Bfrltip",
    stateSave: false,
    processing: true,
    serverSide: true,  
    ajax: {
        url: 'actions.php?action=tblBondRate',
        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;
    //No commas allowed as date separator; if English: no period either!
            if ( ( ! ( 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.substr(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: "bond_rate.bond_type",
            render: function ( data, type, row ) {
//                  return language == 'de-DE' ? 'Deutsch' : 'English';
                if (lang == 'de') {
                    if (data == 1) {
                        return 'Bundesanleihe';                            
                    } else {
                        return 'Pfandbrief';
                    }
                } else {
                    if (data == 1) {
                        return 'German Government Bond';                            
                    } else {
                        return 'Asset Backed Security';
                    }
                }
            }    
        },
        {   data: "bond_rate.currency" },
        {   data: "bond_rate.bond_duration",
            render: function ( data, type, row ) {
//                  return language == 'de-DE' ? 'Deutsch' : 'English';
                var dur = data.replace(',', '.');
                if ( parseFloat(dur) == parseInt(dur) ) {
                   return parseInt(dur);
                }
                return data;
            }    
        },
        {   data: "bond_rate.date" },
        {   data: "bond_rate.rate" },
        {   data: "bond_rate.update_time" }
    ],
    order: [[ 3, 'desc' ], [ 2, 'desc' ], [ 0, 'asc' ]],
    select: {
        style: 'multi'
    },            
    buttons: [
        {   extend: "create", editor: bondRateEditor },
        {   extend: "edit",   editor: bondRateEditor },
        {   extend: "remove", editor: bondRateEditor },
                    "colvis"
    ]
} );

If I didn't use server side here I could save a lot of code as you can see above!

connorplumb

Hi,

I understand! It's loading quite a lot of data that's the only issue so its slowing down the loading, what other method could you reccomend?

rf1234

If you don't have to use server side, don't use it! If you think your queries are too slow try deferRender first. If that is too slow use server side - and deal with its potential issues when filtering your data.
https://datatables.net/reference/option/deferRender

connorplumb

Thanks, I've given that a try now - but my Ajax file loads all of the customer data at once anyway so that setting makes no difference I don't think!

I suppose I've got to take the difficult server-side processing and deal with the issues...