modifying a row not getting updated to DB - row.invalidate()

barncattech

I am trying to make a button that changes the value of a particular field ("status") for every selected row, and update to the database. I see the change happen on screen, but the change never gets written to the database, and I do not see a call to my update code in the network tab in the browser debugger. If I reload the page, the old value is there.

The part in question is the collection of buttons in a dropdown that lets you set the "status" field of selected rows to "active", "project", or "closed". I'm trying some different things in the 3 cases, and none trigger a call to my update script.

So- fundamental question- since the editor is working and calls my updateProjectRecord.php, why is this not called when I call either row.data(d) or row.invalidate() in my buttons?

Here are the editor and the datatable configurations (with some irrelevant parts skipped for brevity).

var editor = new $.fn.dataTable.Editor( {
    ajax: 'projecteditor/php/updateProjectRecord.php',
        table: '#project',
        fields: [
            {
                "label": "name:",
                "name": "name"
            },
    
            {
                "label": "status:",
                "name": "status",
                type:  "select",
            label: "Status:",
              options: [
                    "active",
                    "project",
                    "closed"
                ]
            },
            {
                "label": "city:",
                "name": "city"
            },
            {
                "label": "state:",
                "name": "state"
            }
        ]
  });


var table = $('#project').DataTable( {
    dom: 'Bfrtip',
    order: [1,'asc'],
    ajax: 'projecteditor/php/getProjectList.php',
    table: '#project',
    columns: [ ....... ],
    buttons: [
    { extend: 'create', editor: editor },
    { extend: 'edit',   editor: editor },
    { extend: 'remove', editor: editor },
    {
        extend: 'collection',
        autoClose: true,
        text: 'Set Selected To:',
        buttons: [
        {
            text: "Active",
            action: function ( e, dt, node, config ) {
                table.rows({ selected: true }).every( function ( rowIdx, tableLoop, rowLoop ) {
                    var d = this.data();
                    d.status = "active";
                    this.invalidate();
                });
                // Draw once all updates are done
                table.draw();
            }
        },
        {
            text: "Project",
            action: function ( e, dt, node, config ) {
                table.rows({ selected: true }).each( function ( index ) {
                    var row = table.row( index );
                    var data = row.data();
                    data.status = "project";
                    row.invalidate();
                });
                // Draw once all updates are done
                table.draw();
            }
        },
        {
            text: "Closed",
            action: function ( e, dt, node, config ) {
                table.rows({ selected: true }).every( function ( rowIdx, tableLoop, rowLoop ) {
                    var d = this.data();
                    d.status = "closed";

                    this.data(d);
                    this.invalidate();  // redundant
                    console.log(d.name);
                    console.log(rowIdx);
                    console.log(rowLoop);
                    console.log(this);
                });
                // Draw once all updates are done
                table.draw();
            }
        }
    ]               
}       

barncattech

Note that I did add the editor declaration in my button collection, and it still fails. I thought I had found it

{
        extend: 'collection',
        editor: editor,
        autoClose: true,
        text: 'Set Selected To:',
        buttons: [ 
        ...

to my

colin

That's right, you're just changing it in the underlying table with row().data(), not on the server with the Editor API. You need to call edit() on the records that are selected.

This example from this thread is doing something similar - it's updating other records when the start time changes in an edited field. Hopefully that'll get you going,

Colin

barncattech

Colin- thanks very much. That got me a lot closer. However, it is now only updating the first record in the selection. Is the edit() function asynchronous? It feels like I have a race condition. Consider the following:

{
   text: "Closed",
   action: function ( e, dt, node, config ) {
      table.rows({ selected: true }).every( function ( rowIdx, tableLoop, rowLoop ) {
        console.log("before updating " + rowIdx);
        editor.edit(this, false)
        .set('status', "closed")
        .submit();
        console.log("done updating " + rowIdx);
      });
     table.rows().deselect(); 
   }
}

Lets say I select 5 records and select the button that runs this code. On my console, I get the messages for before update and after update for each of the 5 records. Then, it shows info that it updated only the first record.
Maybe I should make a single call to edit() and pass an array of row indexes instead?

allan

Don't do an edit per row, rather make use of Editor's multi-row editing ability.

{
  action: function (e, dt, node, config) {
    editor
      .edit(table.rows({ selected: true }).ids(true), false)
      .set("status", "closed")
      .submit(function () {
        table.rows().deselect();
      });
  },
  text: "Closed",
};

barncattech

Thanks very much Allan. I had been using my own php script as the ajax for my Editor declaration, and that was not handling edits on multiple records. Fortunately, it looks like I can use your table.project.php which comes from your generator. And, it works great when I do that. I'm not sure why I felt the need to roll my own - it was many years ago!

barncattech

Actually, I still have an issue. What is the upper limit on how many records can be sent to the edit() function? If I try to have it modify, for example, 614 records, it fails and I get an error dialog that says:

"DataTables warning: table id=project - Requested unknown parameter 'numDocs' for row 1636, column 8. For more information about this error, please see http://datatables.net/tn/4"

allan

On the client-side there isn’t one. But you might need to look at the max_input_vars. You might want to also consider using the submit: ‘changed’ option in the form-options so that only changed values are submitted.

I'm not sure why I felt the need to roll my own

Haha. We are all developers here

Allan

barncattech

By golly, that seems to have done it! I guess too much data was being sent. Here is my new code:

{
text: "Closed",
action: function ( e, dt, node, config ) {

editor
  .edit(table.rows({ selected: true }).ids(true), false, {submit: 'changed'})
  .set("status", "closed")
  .submit(function(){
    table.rows().deselect();
  });
}   

Also - I may have discovered a slight error in the docs on this page:
https://datatables.net/reference/api/rows().every()

The description of the two examples says:
"Consider the following example using each(), which iterates over the row indexes that have been selected - we are required to get the row() object for each row to be able to work with it directly:"
But, the examples will iterate over all the records- not just the selected. I think they should have {selected: true} passed to the rows() function, like this:

table.rows({selected: true}).every( function ( rowIdx, tableLoop, rowLoop ) {
    var data = this.data();
    // ... do something with data(), or this.node(), etc
} );

Am I wrong?

allan

Ah - it is the use of the word “selected”. I had meant it by what was passed into rows(…). That could be {selected:true}, but it could also be any other of row-selector.

Yes, definitely some ambiguity there! I will try to tidy that up - thanks for pointing it out.

Allan