Answers

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

  March 2019 edited March 2019

  "So my goal is an editable DataTable with 2 separate SQL statements for the parent and child tables."

  The best for this is to use Editor. I have been using this on many occasions. Here is a blog on this:
  https://datatables.net/blog/2016-03-25

  If you need to "prepare" the data you read (e.g. using GROUP_CONCAT and the likes) to be processed with Editor I recommend you use views. That works very well and makes your Editor code less complex too.

  You can also use proprietary getFormatters with Editor that allow you to retrieve just about anything for each individual Editor column. Same applies to the use of setFormatters in case you need more flexibility.

  https://editor.datatables.net/manual/php/formatters
• sinfony Posts: 7Questions: 2Answers: 0

  March 2019

  Thanks for the helpful links, I've come a bit ahead (I think so) but do you have an example for building a view with the editor? I would like to represent the following SQL statement:

  SELECT d.name AS 'Hostname', GROUP_CONCAT(DISTINCT v.id ORDER BY v.id ASC SEPARATOR ' ') AS 'VLAN', GROUP_CONCAT(DISTINCT v.name ORDER BY v.name ASC SEPARATOR ' ') AS 'Location'
  FROM device d
  INNER JOIN vlan v ON d.deviceId = v.deviceId
  GROUP BY d.name
  

  I've filled my table with a sample query and at least I've been able to integrate the query and td.details-control, which I had problems with before. Looks like this now

  This is the editor example query for the picture shown

  Editor::inst( $db, 'device', 'deviceId' )
      ->fields(
          Field::inst( 'device.deviceId' ),
          Field::inst( 'device.name' ),
          Field::inst( 'device.hersteller' ),
          Field::inst( 'device.modell' )
          ->options( Options::inst()
                  ->table( 'vlan' )
                  ->value( 'id' )
                  ->label( 'id' )
              )
              ->validator( Validate::dbValues() ),
          Field::inst( 'vlan.id' ),
          Field::inst( 'vlan.name' )
      )
      ->leftJoin( 'vlan', 'vlan.deviceId', '=', 'device.deviceId' )
      
      ->process( $_POST )
      ->json();
  

  Child table is still unfilled. How do I pass (in my example the host name) to the SQL query for the child table to be created? Would be great if you could help me there
• rf1234 Posts: 3,000Questions: 87Answers: 421

  March 2019 Answer ✓

  Hi, you can't CREATE a view with Editor but you can USE it in Editor:

  Here is how to create a view in MySQL (should also work with other relational DBMSs):

  ....
  CREATE VIEW `yourView` AS
  SELECT d.name AS Hostname, 
         GROUP_CONCAT(DISTINCT v.id ORDER BY v.id ASC SEPARATOR ' ') AS VLAN, 
         GROUP_CONCAT(DISTINCT v.name ORDER BY v.name ASC SEPARATOR ' ') AS Location
  FROM device d
  INNER JOIN vlan v ON d.deviceId = v.deviceId
  GROUP BY d.name
  

  Subsequently you can query this view with SQL but also with Editor:

  SQL:

  SELECT * from yourView
  

  would return your columns Hostname, VLAN and Location.

  Not sure why you wrote 'Hostname' instead of Hostname. Never seen that with quotation marks before. It is a variable name and not a string actually.

  This is how you can create any kind of view in MySQLWorkbench which I would recommend as a tool:

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

  March 2019 Answer ✓

  Let me give you a simple example from my own coding. It is actually the simplest I could find but should have all that you need. I have filters and those filters that the user can freely define have value ranges.

  Data Model:

  

  This part of the code ties the two tables together. Filtr as parent and valueRange as child:

  

  And the JS code (deleted all the Data tables and editor events to make it shorter):

  var filtrEditor = new $.fn.dataTable.Editor({
      ajax: {
          url: 'actions.php?action=tblFiltr'
      },
      table: "#tblFiltr",
      formOptions: {
          inline: {
              submit: 'allIfChanged'
          },
          main: {
              focus: null
          }
      },
      fields: [
               {
              label: lang === 'de' ? 'Abteilungsauswahl:' : 'Department selection:',
              name: "govdept[].id", //render gov_name, govdept_name, (regional_12)
              type: "selectize",
              opts: {
                  create: false,
                  maxItems: null,
                  openOnFocus: true,
                  allowEmptyOption: true,
                  placeholder: lang === 'de' ? 'Bitte wählen Sie eine oder mehrere Abteilung(en)' : 'Please select one or more Department(s)',
              }
          },  {
              label: lang === 'de' ? 'Eigene Bezeichnung des Filters:' : 'Filter Label:',
              name:  "filtr.label",
              attr: {
                  placeholder: lang === 'de' ? 
                      "Bitte frei wählbaren Text eingeben" :
                      "Please enter text of your choice"
                  }        
          }, {
              label: lang === 'de' ? 'Feld Typ:' : 'Field Type:',
              name: "filtr.field_type",
              type: "select",
              options: fieldTypeOptions
          }
      ]
  });
     
  $('#tblFiltr').on( 'click', 'tbody td.filtrsInline', function (e) {
      filtrEditor.inline( this, {
          onBlur: 'submit'
      } )
  } )
  
  var filtrTable = $('#tblFiltr').DataTable({
      dom: "Bfrtip",
      ajax: {
          url: 'actions.php?action=tblFiltr'
      },
      columns: [
          {   data: "govdept", render: "[, ].name"    },
          {   data: "filtr.label" },
          {   data: "filtr.field_type",
              render: function (data, type, row) {
                  return $.grep(fieldTypeOptions, function(obj){return obj.value == data;})[0].label;
              }
          },
          {   data: "filtr.update_time"   }
      ],
      columnDefs: [
          // targets may be classes
          {   targets: "hiddenCols", visible: false   },
          {   targets: "filtrs", className: "filtrsInline"    }
      ],
      select: {
          style:    'os',
          selector: 'td:first-child'
      },     
      buttons: [
          {   extend: "create", editor: filtrEditor   },
          {   extend: "edit", editor: filtrEditor },
          {   extend: "remove", editor: filtrEditor   },
              "colvis"
      ]
  });
  
  var valueRangeEditor = new $.fn.dataTable.Editor({
      ajax: {
          url: 'actions.php?action=tblValueRange',
          data: function (d) {
              var selected = filtrTable.row({selected: true});
              if (selected.any()) {
                  d.filtr_id = selected.data().filtr.id;
                  d.field_type = selected.data().filtr.field_type;
              }
          }
      },
      table: "#tblValueRange",
      formOptions: {
          inline: {
              submit: 'allIfChanged'
          }
      },
      fields: [{
              label: lang === 'de' ? 'Wertebereichsbegrenzer:' : 'Value Range Delimiter:',
              name: "value_range.range_type",
              type: "select",
              options: rangeTypeOptions
          }, {
              name:  "value_range.value"
          }
      ]
  });
  
  $('#tblValueRange').on( 'click', 'tbody td.valRangesInline', function (e) {
      valueRangeEditor.inline( this, {
          onBlur: 'submit'
      } )
  } )
  
  var valueRangeTable = $('#tblValueRange').DataTable({
      dom: "Bfrtip",
      ajax: {
          url: 'actions.php?action=tblValueRange',
          type: 'POST',
          data: function (d) {
              var selected = filtrTable.row({selected: true});
              if (selected.any()) {
                  d.filtr_id = selected.data().filtr.id;
                  d.field_type = selected.data().filtr.field_type;
              }
          }
      },
      columns: [
           {
              data: null,
              defaultContent: '',
              className: 'select-checkbox',
              orderable: false
          },
          {   data: "value_range.range_type",
              render: function (data, type, row) {
                  return $.grep(rangeTypeOptions, function(obj){return obj.value == data;})[0].label;
              }
          },
          {   data: "value_range.value"   },
          {   data: "value_range.update_time"    }
      ],
      columnDefs: [
          // targets may be classes
          {  targets: "hiddenCols", visible: false  },
          {  targets: "valRanges", className: "valRangesInline" }
      ],
      select: {
          style:    'single',
          selector: 'td:first-child'
      },    
      buttons: [
          {  extend: "create", editor: valueRangeEditor  },
          {  extend: "edit", editor: valueRangeEditor  },
          {  extend: "remove", editor: valueRangeEditor  },
            "colvis"
      ]
  });
  
  
• rf1234 Posts: 3,000Questions: 87Answers: 421

  March 2019 Answer ✓

  And the PHP:

  // Alias Editor classes so they are easy to use
  use DataTables\Editor;
  use DataTables\Editor\Field;
  use DataTables\Editor\Mjoin;
  use DataTables\Editor\Options;
  use DataTables\Editor\Upload;
  
  function tblFiltr(&$db, &$lang) {        
      if ($lang === 'de') {     
          $msg[0] = 'Feld darf nicht leer sein.';
          $msg[1] = 'Bitte geben Sie eine Zahl ein.';
          $msg[2] = 'Bitte wählen Sie mindestens eine Abteilung aus.';
      } else {
          $msg[0] = 'Field may not be empty.';
          $msg[1] = 'Please enter a number.';
          $msg[2] = 'Please select at least one department.';
      }
      Editor::inst( $db, 'filtr' )
      ->field(
          Field::inst( 'filtr.id' )->set( false ),
          Field::inst( 'filtr.label' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
          Field::inst( 'filtr.field_type' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
          Field::inst( 'filtr.update_time' )->set(Field::SET_BOTH)
                                             ->setValue( mySqlTimestamp() ),
          Field::inst( 'filtr.updater_id' ) ->set(Field::SET_BOTH)
                                             ->setValue( $_SESSION['id'] ),
          Field::inst( 'filtr.creator_id' ) ->set(Field::SET_CREATE)
                                             ->setValue( $_SESSION['id'] )
      )
      ->validator( function ( $editor, $editorAction, $data ) use ( $msg ) {
          if ( $editorAction === Editor::ACTION_CREATE || 
               $editorAction === Editor::ACTION_EDIT       ) {
              foreach ( $data['data'] as $pkey => $values ) {
                  if ( $values['govdept-many-count'] <= 0 ) {
                      return $msg[2];
                  }
              }
              return null;
          }
      } )
      ->join(
      Mjoin::inst( 'govdept' )
          ->link( 'filtr.id', 'filtr_has_govdept.filtr_id' )
          ->link( 'govdept.id', 'filtr_has_govdept.govdept_id' )
          ->order( 'govdept.name asc' )
          ->fields(
              Field::inst( 'id' )->set( false )
                  ->options( Options::inst()
                      ->table('govdept')
                      ->value('id')
                      ->label( 'name' )
                      ->render( function ( $row ) {   
                          return getFormatterGovdeptOptions($row['id']);
                      } )
                      ->order( 'name asc' )
                      //where clause MUST be a closure function in Options!!!
                      ->where( function($q) {
                          //the user must be at least an editor for the
                          //respective department
                          $q ->where( function($r) {
                              $r  ->where( 'id',  
                                  '( SELECT DISTINCT govdept.id    
                                      FROM user, govdept_has_user, govdept
                                      WHERE user.id = :id                                     AND        
                                            govdept_has_user.role   
                                                IN ("Administrator", "Principal", "Editor" )  AND     
                                            user.id = govdept_has_user.user_id                AND     
                                            govdept_has_user.govdept_id = govdept.id
                                      )', 'IN', false);
                              $r  ->bind( ':id', $_SESSION['id'] );
                          });
                      } )
                  ),
              Field::inst( 'name' )->set( false )  //getFormatter works but is not needed for the time being
  //                ->getFormatter( function ( $val, $data, $opts ) {
  //                    return getFormatterGovdeptOptions($data['id']);
  //                } )
          )
      )    
      ->where( function ( $q ) {        
          $q  ->where( 'filtr.id', getValidIdsForUser($_SESSION['id'], 'filtr'), 'IN', false );
      } )
      ->on( 'postCreate', function ( $editor, $id, $values, $row ) {            
          logChange( $editor->db(), 'create', $id, $row, 'filtr' );
      } )
      ->on( 'postEdit', function ( $editor, $id, $values, $row ) {
          logChange( $editor->db(), 'edit', $id, $row, 'filtr' );
      } )
      ->on( 'postRemove', function ( $editor, $id, $values ) {
          logChange( $editor->db(), 'delete', $id, $values, 'filtr' );
      } )
      ->process($_POST)            
      ->json();
  }
  
  function tblValueRange(&$db, &$lang) {    
      if ( ! isset($_POST['filtr_id']) || ! is_numeric($_POST['filtr_id']) ) {
          echo json_encode( [ "data" => [] ] );
      } else {
          if ($lang === 'de') {     
              $msg[0] = 'Feld darf nicht leer sein.';
              $msg[1] = 'Bitte geben Sie eine Zahl ein.';
          } else {
              $msg[0] = 'Field may not be empty.';
              $msg[1] = 'Please enter a number.';
          }
          Editor::inst( $db, 'value_range' )
          ->field(
              Field::inst( 'value_range.id' )->set( false ),
              Field::inst( 'value_range.filtr_id' )->set(Field::SET_CREATE)
                                                      ->setValue( $_POST['filtr_id'] ), 
              Field::inst( 'value_range.range_type' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
              Field::inst( 'value_range.value' )
                  ->getFormatter( function ( $val, $data, $opts ) {
                      return getFormatterValueRangeValue
                             ($val, $_POST['field_type']);
                  } )
                  ->validator( 'Validate::notEmpty', array('message' => $msg[0]) )
                  ->setFormatter( function ( $val, $data, $opts ) {
                      $parm = null;
                      return setFormatterValueRangeValue
                              ($val, $parm, $_POST['field_type']);
                  } ),
              Field::inst( 'value_range.update_time' )->set(Field::SET_BOTH)
                                                   ->setValue( mySqlTimestamp() ),
              Field::inst( 'value_range.updater_id' )->set(Field::SET_BOTH)
                                                  ->setValue( $_SESSION['id'] ),
              Field::inst( 'value_range.creator_id' )->set(Field::SET_CREATE)
                                                  ->setValue( $_SESSION['id'] )       
          )
          ->validator( function ( $editor, $editorAction, $data ) {
              if ( $editorAction === Editor::ACTION_CREATE || $editorAction === Editor::ACTION_EDIT ) {
                  if ( $editorAction === Editor::ACTION_CREATE ) {
                      $action = 'create';
                  } else {
                      $action = 'edit';
                  } //no return statement if validation passes
                  foreach ( $data['data'] as $pkey => $values ) {
                      return globalValidatorValueRange($values, $action, 
                                       $_POST['filtr_id'], $_POST['field_type']);
                  }
              }
          } )
          ->leftJoin( 'filtr', 'value_range.filtr_id', '=', 'filtr.id')        
          ->where( function($q) {
              $q ->where( 'value_range.filtr_id', $_POST['filtr_id'] );
          })
          ->on( 'postCreate', function ( $editor, $id, $values, $row ) {            
              logChange( $editor->db(), 'create', $id, $row, 'value_range' );
          } )
          ->on( 'postEdit', function ( $editor, $id, $values, $row ) {
              logChange( $editor->db(), 'edit', $id, $row, 'value_range' );
          } )
          ->on( 'postRemove', function ( $editor, $id, $values ) {
              logChange( $editor->db(), 'delete', $id, $values, 'value_range' );
          } )
          ->process($_POST)            
          ->json();
      }
  }
  
• sinfony Posts: 7Questions: 2Answers: 0

  March 2019

  First of all thank you. That was a good help for me.

  "Not sure why you wrote 'Hostname' instead of Hostname. Never seen that with quotation marks before. It is a variable name and not a string actually."

  I think the column names were strings before, of course it makes no sense in this case, you're right.

  Have now got the parent and child table filled over the view. However, the where clause for the child table does not work yet. Get the error message that .row() would be no function or the problem that no data is loaded at all. I have to take a closer look at this tomorrow, so far all the child tables are filled with the same content.

  
• sinfony Posts: 7Questions: 2Answers: 0

  March 2019

  How can I get the id or hostname of the table row? The query for the child table works so far, if I mark the row before I click on td.details-control. But I want the id / hostname to be passed by td.details-control without first marking the row. I tried to get the Id in the onclick event via "table.row(tr).data()", but that didn't work.
• sinfony Posts: 7Questions: 2Answers: 0

  March 2019

  GoT it, the question is obsolet. I couldn‘t delete the post anymore