Nested DataTable editable with Ajax Source Data

sinfony · March 2019

I have one or two questions about the following code snippet. I want to populate parentData and childData with a sql query. I have a php file with the query and json_encode of the result. How can I pass this query to the used variable in the js? My 2nd question: I want the individual lines of the childData to be editable and to be updated in the database. For this, the data of the corresponding line should be transferred to a modal. Whats the best way to do this? I found a similar example for my desired result: example here

I'm interested: is it possible with the help of the editor to make SQL queries from the table also with Group_Concat or are there only left joins? If so, how can I solve the problem and pass the data? So far I have the problem that I do not get the td.details-control integrated. Therefore, the sample code is hardcoded data. So my goal is an editable DataTable with 2 separate SQL statements for the parent and child tables.

Thanks in advance.

HTML-Code

<!DOCTYPE html>
<html>
  <head>
     <script src="http://code.jquery.com/jquery-1.11.3.min.js"></script>
     <link href="https://nightly.datatables.net/css/jquery.dataTables.css" rel="stylesheet" type="text/css" />
     <script src="https://nightly.datatables.net/js/jquery.dataTables.js"></script>
     <meta charset=utf-8 />
     <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
     <link href="https://netdna.bootstrapcdn.com/bootstrap/3.0.0/css/bootstrap.min.css" rel="stylesheet"/>
     <script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
     <link href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css" rel="stylesheet"/>
    <title>DataTables - JS Bin</title>
  </head>
  <body>
     <div class="panel panel-default">
        <div class="panel-heading"><h3>Overview</h3></div>      
        <div class="panel-body">
          <table id="mytable" class="table table-condensed table-hover" width=100%"> 
          </table>  
       </div>
     </div> 

     <div class="modal fade" id="editModal" role="dialog">
       <div class="modal-dialog">
         <div id="content-data"></div>
       </div>
     </div>
  </body>
</html>

JS

const parentData = [
  {column1: 's01', column2: '200 250', column3: 'A / B / C'},
  {column1: 's02', column2: '100 200', column3: 'A / D'},
  {column1: 's03', column2: '100 300', column3: 'E / F'}
];

const childData = {
  s01: [
    {column1: 'p1', column2: '5', column3: 'yes', column4: '100 200', column5: 's02', column6: '1', tagged: '0'},
    {column1: 'p2', column2: '4', column3: 'yes', column4: '150 250', column5: 's06', column6: '3', tagged: '0'},
    {column1: 'p3', column2: '1', column3: 'yes', column4: '150 250', column5: 's07', column6: '71', tagged: '1'},
    {column1: 'p4', column2: '2', column3: 'yes', column4: '100', column5: 's03', column6: '35', tagged: '1'}
  ],
  s02: [
    {column1: 'p1', column2: '1', column3: 'yes', column4: '150 200', column5: 's02', column6: '21', tagged: '1'},
    {column1: 'p2', column2: '3', column3: 'no', column4: '200 250', column5: 's03', column6: '32', tagged: '1'}
  ],
  s03: [
    {column1: 'p1', column2: '3', column3: 'yes', column4: '100', column5: 's03', column6: '31', tagged: '1'},
    {column1: 'p2', column2: '2', column3: 'yes', column4: '150 300', column5: 's06', column6: '62', tagged: '1'},
    {column1: 'p3', column2: '1', column3: 'no', column4: '150', column5: 's01', column6: '13', tagged: '1'}
  ]
};

const dataTable = $('#mytable').DataTable({
  "paging": false,
  "lengthChange": false,
  "info": false,
  data: parentData,
  columns: [
     {
        "className":      'details-control',
        "orderable":      false,
        "data":           null,
        "defaultContent": ''
     },
    {title: 'Col 1', data: 'column1'},
    {title: 'Col 2', data: 'column2'},
    {title: 'Col 3', data: 'column3'}
  ],
    "order": [[1, 'asc']]
});

$('#mytable').on('click', 'td.details-control', function(){
  const parentRow = dataTable.row($(this).closest('tr'));
  parentRow.child.isShown() ?
  parentRow.child.remove() :
  parentRow.child('<table id="details'+parentRow.data().column1+'" class="table table-condensed table-hover"></table>').show();
  $(this).closest('tr').toggleClass('shown');
  if(!parentRow.child.isShown()) return;
  const detailsData = childData[parentRow.data().column1];
  $('#details'+parentRow.data().column1).DataTable({
  sDom: 't',
    data: detailsData,
    columns: [
      {title: 'Child 1', data: 'column1'},
      {title: 'Child 2', data: 'column2'},
      {title: 'Child 3', data: 'column3'},
      {title: 'Child 4', data: 'column4'},
      {title: 'Child 5', data: 'column5'},
      {title: 'Child 6', data: 'column6'},
      {title: 'Tagged', data: 'tagged', "render": function (data, type, row) {
                          return (data === '1') ? '<span class="glyphicon glyphicon-ok"></span>' : '<span class="glyphicon glyphicon-remove"></span>';}
      },
      {title: 'Action', "render": function (data, type, row) {
                          return '<button type="button" class="btn btn-primary btn-xs" data-toggle="modal" data-target="#editModal" data-id="1"><i class="glyphicon glyphicon-pencil">&nbsp;</i>Edit</button>';}
      }
    ]
  });
  
});

rf1234 · 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 · 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 · March 2019

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 · March 2019

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 · March 2019

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 · 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 · 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 · March 2019

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

Nested DataTable editable with Ajax Source Data

Nested DataTable editable with Ajax Source Data

Answers

Howdy, Stranger!

Categories

DataTables

Nested DataTable editable with Ajax Source Data

Nested DataTable editable with Ajax Source Data

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables