Answers

• rf1234 Posts: 3,000Questions: 87Answers: 421

  November 2018

  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 Posts: 3,000Questions: 87Answers: 421

  November 2018 edited November 2018

  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 Posts: 4Questions: 2Answers: 0

  November 2018

  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 Posts: 3,000Questions: 87Answers: 421

  November 2018

  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 Posts: 4Questions: 2Answers: 0

  November 2018

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