How to set fiter and order data to a DataTables cell

Question

I have a DataTable and I set a cells value through its object.

I don't want to set the cells values directly through jQuery because of other problems found here

How can I set different values for the filtering and ordering of that cell like it can be achieved when you load orthogonal data or when you use HTML5 data-... attributes

Here's a code snippet for better understanding the problem - click to sort by speed after running it

$(document).ready(function() {
  var dt = $('#example').DataTable({});
  
  //this is not enough as the change doesn't reflect on sorting and filtering
  dt.cell( $("#obj2_speed") ).data(9 + 'km/h') ;
  
  //this doesn't work like I would like to
  /*dt.cell( $("#obj1_speed") ).data({
    "_": 7 + 'km/h',
    "sort": 7,
    "filter": 'seven'
  });*/
});

<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" width="100%" cellspacing="0">
  <thead>
    <tr>
      <th>Object</th>
      <th>Speed</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>object 1</td>
      <td id="obj1_speed" data-sort="80" data-filter="eighty">80km/h</td>
    </tr>
    <tr>
      <td>object 2</td>
      <td id="obj2_speed" data-sort="8" data-filter="eight">8km/h</td>
    </tr>
    <tr>
      <td>object 3</td>
      <td id="obj3_speed" data-sort="90" data-filter="ninety">90km/h</th>
    </tr>
  </tbody>
</table>

My use case for the above sounds something like this:

I initially load the DataTable from a database but then I have to do repeated ajax requests which need to update different portions of the DataTable, and those updates need to be reflected inside it.

Say I keep my browser open on my page which displays my DataTable sorted by speed. After 5 seconds an ajax request/response comes in and updates the speed of a cell. I want that updated and the row moved upwards or downwards because I'm looking at it ordered by speed. I already achieved that but with small shortcomings given by the fact that "8km/h" is a string and won't get ordered correctly with "80km/h" and "90km/h"

Answer 1

I had a real struggle by updating data-order etc attributes dynamically. It does not really seem to work. I would have thought that a mix of direct node() access combined with invalidate() would do the trick, but I guess you simply cannot change HTML5 attributes on the fly and force dataTables to reindex. See my attempt here -> http://jsfiddle.net/m9ymz187/

---

However, your update of the question suggests that you can take a far easier approach by using a custom sorting plugin :

jQuery.extend( jQuery.fn.dataTableExt.oSort, {
  "speed-pre": function ( a ) {
     return parseFloat( a.match(/[+-]?\d+(\.\d+)?/) )
  }
});

var table = $('#example').DataTable({
  columnDefs : [
    { targets: 0, type: 'speed' }
  ]
}) 

Now you can change the cell() content on the fly, draw() them and still get the correct order. See demo -> http://jsfiddle.net/wmxz20ae/

The demo is inserting random speed+km/h in 2 secs intervals and the table sorting updates nicely. So use the "plugin", your dt.cell( <selector> ).data( newData ) and then draw().

Answer 2

Use a server-side scripting language like PHP or Rails.

True enough, you can store unlimited amounts of data in JSON but the fact remains that it's still difficult to parse large amounts of data into JSON.

That's where the database comes in.

You can type all of that information into the table if you'd like but it would be easier to use something like Ruby on Rails to create new objects and then load them in from the server.

Have the speed of the object/record in it's own column in your database and use an ORDER_BY 'speed' conditional statement in your query. Hide an ID column in the table (How to hide columns in HTML table?) and then when you perform your AJAX request, sort the table by the ID column (I've never used JavaScript data-tables before but this works for regular HTML5 tables: Sorting HTML table with JavaScript).