Parent / child editing in child rows

Happy New Year everyone. Last year we took a bit of a step back from the blog while focusing on other aspects of DataTables, the extensions and support, but in 2019 we will be more regular with blog posts. To get us started we are going to revisit the Editor parent / child post. Parent / child editing is quite a popular topic for when you have one-to-many database structures, letting end users edit data from both tables on a single page.

The most frequent question about the previous post is "how do we do this with child rows, rather than having a child table always shown?" So that's what we are going to explore in here. As a quick start, here is the result we are aiming for:

	Name	Users

Parent table

Rather than attempting to modify the previous parent / child editing post, we'll create the editing table here from first principles so it is easier to follow along. The first step in that process is to create the parent table, which is a very simple Editor and DataTable combination like you'll find in the Editor examples, we'll also combine it with the row details DataTables example.

Editor Javascript

The Editor Javascript is about as simple as it is possible to get - a single field (the site name) which submits data to the server-side script:

var siteEditor = new $.fn.dataTable.Editor( {
    ajax: '../php/sites.php',
    table: '#sites',
    fields: [ {
        label: 'Site name:',
        name: 'name'
    } ]
} );

DataTables Javascript

For the DataTables initialisation we need to define three columns:

• The show / hide control for the child rows
• The site name
• The number of users that are assigned to that site. For this a columns.render function is used which simply returns the number of users from an array of data.

var siteTable = $('#sites').DataTable( {
    order: [ 1, 'asc' ],
    ajax: '../php/sites.php',
    columns: [
        {
            className: 'details-control',
            orderable: false,
            data: null,
            defaultContent: '',
            width: '10%'
        },
        { data: 'name' },
        { data: 'users', render: function ( data ) {
            return data.length;
        } }
    ],
    select: {
        style:    'os',
        selector: 'td:not(:first-child)'
    },
    layout: {
        topStart: {
            buttons: [
                { extend: 'create', editor: siteEditor },
                { extend: 'edit',   editor: siteEditor },
                { extend: 'remove', editor: siteEditor }
            ]
        }
    }
} );

Note also that the select.selector option is used to disallow row selection of the child show / hide control column - you don't want the user to change the row selection every time they show or hide the child row!

Server-side (PHP)

For the parent table, the PHP script reads the id and name columns from the site table. The id column is required so information can be submitted to the child table server-side script when a row is selected - the id is not actually shown in the table, nor is it in the Editor form (hence why set(false) is used for safety).

One point worth noting here is that an Mjoin instance is used to get information about the number of items that use each site (Mjoin is short for "many join"). A detailed explanation on how to use Mjoin is available in the Editor manual. If you don't need or want to show the count column in your parent table, the Mjoin is not required.

Editor::inst( $db, 'sites' )
    ->fields(
        Field::inst( 'id' )->set( false ),
        Field::inst( 'name' )->validator( 'Validate::notEmpty' )
    )
    ->join(
        Mjoin::inst( 'users' )
            ->link( 'sites.id', 'users.site' )
            ->fields(
                Field::inst( 'id' )
            )
    )
    ->process( $_POST )
    ->json();

Child table

Let's now write the event handler that will show and hide the child rows as this will define the functions required to show and hide the DataTable for each child row. This is a small modification to the row details example. Rather than passing in just the row data to methods to create the child table, we'll pass in the whole row instance, giving access to the full DataTables API for the parent table. Also in this case we will use a destroy function to tidy up the child tables when they are closed to ensure there are no memory leaks:

$('#sites tbody').on('click', 'td.details-control', function () {
    var tr = $(this).closest('tr');
    var row = siteTable.row( tr );

    if ( row.child.isShown() ) {
        // This row is already open - close it
        destroyChild(row);
        tr.removeClass('shown');
    }
    else {
        // Open this row
        createChild(row);
        tr.addClass('shown');
    }
} );

From this we need to create two functions:

• createChild for the child table's DataTable and Editor functionality
• destroyChild to tidy up

Creating a DataTable

In the row details example a string is given to the child() method to have that displayed in the child row. However, it is also possible to pass in a DOM element, so we can create a DataTable by simply creating a table element, inserting it into the document (with child().show()) and then initialising it as a regular DataTable:

function createChild ( row ) {
    // This is the table we'll convert into a DataTable
    var table = $('<table class="display" width="100%"/>');

    // Display it the child row
    row.child( table ).show();

    // Initialise as a DataTable
    var usersTable = table.DataTable( {
        // ...
    } );
}

You will be able to see from this that we can build any DataTable on-the-fly whenever a child row is requested to be displayed by the user. Each time the createChild() function is called a new unique table is created, so there is no need to create id's for each table either.

Destroying a DataTable

When it comes time to close the child row, we don't want to simply close it and leave the DataTable inside it taking up memory - that would be a leak that would eventually cause the browser to run out of memory if the end user open and closed enough rows. Instead, we need to make use of the destroy() method to destroy the table and all of its event handlers. We also remove it from the DOM using a little jQuery:

function destroyChild(row) {
    var table = $("table", row.child());
    table.detach();
    table.DataTable().destroy();

    // And then hide the row
    row.child.hide();
}

Editor configuration

The configuration for the Editor child row is almost exactly the same as any other basic Editor in that it defines the Ajax URL for the data, the table to edit and the fields to be editable. However, in this case we need to use the ajax.data option to sent the id of the parent (in this case the site id) to the server for use in WHERE conditions. We can also make the user experience easier by preselecting the site that the child table is in, through use of the field.def option being set to the parent row's id (i.e. rowData.id in this case):

var rowData = row.data();
var usersEditor = new $.fn.dataTable.Editor( {
    ajax: {
        url: '/media/blog/2016-03-25/users.php',
        data: function ( d ) {
            d.site = rowData.id;
        }
    },
    table: table,
    fields: [ {
            label: "First name:",
            name: "users.first_name"
        }, {
            label: "Last name:",
            name: "users.last_name"
        }, {
            label: "Phone #:",
            name: "users.phone"
        }, {
            label: "Site:",
            name: "users.site",
            type: "select",
            placeholder: "Select a location",
            def: rowData.id
        }
    ]
} );

DataTable configuration

The DataTable configuration is also almost the same as other basic DataTables, again modified to use ajax.data to send the id of the parent row, to ensure that only the rows that belong to that parent are loaded:

var usersTable = table.DataTable( {
    pageLength: 5,
    ajax: {
        url: '/media/blog/2016-03-25/users.php',
        type: 'post',
        data: function ( d ) {
            d.site = rowData.id;
        }
    },
    columns: [
        { title: 'First name', data: 'users.first_name' },
        { title: 'Last name', data: 'users.last_name' },
        { title: 'Phone #', data: 'users.phone' },
        { title: 'Location', data: 'sites.name' }
    ],
    select: true,
    layout: {
        topStart: {
            buttons: [
                { extend: 'create', editor: usersEditor },
                { extend: 'edit',   editor: usersEditor },
                { extend: 'remove', editor: usersEditor }
            ]
        }
    }
} );

We also use pageLength to keep the page size small in the child rows, but this can be set as you wish. Indeed this highlights that the Editor and DataTable in the child rows are just regular components and can be modified using any of the options, events and APIs for each, just like any other Editor and DataTable.

Updating the parent table

When the child table has been modified, the parent table's Users count might need to be updated in one or more rows. For this we use the ajax.reload() method (accessed through the row variable we passed into the function - recall that the DataTables' chaining API allows access to top level methods at all levels):

usersEditor.on( 'submitSuccess', function (e, json, data, action) {
    row.ajax.reload(function () {
        $(row.cell( row.id(true), 0 ).node()).click();
    });
} );

Line three above uses a synthetic click event to trigger the "show" action for the child row, as the Ajax reload will cause it to be closed automatically (it is effectively a new row that is added). This could be improved in terms of efficiency by getting the new counts from the server and using row().data() to update the data for each row, but that is outside the scope of this post.

Server-side (PHP)

Although on the client-side we can have multiple child Editor's initialised and shown at any one time, on the server-side we only need a single script that will differentiate between the Editor's by the parent id (i.e. the site). This is done using the site parameter submitted and a WHERE condition:

if ( ! isset($_POST['site']) || ! is_numeric($_POST['site']) ) {
    echo json_encode( [ "data" => [] ] );
}
else {
    Editor::inst( $db, 'users' )
        ->field( 
            Field::inst( 'users.first_name' ),
            Field::inst( 'users.last_name' ),
            Field::inst( 'users.phone' ),
            Field::inst( 'users.site' )
                ->options( 'sites', 'id', 'name' )
                ->validator( 'Validate::dbValues' ),
            Field::inst( 'sites.name' )
        )
        ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
        ->where( 'site', $_POST['site'] )
        ->process($_POST)
        ->json();
}

Wiring it together

There is just one step remaining - when the site label is updated in the parent table, we need to reflect that in the child table. We can do that using the ajax.reload() function for the child tables:

function updateChild ( row ) {
    $('table', row.child()).DataTable().ajax.reload();
}

That function can be called using:

siteEditor.on('submitSuccess', function () {
    siteTable.rows().every(function () {
        if (this.child.isShown()) {
            updateChild(this);
        }
    });
} );

If you would like to see the fully assembled Javascript used in this post, it is available here. The CSS used is also available, although this is just for the row detail buttons and to highlight the child row.

Where to go from here

What I'd like you to be able to take away from this article is that the child row display need not be limited to static data like in the basic example. You can add any interactivity you wish into a child row, including a comprehensive editable DataTable!