Answers

• kthorngren Posts: 21,343Questions: 26Answers: 4,954

  August 2021

  See if this blog helps.

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

  August 2021 edited August 2021

  If you change your settings to serverside you have to accept that the search function is performed on the server based on the raw data BEFORE getFormatting. That is very different from what you are used to!

  To make search work again you can either manipulate the data entered by the user to match the format on the server or build views that emulate end user rendering on the server. These views could then be searched by Editor.

  Either way: It is a lot of work. I posted a couple of things on that some time ago in this forum.

  This is on manipulating the search data client side:
  https://datatables.net/forums/discussion/comment/125435/#Comment_125435

  This is a use case to search money market rates from a server side data table. Two languages are supported, English and German. The number and the date formats deviate from each other in English and German - and mostly they also deviate from what is saved in the data base.

  This is the Editor instance server side (PHP):

  if ($lang === 'de') {     
      $searchTable = "rate_search_german";
  } else {
      $searchTable = "rate_search_english";
  }
  Editor::inst( $db, 'rate' )
      ->field(
          Field::inst( 'rate.ref_rate' ),
          Field::inst( 'rate.currency' ),
          Field::inst( 'rate.ref_rate_period' ),
          Field::inst( 'rate.date' )
              ->getFormatter( function ( $val, $data, $opts ) {
                      return getFormatterDate($val);                     
                  } )
              ->setFormatter( function ( $val, $data, $opts ) {
                      return setFormatterDate($val);
                  } ),
          Field::inst( 'rate.rate' )
              ->validator( function ( $val, $data, $opts ) {
                  return validatorRate($data['rate'], $val, true);
              } )
              ->getFormatter( function($val, $data, $opts) {
                  return getFormatterRate($val);
              })
              ->setFormatter( function($val, $data, $opts) {
                  return setFormatterRate($val);
              }),
          Field::inst( 'rate.update_time' )
      )
      ->leftJoin( $searchTable,  'rate.id', '=', $searchTable . '.rate_id')
  //        ->leftJoin( 'rate_search_german',  'rate.id', '=', 'rate_search_german.rate_id')
  //        ->leftJoin( 'rate_search_english', 'rate.id', '=', 'rate_search_english.rate_id')
      ->where( function ( $q ) {
          if ( isset($_SESSION['infoPage']) ) {
              $q  ->where( 'rate.date', '2009-12-31', '>' );
          }
          if ( isset($_POST['fullTextSearchString']) ) {
              if ( $_POST['fullTextSearchString'] > '' ) {
                  if ($_SESSION['lang'] === 'de') {   
                      $q  ->where( 'rate_search_german.global_search_field', '%'. trim($_POST['fullTextSearchString'], '"') .'%', 'LIKE' );
                  } else {
                      $q  ->where( 'rate_search_english.global_search_field', '%'. trim($_POST['fullTextSearchString'], '"') .'%', 'LIKE' );
                  }
              }
          }
      } )              
      ->process($_POST)
      ->json();
  

  These are the definitions of the two views I use for the search: rate_search_german and rate_search_english

  CREATE VIEW `rate_search_german` AS
  SELECT id                               AS rate_id,
         CONVERT(
         CONCAT_WS ( SPACE(1), 
         currency,
         ref_rate,
         ref_rate_period,
         DATE_FORMAT(date, '%d.%m.%Y'),
         REPLACE(rate, '.', ','),
         update_time ) 
         USING utf8mb4                ) 
         COLLATE utf8mb4_general_ci   
                                          AS global_search_field
  FROM rate
  

  CREATE VIEW `rate_search_english` AS
  SELECT id                               AS rate_id,
         CONVERT(
         CONCAT_WS ( SPACE(1), 
         currency,
         ref_rate,
         ref_rate_period,
         DATE_FORMAT(date, '%d/%m/%Y'),
         rate, 
         update_time ) 
         USING utf8mb4                ) 
         COLLATE utf8mb4_general_ci   
                                          AS global_search_field
  FROM rate
  

  And finally the client side data table:

  var rateTable = $('#tblRate').DataTable( {
      dom: "Bfrltip",
      processing: true,
      serverSide: true, 
      stateSave: true,
      ajax: {
          url: 'actions.php?action=tblRate',
          type: 'POST',
          data: function ( d ) {            
              d.fullTextSearchString = d.search.value;
          }
      },
      language: {
          "searchPlaceholder": lang === 'de' ? 
                      "z.B. usd libor overnight 17.06.2019" :
                      "e.g. usd libor overnight 17/06/2019",
          "search": lang === 'de' ? 
                      "Suche (Geduld bitte: 100k+ Datensätze!):" :
                      "Search (Patience please: 100k+ records!):"
      },
      pageLength: 20,
      lengthMenu: [5, 10, 20, 50, 100, 200, 500],
      columns: [
          {   data: "rate.currency" },
          {   data: "rate.ref_rate" },
          {   data: "rate.ref_rate_period" },
          {   data: "rate.date" },
          {   data: "rate.rate" },
          {   data: "rate.update_time" }
      ],
      columnDefs: [
          // targets may be classes
          {targets: [0, 1, 2, 3, 4, 5], searchable: false}
      ],
      order: [[ 3, 'desc' ]],
      select: {
          style: 'single'
      },            
      buttons: [
          {   extend: "create", editor: rateEditor, className: "lgfAdminOnly" },
          {   extend: "edit",   editor: rateEditor, className: "lgfAdminOnly" },
          {   extend: "remove", editor: rateEditor, className: "lgfAdminOnly" },
          {   extend: "colvis", columns: ':not(.never)' }
      ]
  } );
  

  I turned off search in the individual columns in columnDefs as well.

  As you can see: this is highly individual. I would only use it if there is no other way ... You might as well have to limit user search.
• marabou Posts: 8Questions: 2Answers: 0

  August 2021

  Thanks everyone for the suggestions!

  @rf1234, I agree that views are a labour intensive but straightforward solution. Nice code!

  Best regards,

  N