rowReorder and save the new sorting order in mysql table

Brecht2727

Hi, this is my table init:

  var table = new DataTable('#categoriesTable', {
    dom: "Bfrtilp",
    buttons: ["excel", "pdf"],
    processing: true,
    serverSide: true,
    serverMethod: 'post',
    /*rowReorder: {
      selector: '.reorder'
    },*/
    rowReorder: true,
    ajax: {
      url: 'assets/ajax/ajax-get-table-games-cats-overview.php'
    },
    //scrollX: !0,
    columns: [
      { data: 'id' },
      { data: 'sort_order' },
      { data: 'cat_name' },
      { data: 'cat_icon' },      
      { data: 'active' },
      { data: 'order_id' },
      { data: 'actions' }
    ],
    columnDefs: [
      
        {
            className: 'reorder',
            render: () => '≡',
            targets: 1
        }, 
    ],    
    order: [[0, "desc"]]
  });

table.on('row-reorder', function (e, diff, edit) {
    let result = 'Reorder started on row: ' + edit.triggerRow.data()[1] + '<br>';
 
    for (var i = 0, ien = diff.length; i < ien; i++) {
        let rowData = table.row(diff[i].node).data();
 
        result +=
            `${rowData[1]} updated to be in position ${diff[i].newData} ` +
            `(was ${diff[i].oldData})<br>`;
    }
 
    document.querySelector('#result123').innerHTML = 'Event result:<br>' + result;
});

This is the result i am get back. Everything is undefined.

Event result:
Reorder started on row: undefined
undefined updated to be in position undefined (was undefined)
undefined updated to be in position undefined (was undefined)
undefined updated to be in position undefined (was undefined)
undefined updated to be in position undefined (was undefined)

kthorngren

There are a couple issues:

1. You are using objects not arrays for the row data. edit.triggerRow.data()[1] needs to be edit.triggerRow.data().sort_order and ${rowData[1]} needs to be ${rowData.sort_order}.
2. position undefined (was undefined). These are the newData and oldData fields which are undefined. A quick forum search found this thread which was solved by setting rowReorder.dataSrc.

Here is your code in a server side processing test case with the above updates:
https://live.datatables.net/pojageyo/1/edit

Kevin

Brecht2727

Hi Kevin, thanks for helping me out here.
I changed your code to what i need but now i am trying to save the new order into the mysql table with following fields:

id, cat_name, cat_icon, active, order_id

The order_id should be updated. Any idea?

kthorngren

Its hard to say without seeing what you are doing. I suspect you will want to use $.ajax() to send the JSON array of the changes to the server, for example:

[
    {
        "id": 1,
        "order_id": 2
    },
    {
        "id": 12,
        "order_id": 3
    },
    {
        "id": 3,
        "order_id": 1
    }
]

Then loop through the array and use MySql UPDATE to update each id with the changed order_id. I assume you set the rowReorder.dataSrc to order_id?

Kevin

Brecht2727

I will post my code again to show you what i am trying to do but it is not good:

For the jQuery:

  var table = new DataTable('#categoriesTable', {
    dom: "Bfrtilp",
    buttons: ["excel", "pdf"],
    processing: true,
    serverSide: true,
    serverMethod: 'post',
    rowReorder: {
      selector: '.reorder',
      dataSrc: 'order_id'
    },
    ajax: {
      url: 'assets/ajax/ajax-get-table-games-cats-overview.php'
    },
    //scrollX: !0,
    columns: [
      { data: 'id' },
      { data: 'test' },
      { data: 'cat_name' },
      { data: 'cat_icon' },      
      { data: 'active' },
      { data: 'order_id' },
      { data: 'actions' }
    ],
    columnDefs: [
      {
        className: 'reorder',
        render: () => '≡',
        targets: 1,
        orderable: false
      }
    ],    
    //order: [[5, "asc"]]
  });

  table.on('row-reorder', function (e, diff, edit) {
    console.log(edit.triggerRow.data());
    let result = 'Reorder started on row: ' + edit.triggerRow.data().cat_name + '<br>';

    for (var i = 0, ien = diff.length; i < ien; i++) {
        let rowData = table.row(diff[i].node).data();

        result +=
            `${rowData.cat_name} (id = ${rowData.id}) updated to be in position ${diff[i].newData} ` +
            `(was ${diff[i].oldData})<br>`;

        $.ajax({
          type: 'POST',
          url: 'assets/ajax/ajax-content-games-categories-change-order.php',
          dataType: 'json',
          cache: false,
          data: {id: rowData.id, newdata: diff[i].newData, olddata: diff[i].oldData}, 
          success: function (data) {
            console.log(data);
          },
          error: function (data) {
            console.log('An error occurred');
            console.log(data);
          }
        });

    }

    document.querySelector('#result123').innerHTML = 'Event result:<br>' + result;
  });

For the ajax:

  $id = $_REQUEST['id'];
  $newdata = $_REQUEST['newdata'];
  $olddata = $_REQUEST['olddata'];
  
  $data = array(
    'order_id' => $newdata
  );

  $db->where('id', $id);
  $db->update('game_categories', $data);

That is what i need to do: 'Then loop through the array and use MySql UPDATE to update each id with the changed order_id'

kthorngren

It looks like you are sending one ajax request per row. This might be too slow for a smooth update. I think you will want to create an array of updates, like I posted above, to send in one request. I think the loop would look like this:

    for (var i = 0, ien = diff.length; i < ien; i++) {
        let rowData = table.row(diff[i].node).data();
        update.push( {id: rowData.id,  order_id: diff[i].newData} )
    }

Send that as that ajax.data. The server should receive JSON data like this:

[
    {
        "id": 1,
        "order_id": 2
    },
    {
        "id": 12,
        "order_id": 3
    },
    {
        "id": 3,
        "order_id": 1
    }
]

It looks like you are using PHP. I'm not familiar with PHP so can't give an example.

You might also want to set owReorder.update false with server side processing and call draw() in the success function to update the row order from the DB.

Kevin

Brecht2727

Yes, i think that your solution to send the JSON in one ajax call is better so i have updated my code.
But i have still a problem with all other ids that are also in the database and not updated so they are not in the JSON array. That brings me to duplicate order_id items.

  var table = new DataTable('#categoriesTable', {
    dom: "Bfrtilp",
    buttons: ["excel", "pdf"],
    processing: true,
    serverSide: true,
    serverMethod: 'post',
    rowReorder: {
      selector: '.reorder',
      dataSrc: 'id' 
    },
    ajax: {
      url: 'assets/ajax/ajax-get-table-games-cats-overview.php'
    },
    //scrollX: !0,
    columns: [
      { data: 'id' },
      { data: 'test' },
      { data: 'cat_name' },
      { data: 'cat_icon' },      
      { data: 'active' },
      { data: 'order_id' },
      { data: 'actions' }
    ],
    columnDefs: [
      {
        className: 'reorder',
        render: () => '≡',
        targets: 1,
        orderable: false
      }
    ],    
    order: [[5, "asc"]]
  });

  table.on('row-reorder', function (e, diff, edit) {

    var update = [];
    
    for (var i = 0, ien = diff.length; i < ien; i++) {
        let rowData = table.row(diff[i].node).data();
        update.push( {id: rowData.id,  order_id: diff[i].newData} )
    } 
    
        $.ajax({
          type: 'POST',
          url: 'assets/ajax/ajax-content-games-categories-change-order.php',
          dataType: 'json',
          cache: false,
          data: {update: update}, 
          success: function (data) {
            console.log(data);
            $('#categoriesTable').DataTable().ajax.reload( null, false );
          },
          error: function (data) {
            console.log('An error occurred');
            console.log(data);
          }
        });
  });

  $update = $_REQUEST['update'];
  
  foreach($update as $u) {
    $data = array(
      'order_id' => $u['order_id']
    );

    $db->where('id', $u['id']);
    $db->update('game_categories', $data);    
  }

kthorngren

That is possible if you don't start with unique values for the order_id. I don't know your situation nor data but you will probably need to reset the order_id field to have unique values. Then I would set the fields UNIQUE constraint to true. Once you have unique values then they should never duplicate as they are swapped in the RowReorder process which you see in the diff variable.

Kevin

Brecht2727

It is not clear to me. But if someone adds a new category the order_id is always 0 from the start. So if they add 10 new categories we have 10 times 0 in order_id field.

Can you explain more in detail what you want to say?
I am using MySQL database and PHP code

kthorngren

This article describes unique constraints. Without knowing your solution my suggestion is that you will need to use a query to get the highest numeric value in the order_id column then increment that to set the order_id value when adding a new category.

Kevin

Brecht2727

Hi Kevin,
If I follow your idea, I still have duplicate entries in my database on save and because the CONSTRAINT is on that order_id field I get an error. P.e. if you want to update the first item order_id = 1 there is another order_id = 1 needs to be changed later in the array

kthorngren

That makes sense. So having the UNIQUE constraint won't work. Turn it off. If you start with a unique set of order_id values and only update them with the RowReorder process then they will stay unique. There will be duplicates for a short period while updating all the changes but once done there shouldn't be any duplicates.

Kevin

Brecht2727

Hi Kevin,

Finally it works. Thanks for helping me!

1. dataSrc must be set to 'order_id' and not to the auto_increment 'id' otherwise you will continue to get duplicate order_ids.
2. I dropped the UNIQUE constraint off again.
3. And when creating a new category the 'order_id' must find the last 'order_id' and do a +1

Complete code below:

  var table = new DataTable('#categoriesTable', {
    dom: "Bfrtilp",
    buttons: ["excel", "pdf"],
    processing: true,
    serverSide: true,
    serverMethod: 'post',
    rowReorder: {
      selector: '.reorder',
      dataSrc: 'order_id' 
    },
    ajax: {
      url: 'assets/ajax/ajax-get-table-games-cats-overview.php'
    },
    //scrollX: !0,
    columns: [
      { data: 'id' },
      { data: 'test' },
      { data: 'cat_name' },
      { data: 'cat_icon' },      
      { data: 'active' },
      { data: 'order_id' },
      { data: 'actions' }
    ],
    columnDefs: [
      {
        className: 'reorder',
        render: () => '≡',
        targets: 1,
        orderable: false
      },  
      { 
        targets: [3,4,5,6],
        class: 'text-center'
      },
      { 
        targets: [3,6],
        orderable: false
      }
    ],
    order: [[5, "asc"]]
  });

  table.on('row-reorder', function (e, diff, edit) {
    var update = [];
    
    for (var i = 0, ien = diff.length; i < ien; i++) {
        let rowData = table.row(diff[i].node).data();
        update.push( {id: rowData.id,  order_id: diff[i].newData} );
    } 
    
    //console.log(update);
    
    $.ajax({
      type: 'POST',
      url: 'assets/ajax/ajax-content-games-categories-change-order.php',
      dataType: 'json',
      cache: false,
      data: {update: update}, 
      success: function (data) {
        $('#categoriesTable').DataTable().ajax.reload( null, false );
      },
      error: function (data) {
        console.log('An error occurred');
        console.log(data);
      }
    });
  });

ajax-content-games-categories-change-order.php file contains:

  $update = $_REQUEST['update'];
  
  foreach($update as $u) {
    $data = array(
      'order_id' => $u['order_id']
    );

    $db->where('id', $u['id']);
    $db->update('game_categories', $data);    
  }