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: /
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/07nk5wob/5/
Share Improve this question asked Nov 9, 2015 at 9:08 RichardRichard 65.6k134 gold badges356 silver badges568 bronze badges4 Answers
Reset to default 6SOLUTION
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
- Orthogonal data example
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/07nk5wob/6/
Working fiddle here: http://jsfiddle/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));
}
});
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!