Sorting newly added column values in datatable not

Question

I have a DataTable which has multiple columns and the sorting function works correctly for all columns except one.

For the column which doesn't work, I am adding the values to the table later on after doing a lazy fetch after a user action, whereas all other values in the columns are loaded when the table is rendered.

Is there a way for the table to get to know the latest values so that the new column can be sorted as well. To add text to the column I use the .text function of Jquery.

Answer 1

The cell().invalidate() function https://datatables.net/reference/api/cell().invalidate()

or row().invalidate() function https://datatables.net/reference/api/row().invalidate()

Might be useful, if called in your callback from your Lazy Fetch.

Answer 2

so I used the datatables rows every function to loop through all the rows in the table. I get the Id which is column 0 and compare it to the id in the lazy loaded JSON data. if it matches I update cell[4] the age column.

run the fiddle below and click the load ages button. even if you sorted it, it should correctly get the ages in there, and they should be sortable.

var Ages = [{
  'id': '1',
  'age': '63'
}, {
  'id': '2',
  'age': '66'
}, {
  'id': '3',
  'age': '22'
}, {
  'id': '4',
  'age': '33'
}];


var table = $('#example').DataTable({

});

$('#addagesBtn').click(function(event) {
  table.rows().every(function(rowIdx, tableLoop, rowLoop) {
    var self = this;
    var d = this.data();
    var id = d[0]
    $.each(Ages, function(i, item) {
      if (item.id === id) {
        d[4] = item.age
        self.data(d);
        self.invalidate();
      }
    });
  });
  table.draw();
})

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>
<link href="https://cdn.datatables.net/1.10.15/css/jquery.dataTables.min.css" rel="stylesheet"/>

<table id="example" class="display" cellspacing="0" width="100%">
  <thead>
    <tr>
      <th>ID</th>
      <th>Name</th>
      <th>Position</th>
      <th>Office</th>
      <th>Age</th>
      <th>Start date</th>
      <th>Salary</th>
    </tr>
  </thead>
  <tfoot>
    <tr>
      <th>ID</th>
      <th>Name</th>
      <th>Position</th>
      <th>Office</th>
      <th>Age</th>
      <th>Start date</th>
      <th>Salary</th>
    </tr>
  </tfoot>
  <tbody>

    <tr>
      <td>1</td>
      <td>Garrett Winters</td>
      <td>Accountant</td>
      <td>Tokyo</td>
      <td></td>
      <td>2011/07/25</td>
      <td>$170,750</td>
    </tr>
    <tr>
      <td>2</td>
      <td>Ashton Cox</td>
      <td>Junior Technical Author</td>
      <td>San Francisco</td>
      <td></td>
      <td>2009/01/12</td>
      <td>$86,000</td>
    </tr>
    <tr>
      <td>3</td>
      <td>Cedric Kelly</td>
      <td>Senior Javascript Developer</td>
      <td>Edinburgh</td>
      <td></td>
      <td>2012/03/29</td>
      <td>$433,060</td>
    </tr>
    <tr>
      <td>4</td>
      <td>Airi Satou</td>
      <td>Accountant</td>
      <td>Tokyo</td>
      <td></td>
      <td>2008/11/28</td>
      <td>$162,700</td>
    </tr>

  </tbody>
</table>

<button id="addagesBtn">
  lazy load ages
</button>