Full Editor For Multiple Child Rows

franks59

I have 3 tables:

Host (the parent) - HostKey, Hostname, IP, Location

Drives (child of Hosts) - Drivekey, HostKey, Model, SerialNo, InServiceDate

Functions (child of hosts) - FunctionsKey, HostKey, Function

Both Drives and Functions contain many records per HostKey.

I was looking for an example of how to enable editing (I have the Editor plug-in) for both the parent and child records. I know I listed two child tables above, but an example of a single pair of parent-child editing would suffice.

I have seen child editing, but not using a table for the edits, only a single pop-up with the host record and select boxes. I'm in need of full edit capabilities of every child record.

Ideally it would allow inline editing of the parent, an expand button for the child which would expand like the Sliding Child examples except it would show a full table of all the child records with full edit capabilities. A pop-up for the child editing is ok too.

As I said, I have looked around for a starting point but I can find none. I would have thought this to be a common scenario with one-to-many editing.

Any help or pointers would be appreciated.

Thanks,

Frank

crush123

don't know if its what you're after, but here goes...

still new to editor, but slowly getting the hang of it

i have a page with 2 datatables in a parent and child relationship, for orders and order details.

selecting an order in the top table reloads the data in the bottom order details table.

both tables are editable, (though in the linked example, not many fields are)

clicking the link in the details table, and checking the box in the editor, will set a date field

take a look and if it helps, i can post some code

http://test2.forthwebsolutions.com/plugins/shop/orders.php

franks59

Thanks for the link.

This is sort of what I was looking for but I need to directly edit the order details (CRUD).

I'm thinking maybe I'll have to put a hyperlink in on of the parent table's columns that will child pass session variables or POST data to a new page dedicated to editing the child details for the parent record.

Frank

crush123

You can do that, but my example has been locked down a bit.

I will change the editor instances and update it

try http://test2.forthwebsolutions.com/plugins/shop/ordersdemo.php

there are more editable fields for both the orders and order details

allan

@franks59 - If the relationship between the tables is one-to-one for the records, then you can use Editor's leftJoin method to do what you need (assuming you are using the provided PHP or .NET libraries?).

Consider this example. The second table is updated in the location field. It just so happens in this case that an select input makes best sense here, but you could also use a text input or any other type.

Equally, the editing can be updated n layers deep if you need multiple across multiple tables. On the client-side it is "just a field" while on the server-side the libraries should take care of everything for you once you have the left join set up.

Worth reading the left join documentation which explains this in more detail - PHP - .NET.

Allan

franks59

The relationship is one-to-many as in each computer host will have multiple drives installed., or as in Crush 123's example - each Order has many Line Items.

I have followed and understand the join plus I have verified that the join I created returns multiple child records for the host.

The problem is how to display the multiple child records in an editable table. The examples I saw (and reproduced myself) only display a single parent record with the child records either concatenated into a single field or set up as a series of checkboxes.

What I'd like is to be able to edit the parent record as a table, then expand the parent record and see an editable table of all the child records associated with the parent.

Thanks,

Frank

allan

I see, so the child record doesn't get edited at the same time as the parent table? If so then what I would suggest is having two different Editor instances on the page - one for the parent table and one for the children. In the child row details have an edit button that will cause the child Editor to activate. It would probably need to operate as a standalone Editor, unless you were to create the child rows as an inner DataTable (which would be done by initialising a new DataTable once the child row has been populated with a plain HTML table).

Allan

fsmith

Thanks Allan,

Any examples of the inner Data Table and how to populate it with the data returned by the join operation?

Frank

allan

Not really I'm afraid - I really need to get around to making one!

Basically what you could do is to is create a simple HTML table when you call the row().child() method. Then immediately after initialise a DataTable on that newly created table - that could Ajax load the data to show so it would be exactly like any other DataTable.

For example:

var row = table.row( ... );

// Create HTML table and show
row.child( '<table ...>' ).show();

// Get the child row, find the table and create a DataTable
$( row.node() ).next().find( 'table' ).DataTable( ... );

Allan

fsmith

@Crush123,

Are you using the Editor PHP classes to retrieve the order details? If so, do you have a snippet you can share? I can't figure out how to use the Editor classes to retrieve a specific set of records. I have the detail key in the request object as per your js example, but I don't see how to tell the Editor PHP class to restrict it's query to only that key.

@Alan,

Is there an example of using the Editor PHP class to retrieve specific records such as "where id=123" and how to pass that key from the Editor JS?

Thanks,

Frank

crush123

Sure,

I have a single php page with 2 instances of editor, each with their own ajax source

php script

<script>
var editor_1; // use a global for the submit and return data rendering in the examples
var editor_2; 
$(document).ready(function() {

// add cancel buttons to the editor forms, - needs to be added *before* instantiating the editor
TableTools.BUTTONS.editor_edit.formButtons.push(
    {
    "label": "Cancel",
    "className":"btn btn-link",
    "fn": function () {
    this.close();
    }
});

editor_1 = new $.fn.dataTable.Editor( {

    ajax: "/plugins/shop/ajax/ajax_orders.php",
    table: "#orders",
    fields: [ {
            label: "First Name:",
            name: "tblorders.OrderFirstName"
        }, {
            label: "Last Name:",
            name: "tblorders.OrderLastName"
        }, {
            label: "Email:",
            name: "tblorders.OrderEmail"
        }, {
            label: "Phone:",
            name: "tblorders.OrderPhone"
        }
    ]

} );

editor_2 = new $.fn.dataTable.Editor( {

    ajax: "/plugins/shop/ajax/ajax_orderdetails.php",
    table: "#orderdetails",
    fields: [ {
            label: "Ticket #:",
            name: "tblorderdetails.DetailItemID"
        }, {
            label: "Detail SKU:",
            name: "tblorderdetails.DetailSKU",
        }, {
            label: "Detail Price:",
            name: "tblorderdetails.DetailPrice",
        }, {
            label: "Return:",
            name: "tblorderdetails.Returned",
            type: "checkbox",
            separator: "|",
            ipOpts: [{ label: '', value: 1 }]
        }, {
            label: "ProductID:",
            name: "tblorderdetails.DetailProductID",
            type: "hidden"      
        }, {
            label: "OrderID:",
            name: "tblorderdetails.DetailOrderID",
            type: "hidden"
        }
    ]       
} );

var tblorders = $('#orders').DataTable( {
    dom: "Tfrtip",
    scrollY: 200,
    scrollCollapse: true,
    paging: false,
    ajax: {
        url: "/plugins/shop/ajax/ajax_orders.php"
    },
    columns: [
        { data: "tblorders.OrderAmount", render: $.fn.dataTable.render.number( ',', '.', 2, '£' ) },
        { data: null, render: function ( data, type, row ) {
            // Combine the first and last names into a single table field
            return data.tblorders.OrderFirstName+' '+data.tblorders.OrderLastName;
        } },
        { data: "tblorders.OrderEmail" },
        { data: "tblorders.OrderPhone" },
        { data: "tblorders.OrderDate" }
        ],
    tableTools: {
        sRowSelect: "os",
        aButtons: [
            { sExtends: "editor_edit",   editor: editor_1 }
        ]
    }
} );

$('#orders tbody').on( 'click', 'tr', function () {

var id = this.id.substr(4);
    tblorderdetails.ajax.url('/plugins/shop/ajax/ajax_orderdetails.php?DetailOrderID='+id).load();
    }
 );

var tblorderdetails = $('#orderdetails').DataTable( {
    dom: "Trtip",
    scrollY: 200,
    scrollCollapse: true,
    paging: false,
    "language": {
  "emptyTable": "Click on the order table above to display its details"
},
    ajax: "/plugins/shop/ajax/ajax_orderdetails.php",
    columns: [
        { data: "tblorderdetails.DetailItemID" },
        { data: "tblorderdetails.DetailSKU" },
        { data: "tblorderdetails.DetailPrice", render: $.fn.dataTable.render.number( ',', '.', 2, '£' ) },
                    {
            data: "tblorderdetails.Returned",
            className: "center",
            "render": function ( data, type, row ) {
            if (data == 0) {
            return '<a href="" class="editor_edit">Return Item</a>';
            } else {
            return row.tblorderdetails.ReturnedDate;
            }
            }
        }
        ],
        tableTools: {
        sRowSelect: "os",
        aButtons:  [{ sExtends: "editor_edit",   editor: editor_2 }]
        }
} );

// Edit record
$('#orderdetails').on('click', 'a.editor_edit', function (e) {
    e.preventDefault();

    editor_2
        .title( 'Return Item ?' )
        .message ( 'Check the box to return the item back into stock')
        .buttons( 'Confirm' )
        .edit( $(this).closest('tr') );
} );
});
</script>

each editor/datatable instance has a separate ajax source

orders

<?php


// DataTables PHP library
include( "../../../DataTables-1.10.5/extensions/Editor-PHP-1.4.0/php/DataTables.php" );

// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
  $data = Editor::inst( $db, 'tblorders', 'OrderID' )//table name and PKey(defaults to ID)
->field(
    Field::inst( 'tblorders.OrderAmount' ),
    Field::inst( 'tblorders.OrderFirstName' ),
    Field::inst( 'tblorders.OrderLastName' ),
    Field::inst( 'tblorders.OrderPhone' ),
    Field::inst( 'tblorders.OrderDate' )
        ->getFormatter( 'Format::date_sql_to_format', 'd M Y' )
        ->set( Field::SET_NONE ),//this is a timestamp so it is read only
    Field::inst( 'tblorders.OrderEmail' )
    )
->process( $_POST )
->data();

echo json_encode( $data );
?>

order details

<?php $today = date("Y-m-d");?>

<?php

// DataTables PHP library
include( "../../../DataTables-1.10.5/extensions/Editor-PHP-1.4.0/php/DataTables.php" );

// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Validate;

$filter = isset($_GET['DetailOrderID'])?$_GET['DetailOrderID']:0;//filter recordset according to currently selected row
if ( isset($_POST['action']) && $_POST['action'] === 'edit' ) {
$filter = $_POST['data']['tblorderdetails']['DetailOrderID'];//if edit form has been posted filter recordset according to orderid of row being edited
}//this will ensutre that the orders page is refreshed correctly

$editor = Editor::inst( $db, 'tblorderdetails', 'DetailID' );

$dateField =  Field::inst('tblorderdetails.ReturnedDate')
->set( Field::SET_NONE )
->getFormatter( 'Format::date_sql_to_format', 'd M Y' );

$patronpaymentField =  Field::inst('tblorderdetails.PatronPayment')
->set( Field::SET_NONE );

if ( Editor::action( $_POST ) === Editor::ACTION_EDIT ) {
$returnvalue = $_POST['data']['tblorderdetails']['Returned'];

if ($returnvalue == 1) {//if item is returned, set the ReturnedDate field to today, set the PatronPayment field to 0
    $dateField
        ->set( Field::SET_BOTH )
        ->setValue( $today );
    $patronpaymentField
        ->set( Field::SET_BOTH )
        ->setValue( 0 );
}
}

$editor->
field(
    $dateField,
    $patronpaymentField,
    Field::inst( 'tblorderdetails.DetailOrderID' ),
    Field::inst( 'tblorderdetails.DetailItemID' ),
    Field::inst( 'tblorderdetails.DetailPrice' ),
    Field::inst( 'tblorderdetails.DetailSKU' ),
    Field::inst( 'tblorderdetails.Returned' )
        ->setFormatter( function ( $val, $data, $opts ) {
            return ! $val ? 0 : 1;
        } )
)
->where('tblorderdetails.DetailOrderID', $filter);

$data = $editor
->process( $_POST )
->data();

echo json_encode( $data );
?>

Hope that will help you out

fsmith

@Crush123

Thanks,

The "where" function is what I was looking for!

In your details php file, I kept getting an error on updating the child table, so instead of the logic you had in the orderdetails php file, I decided to instantiate one of two versions of the Editor class. If $GET was set, I created an instance with where(), if not, then I din't include it. Works great.

In any case, this really helped a lot!

Thanks,
Frank

crush123

Cool,

@Alan

For me, the next step would be to incorporate the order details as child rows, so that the order would be the main row, and the child row details would be visible on click.

Don't know if this is possible, but if it is, some pointers or a tutorial would be awesome.

allan

@fsmith - Documentation for the where() method is in the PHP reference documentation. Click the row to get more details.

@crush123 - Sounds basically like what I was trying to describe above. You would create a DataTable in the child row and then just apply Editor as normal.

Might be a little while before I can create a demo of that, but tis a good idea for one!

Allan