Answers

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

  April 2019 edited April 2019

  It is a pretty old question and you've probably found a solution for it. But since I am dealing with similar issues here is a solution I've just finished testing. I defined a field called "global_search_field" that contains a concatenation of a couple of fields. Now I can enter stuff like "LIBOR USD 3months" which is ref_rate plus currency plus ref_rate_period and it is being found. (But: "USD 3months LIBOR" isn't found with this unfortunately because Editor is obviously applying the complete search string to every field as a LIKE statement. If EDITOR were splitting the string into individual words and searched for LIKE '%USD%' OR LIKE '%3months%' OR LIKE '%LIBOR%' it would work but unfortunately it doesn't.

  The global_search_field must be defined in Javascript as well even though I don't return it to the front-end (getFormatter returns space) and I guess also in the HTML. I use class "never" so that it isn't displayed and also make sure it doesn't show up in the colvis selection either.

  @allan I tried something more complex for the concatenated field like this:

  select  
  CONCAT( rate.ref_rate, " ", 
          rate.currency, " ", 
          rate.ref_rate_period, " ", 
          DATE_FORMAT(rate.date, "%d.%m.%Y") ) 
  AS global_search_field 
  from rate
  

  this works fine with MySQL but it doesn't work with Editor PHP. Editor doesn't seem to be able to handle DATE_FORMAT inside CONCAT. Is there a solution for this? And also are you planning to enhance server side search by using more differentiated LIKE statements in Editor's SQL?

  HTML:

  <div class="container"> 
      <table id="tblRate" class="table table-striped table-bordered rateTables hidden"
             cellspacing="0" width="100%">
          <thead>
              <tr>
                  <th>Ref Rate</th>
                  <th>Currency</th>
                  <th>Period</th>
                  <th>Date</th>
                  <th>Rate</th>
                  <th>Timestamp</th>
                  <th class="never">SearchField</th>
              </tr>
          </thead>
      </table>
  </div>
  

  Javascript:

  var rateTable = $('#tblRate').DataTable( {
      dom: "Bfrltip",
      processing: true,
      serverSide: true,    //server side only works well with type "POST" !!!
      ajax: {
          url: 'actions.php?action=tblRate',
          type: 'POST'
      },
      pageLength: 20,
      lengthMenu: [5, 10, 20, 50, 100, 200, 500],
      columns: [
          {   data: "rate.ref_rate" },
          {   data: "rate.currency" },
          {   data: "rate.ref_rate_period" },
          {   data: "rate.date" },
          {   data: "rate.rate" },
          {   data: "rate.update_time" },
          {   data: "global_search_field" }
      ],
      order: [[ 3, 'desc' ]],
      select: {
          style: 'multi'
      },            
      buttons: [
          {   extend: "create", editor: rateEditor },
          {   extend: "edit",   editor: rateEditor },
          {   extend: "remove", editor: rateEditor, name: "remove" },
          {   extend: "colvis", columns: ':not(.never)' },
      ]
  } );
  

  PHP:

  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' ),
          Field::inst( 'CONCAT(rate.ref_rate, " ", rate.currency, " ", rate.ref_rate_period) AS global_search_field' )->set( false )
              ->getFormatter( function ( $val, $data, $opts ) {
                  return '';                     
              } )
      )
      ->process($_POST)
      ->debug( true )
      ->json();
  
• rf1234 Posts: 3,000Questions: 87Answers: 421

  April 2019

  of course it must be "LIKE '%USD%' AND LIKE '%3months%' AND LIKE '%LIBOR%'" in my note above.