Handling Soft Database Deletes

stevehart

Scenario:

• Load records into DataTables Editor.
• idSrc is the record's primary key on the database.
• User selects a row, makes changes and then saves those changes (submits to server).
• On the server we don't physically delete the record. We mark it as deleted and create a new record with the same data updated with the fields that have been modified.
• We want to reflect the updates in DataTables but the primary key has changed (i.e. the key tied to idSrc used to update DataTables).
• Result is DataTables Editor cannot find the row since the idSrc key has changed.

What I have tried is to generate the idSrc (DT_RowId) and have an additional field (e.g. RecordId) that is the primary key for the database record. That works except when I try to delete a row, DataTables Editor sends the generated ids (from idSrc/DT_RowId), but I need to have the primary keys submitted. From looking at the DataTables Editor code, it does not appear there's anyway for me to tell DataTables Editor to send additional data (i.e. the primary keys) with the "remove" action.

How best to handle this situation? Have I missed something simple?

Cheers,
Steve

allan

Hi,

Interesting one - thanks for posting this. There isn't a way out of the box to get the Editor PHP and .NET libraries to work with this append only kind of structure, since, as you say, they expect the row's id to remain as is.

How are you doing the return at the moment - are you actually using the PHP or .NET libraries, or your own code? If you own code, it might open other options - for example if the JSON includes the new row after edit, listen for the submitSuccess event and then use row.add() to add the new row from the JSON data (which submitSuccess has access to).

Allan

stevehart

Thanks for your prompt response Allan. I will try the approach you suggested.

One idea I had after looking at the Editor code, is that you could allow the idSrc to be specified as an object. Then the object could include the DataTables row id (e.g. DT_RowId) and any other data that we want to submit to the server for removal/deletes. With that approach I could include a "RecordId" in the idSrc structure (which I use for querying the database record) and simply return an updated "RecordId" to reflect that the "Edit" is actually a new record (due to our approach to always create new records for edits to maintain an audit trail).

Cheers,
Steve

stevehart

Sorry, forgot to answer your question about how I'm using the libraries. We're currently using our own code and not using the PHP or .NET libraries.

Cheers,
Steve

stevehart

Trying the submitSuccess approach. How can I determine that it was a "edit" action in the submitSuccess event? I can set a variable to record the action in preSubmit I guess and check that in submitSuccess? The action doesn't appear to be noted anywhere in the submitSuccess parameters. Am I missing something obvious that's more straight forward?

I guess I could also consider adding an extra return value from the server called "newId" and if not blank then add a new row.

allan

How can I determine that it was a "edit" action in the submitSuccess event?

I think what you would need to do is use edit:

editor.on( 'edit', function () {
  editor.one( 'submitSuccess', function (...) {
    ...
  } );
} );

It unfortunately isn't possible to use the API methods in submitSuccess to determine the editor's state, as the editing state has actually been cleared by that point!

It probably is something that should be passed through to the event handler.

Allan

stevehart

Ok, I think this will work after brief testing. Using the "setData" event I do this if it's an "edit" action and there are no errors:

var idSrc = this.s.idSrc;
var modifier = editor.modifier();
var rowData = table.row(modifier).data();
rowData[idSrc] = json.data[0][idSrc];

This updates the row's id to be the id of the new record created on the server which enables the editor to find the row and update it.