4

I am using DataTables version 1.10. I want to make a column sortable by a numeric value, when the value shown in the column is not numeric.

I can see that what I need to do is add a data-sort attribute to each table cell. I've tried adding this with the createdRow method, but although I can see the attribute in the HTML, it's not sorting numerically.

This is my code:

var data = [
      {
          'name': 'France',
          'played': 1000,
          'won': 11
      },
      {
          'name': 'England',
          'played': 1000,
          'won': 100
      },
      {
          'name': 'Germany',
          'played': 1000,
          'won': 109
      }
  ];
    $.each(data, function(i, d) {
        d.won_percent = (d.won / d.played) * 100;
        d.won_display = d.won + '/' + d.played;
        d.won_display += ' (';
        d.won_display += Math.round(d.won_percent * 10) / 10;
        d.won_display += '%)';
    });
  var columns = [
    { "data": "name",
      "title": "Country"
    },
    { "data": "won_display",
      "title": "Games won"
    },
    { "data": null,
      "title": "Notes",
     "defaultContent": 'Some other text here, included just to test that responsive works'
    }  
  ];
  var html = '<table class="table table-bordered table-hover" cellspacing="0" width="100%" id="myTable"></table>';
  $('#table').html(html);
  $("#myTable").DataTable({
    data: data,
    columns: columns,
    order:[[1, "desc"]],
    responsive: true,
    paging: false,
    searching: false,
    createdRow: function (row, data, rowIndex) {
      $.each($('td', row), function (colIndex) {
        if (colIndex === 1) {
          $(this).attr('data-order', data.won_percent);
        }
      });
    }
  });
});

How can I get my table to sort by the value of d.won_percent?

Note that I'm also building a responsive table, which means that I need to be careful about using render events.

JSFiddle here: http://jsfiddle.net/07nk5wob/5/

Improve this question

4 Answers 4

Reset to default
6

SOLUTION

You can use orthogonal data which is the term jQuery DataTables uses for a way of providing different data for display, sort and filter operations.

Also you need to explicitly state column data type with type: "num".

$.each(data, function (i, d) {
    d.won_percent = {
        sort: (d.won / d.played) * 100
    };
    d.won_percent.display = 
        d.won + '/' + d.played +
        ' (' + Math.round(d.won_percent.sort * 10) / 10 + '%)';
});

// ... skipped ...

{
    "data": "won_percent",
    "title": "Games won",
    "type": "num",
    "render": {
        "_": "display",
        "sort": "sort"
    }
},

DEMO

See updated jsFiddle for code and demonstration.

LINKS

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Actually I'm afraid it's not working properly. See here with updated numbers: jsfiddle.net/07nk5wob/34
@Richard, I have corrected my code, Apparently DataTables doesn't do type detection when orthogonal data is used so it needs to be stated explicitly with type: "num".
5

here is a native way of doing it

add this "columnDefs" object inside the datatables settings object

  columnDefs: [
  { 
     targets: [1], // cell target
         render: function(data, type, full, meta) {
             if(type === "sort") {
                var api = new $.fn.dataTable.Api(meta.settings);
                var td = api.cell({row: meta.row, column: meta.col}).node(); // the td of the row
                data = $(td).attr('data-order'); // the data it should be sorted by
             }
             return data;
         }
     },
  ],

here is a working fiddle : http://jsfiddle.net/07nk5wob/6/

Improve this answer

1 Comment

This code it's just awesome! @Enjoyted you save me a week of researching, thank you so much!
0

Working fiddle here: http://jsfiddle.net/annoyingmouse/07nk5wob/9/

Basically you need a special sorting thingie:

jQuery.extend( jQuery.fn.dataTableExt.oSort, {
   "special-pre": function ( a ) {
       var regExp = /\(([^)]+)\)/;
       var matches = regExp.exec(a);
       return parseFloat(matches[1]);
   },
   "special-asc": function ( a, b ) {
       return ((a < b) ? -1 : ((a > b) ? 1 : 0));
   },
   "special-desc": function ( a, b ) {
       return ((a < b) ? 1 : ((a > b) ? -1 : 0));
   }
});
Improve this answer

Comments

0

Here is a working fiddle for your problem.

You can implement your own sorting plugin to do that. It is quite simple. Here I have extend the DataTable plugin by adding a percentage sorting method. You can see in the percentage-pre method, I'm only returning the numeric value which you want to sort.

jQuery.extend( jQuery.fn.dataTableExt.oSort, {
   "percentage-pre": function ( a ) {
       var regExp = /\(([^)]+)\)/;
       var matches = regExp.exec(a);
       var exactVal = matches[1];
       return parseFloat(exactVal.slice(0, -1));
   },
   "percentage-asc": function ( a, b ) {
       return ((a < b) ? -1 : ((a > b) ? 1 : 0));
   },
   "percentage-desc": function ( a, b ) {
       return ((a < b) ? 1 : ((a > b) ? -1 : 0));
   }
});

Then in the above fiddle you can see, I am adding below code in DataTable init method:

columnDefs: [
   { type: 'percentage', targets: 1 }
 ]

Hope this is what you need!

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.