Friday 25th March, 2016

Parent / child editing with Editor

When working with data from a database, it is quite a common pattern to use parent / child representations of the data. This allows the end user to select a row in the parent table, then being presented with the associated child data from that record. This interface can be particularly useful when using tables with strong referencing (i.e. joined tables) as it displays a very simple, but powerful and information dense, interface to the end user.

Setting up parent / child editing with Editor is a fairly common question, so in this post I'll detail how it can be done. You'll see here that it can be implemented easily through the use of the DataTables API and events.

In this post I'll use a Sites table as the master table, and a Users table as the child, where each user has a site assigned to them. As we select different rows in the sites table, data will be loaded into the users table as required. Both tables are fully editable and a demonstration of the result is shown below.

Name	Users

First name	Last name	Phone #	Location

Parent table

The first step to creating the parent / child display is to create the parent table. This is a very simple Editor and DataTable combination like you'll find in the Editor examples.

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 DataTable.Editor( {
    ajax: '../php/sites.php',
    table: '#sites',
    fields: [ {
        label: 'Site name:',
        name: 'name'
    } ]
} );

DataTables Javascript

The DataTable initialisation is equally as simple - we have two columns:

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.

Note that select.style is used to allow only a single item in the table to be selected at a time. It would be quite possible to allow multiple rows to be selected, but for simplicity in this article a single row will suffice.

var siteTable = $('#sites').DataTable( {
    ajax: '../php/sites.php',
    columns: [
        { data: 'name' },
        { data: 'users', render: function ( data ) {
            return data.length;
        } }
    ],
    select: {
        style: 'single'
    },
    layout: {
        topStart: {
            buttons: [
                { extend: 'create', editor: siteEditor },
                { extend: 'edit',   editor: siteEditor },
                { extend: 'remove', editor: siteEditor }
            ]
        }
    }
} );

Server-side (PHP)

Finally 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

The child table (Users) is very similar to the parent table in construct - the fields and names are different, but the same basic pattern as other DataTables and Editor tables apply here also.

The one key ingredient that makes the whole thing tick is the ability to submit the id value from the row that was selected in the parent table. This is easily obtained from the row().data() method using the {selected:true} selector-modifier - for example:

table.row( { selected: true } ).data();

Full details of how Select integrates with the DataTables API are available in the Select manual.

Editor Javascript

The Users Editor instance is created with an ajax.data option specified as a function. This means that whenever Editor makes an Ajax request to the server, this function will run and augment the data submitted to the server. In this case we want to submit the site id from the row selected in the parent table (as described above). Thus we have the following:

var usersEditor = new DataTable.Editor( {
    ajax: {
        url: '../php/users.php',
        data: function ( d ) {
            var selected = siteTable.row( { selected: true } );

            if ( selected.any() ) {
                d.site = selected.data().id;
            }
        }
    },
    table: '#users',
    fields: [ ... ]
} );

Note that the fields have been left out for brevity - see the Editor join example if you would like to read the full field list.

DataTables Javascript

DataTables also has an ajax.data option that will be executed whenever DataTables makes a request for the data to display. It operates in exactly the same way as the Editor option of the same name:

var usersTable = $('#users').DataTable( {
    ajax: {
        url: '../php/users.php',
        type: 'post',
        data: function ( d ) {
            var selected = siteTable.row( { selected: true } );

            if ( selected.any() ) {
                d.site = selected.data().id;
            }
        }
    },
    columns: [
        { data: 'users.first_name' },
        { data: 'users.last_name' },
        { data: 'users.phone' },
        { data: 'sites.name' }
    ],
    select: true,
    layout: {
        topStart: {
            buttons: [
                { extend: 'create', editor: usersEditor },
                { extend: 'edit',   editor: usersEditor },
                { extend: 'remove', editor: usersEditor }
            ]
        }
    }
} );

Server-side (PHP)

There are two important considerations for the child table's PHP:

If the selected site id (simply called site) is not submitted as part of the request, an empty data array should be shown on the client-side.
When the selected site information is submitted it should be used as a WHERE condition so the child table only displays data that matches that site. This is done using the Editor->where() method as shown below:

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

We've got our two tables, each is editable thanks to Editor, so all we need to do now is wire them together such that a selection in the parent table will load new data for the child table. Also a change in the data for either table will be reflected in the other.

Selecting rows

The Select extension will trigger the select event when a row is selected and deselect when a row is deselected. Thus all we need to do is listen for those events and call the ajax.reload() method to load new data into the child table whenever either occurs (recall that the ajax.data function will be executed at that point, getting the newly selected row id).

siteTable.on( 'select', function () {
    usersTable.ajax.reload();

    usersEditor
        .field( 'users.site' )
        .def( siteTable.row( { selected: true } ).data().id );
} );

siteTable.on( 'deselect', function () {
    usersTable.ajax.reload();
} );

In the above code you'll notice also that there is a call to field().def() - this is used to set a field default. While not required, it does make it easier for the end user if the default for the "Site" field matches that selected in the parent row.

Updated data

When data is updated in the child table (a site changed, new item added, etc) the parent table needs to be updated to reflect the change. Equally, when the parent table is updated (modifying a site's name for example) the child table should be updated. For both we can use Editor's submitSuccess event. Similar to the row selection we just call the ajax.reload() method to update the respective tables:

siteEditor.on( 'submitSuccess', function () {
    usersTable.ajax.reload();
} );

usersEditor.on( 'submitSuccess', function () {
    siteTable.ajax.reload();
} );

Where to go from here

The key take away from this article should be how data selected in one table can be used to affect what data is loaded in another. The tables shown in this example are intentionally very simple and an obvious extension is to increase their complexity with more fields and additional field types.

Beyond that you might wish to consider the following:

Hide the child table when no rows are selected in the parent table
A warning about deleting rows from the parent table which are referenced in the child table could be shown
Multi-row selection in the parent table, allowing multiple site's to be shown in the child table (this would require an OR expression on the server-side.

Any other suggestions?! Please feel free to post them here or in the forum.