Refreshing data before editing

A web-based CRUD system such as Editor is by its very nature concurrent - i.e. multiple users could be updating data at the same time. Those users might be in the same office, or they might be on opposite sides of the world, but one thing you don't want to happen is for one user to make updates to a row, then have another user undo those changes by submitting information without realising it has changed already.

Ultimately the best solution for this is to update the data in the table in real-time - that is a complex topic and something we will be exploring in future (stay tuned!), but in this post I would like to introduce a mechanism that can be used with minimal code effort and no disruption in an existing code base: refreshing the data for the row(s) to be edited when the user triggers editing on a row.

To do this, we'll develop a new Button that can perform this action before triggering editing, in a reusable component. The result is shown below (note that this looks very similar to standard editing in Editor, but an Ajax request is used to refresh the data when clicking Edit. A second browser window showing this page can be used to create your own form of concurrency, updating the data from one in the other:

Name	Position	Office	Salary
Name	Position	Office	Salary

Reusable button

The Buttons library provides a number of built in button types (e.g. for export of data), and libraries such as Editor and Select can extend those abilities. But the real power of Buttons is its ability to define custom buttons so you can create your own buttons and extend the built in ones.

Reusable custom buttons should be attached to the $.fn.dataTable.ext.buttons object, where the property name is the name of the button (used in the DataTables buttons configuration). In this case we wish to have the button behave similarly to the edit button type, so we can extend that to build upon it. Our initial code structure thus looks like:

$.fn.dataTable.ext.buttons.editRefresh = {
    extend: 'edit',
    text: 'Edit',
    action: function (e, dt, node, config) {
        // 1. Get currently selected row ids
        // ...

        // 2. Ajax request to refresh the data for those ids
        // ...

        // 3. On success update rows with data
        // ...

        // 4. And finally trigger editing on those rows
        // ...
    }
};

Breakdown

Now that our basic design has been designed, all we need to do is flesh out each individual component:

To get the ids of the rows that are about to be edited (so we can identify those we are interested in to the server) we can use the DataTables rows().ids() method, combined with the {selected:true} selector modifier to filter down to only the selected rows.
Since we already have jQuery on the page we'll use $.ajax() to make a simple Ajax request, sending the row ids to the server. Note the use of ajax() to get the configured Editor Ajax url.
Updating the rows is done simply with row().data(), selecting rows based on their ids in a loop. Note that we should call draw() to refresh the table state, but only once all selected rows have been updated.
We can make use of the edit button defined by Editor by calling it, passing through our own action method's parameters, using Function.prototype.call to ensure matching scope.

Putting that together and fleshing out the code we have:

$.fn.dataTable.ext.buttons.editRefresh = {
    extend: 'edit',
    text: 'Edit',
    action: function (e, dt, node, config) {
        this.processing( true );

        // Get currently selected row ids
        var selectedRows = dt.rows({selected:true}).ids();
        var that = this;

        // Ajax request to refresh the data for those ids
        $.ajax( {
            url: config.editor.ajax(),
            type: 'post',
            dataType: 'json',
            data: {
                refresh: 'rows',
                ids: selectedRows.toArray().join(',')
            },
            success: function ( json ) {
                // On success update rows with data
                for ( var i=0 ; i<json.data.length ; i++ ) {
                    dt.row( '#'+json.data[i].DT_RowId ).data( json.data[i] );
                }
                dt.draw(false);

                // And finally trigger editing on those rows
                $.fn.dataTable.ext.buttons.edit.action.call(that, e, dt, node, config);
            }
        } );
    }
};

Using the button

Now we just create and Editor and DataTable instance as per normal, but rather than using Editor's edit button in the buttons array, use editRefresh - e.g.:

buttons: [
    { extend: 'create', editor: editor },
    { extend: 'editRefresh', editor: editor },
    { extend: 'remove', editor: editor }
]

And that's it on the client-side!

Server-side

On the server-side we need a method of being able to get the data for the rows requested from the client-side. That can be done in the PHP, .NET and NodeJS libraries available for Editor with a fairly simple WHERE condition.

Breaking the chain

In many of the Editor examples we use a single chain to define the Editor instance, process the data and return it to the client-side - e.g. in PHP we might use:

$editor = Editor::inst( $db, 'table' )
    ->fields( ... )
    ->process( $_POST )
    ->json();

But we want to conditionally add a WHERE statement - in order to do that you must keep in mind that the above can be rewritten as:

$editor = Editor::inst( $db, 'table' );
$editor->fields( ... );
$editor->process( $_POST );
$editor->json();

With that it's easy to see how we can use an if statement to check if only certain rows should be retrieved.

PHP

In PHP we can use an anonymous function to add a list of WHERE ... OR ... conditions based on the row ids:

$editor = Editor::inst( $db, 'staff' );
$editor->fields(
    ...
);

// Check if we are getting data for specific rows
if ( isset( $_POST['refresh'] ) ) {
    $editor->where( function ($q) use ($editor) {
        // Split the CSV ids
        $ids = explode( ',', $_POST['ids'] );

        for ( $i=0 ; $i<count($ids) ; $i++ ) {
            // Remove the row prefix
            $id = str_replace( $editor->idPrefix(), '', $ids[$i] );
            $q->or_where( 'id', $id );
        }
    } );
}

// Process and fire back the result
$editor
    ->process( $_POST )
    ->json();

.NET

In .NET, similar to the PHP libraries above, we can use the Editor->Where() method with an anonymous function to built a WHERE ... OR ... list if required by the client-side:

var editor = new Editor(db, "staff")
    .Model<StaffModel>();

if (Request.HasFormContentType && Request.Form.ContainsKey("refresh")) {
    editor.Where( q => {
        var ids = (Request.Form["ids"].ToString()).Split(',');

        for (var i=0 ; i<ids.Length ; i++) {
            var id = ids[i].Replace(editor.IdPrefix(), "");

            q.OrWhere( "id", id );
        }
    });
}

var response = editor
    .Process(Request)
    .Data();

return Json(response);

NodeJS

The NodeJS libraries for Editor use the Knex library for database connections and abstraction and it exposes that for conditions through the Editor.where() method. We can then use Knex's orWhere() method to limit the SELECT statement:

let editor = new Editor(db, 'staff').fields(
    ...
);

if (req.body.refresh) {
    editor.where(q => {
        let ids = req.body.ids.split(',');

        for (let i=0 ; i<ids.length ; i++) {
            let id = ids[i].replace(editor.idPrefix(), '');
            q.orWhere('id', id);
        }
    })
}

await editor.process(req.body);
res.json(editor.data());

Conclusion

In this post I've demonstrated how a simple custom button can be used to improve Editor's suitability for a high concurrency system by refreshing the data for the rows to be edited when editing is triggered by the end user. Like the rest of Editor this method fully supports multi-row editing.

Expect to see this feature built into Editor and its libraries in future!

The full Javascript for this example is available if you would like to see the unabridged version. Likewise the PHP, .NET Core and NodeJS scripts used for the development of this post are also available. Each is a small modification of the "staff" example available in the demo packages.