Sort column in dataTable

Question

How can I sort a column by first value without considering what is in brackets?
I added a type sort, but it doesn’t sort as I need.
I added an example of my code on jsfiddle: http://jsfiddle.net/alexserden/8geodt1b/27/
Problem with 9 column.

$("#myTable").DataTable({
    scrollY: '400px',
    width: 100,
    // responsive: true,
    searching: false,
    lengthChange: false,
    // bAutoWidth: true,
    // retrieve: true,
    paging: false,
    scrollX: false,
    data: data,
    columns: [
        {"data": "ourShort"},
        {"data": "espGroup"},
        {"data": "rd"},
        {"data": "city"},
        {"data": "hr"},
        {"data": "shr"},
        {"data": "vacancy"},
        {"data": null,  render: data => {

                if(data.deltaHired<=0){
                    return '<span>'+data.hired + '</span>' +' (▾'+data.deltaHired+')'
                }else{
                    return '<span>'+data.hired + '</span>' + ' (▴'+data.deltaHired+')'
                }
        }},
        {"data": null,className:"textAlignRight", type: 'sort', render: data => {
                if(data.deltaFired>=0){
                    return '<span>'+data.fired+'</span>'+' (▴'+data.deltaFired+')'
                }else{
                    return '<span>'+data.fired+'</span>' +' (▾'+data.deltaFired+')'
                }

            }},
        {"data": null, className:"textAlignRight", type: 'sort', render: data => {
                if(data.deltaStaffing<0){
                    return '<span>'+data.staffing + '%'+'</span>' +' (▾'+data.deltaStaffing*(-1)+'%)'
                }else{
                    return  '<span>'+data.staffing + '%'+'</span>' +' (▴'+data.deltaStaffing+'%)'
                }

        }}

    });
});

Answer 1

Just to clarify the problem (in case I have misunderstood). Looking at column 9 as an example. If you sort that data now, you see this:

The values are not sorted as expected.

What you want to see is this:

To do this I made the following changes:

1) I added 3 new columns at the end of your "columns" section:

{ "data": "hired", "visible": false },
{ "data": "fired", "visible": false },
{ "data": "staffing", "visible": false }

These are columns 11, 12, 13 - but their indexes are 10, 11, 12.

2) I added a new "orderData" element to the three columns which contain the extra data you want to ignore:

{"data": null,  "orderData": [ 10 ], render: ... // the "hired" data
{"data": null,  "orderData": [ 11 ], render: ... // the "fired" data
{"data": null,  "orderData": [ 12 ], render: ... // the "staffing" data

This means that the "hired" column will use data in column index 10 as its sort data. But column index 10 is hidden, so the users do not see it. And this approach is repeated for the additional 2 columns.

Final note:

I made minor changes to your JSON, to make it valid JSON:

For example:

'city': "NewYork", // invalid

"city": "NewYork", // valid

Here is what I ended up using:

let data = [{
    "city": "NewYork",
    "deltaFired": 0,
    "deltaHired": 0,
    "deltaStaffing": "2",
    "espGroup": 1,
    "fired": 9,
    "hired": 0,
    "hr": "Петренко",
    "ourShort": "Магазин 3",
    "rd": "Пузатенко",
    "shr": "65.0",
    "staffing": "68",
    "vacancy": "29.5"
}, {
    "city": "LosSantos",
    "deltaFired": 0,
    "deltaHired": 0.5,
    "deltaStaffing": "1",
    "espGroup": 1,
    "fired": 2,
    "hired": 9.4,
    "hr": "Сидоренко",
    "ourShort": "Магазин 4",
    "rd": "Петренко",
    "shr": "125.0",
    "staffing": "69",
    "vacancy": "45.3"
}, {
    "city": "Kiev",
    "deltaFired": 0,
    "deltaHired": 0,
    "deltaStaffing": "1",
    "espGroup": 1,
    "fired": 6,
    "hired": 9.7,
    "hr": "Выасильченко",
    "ourShort": "Магазин 1",
    "rd": "Иваненко",
    "shr": "109.0",
    "staffing": "29.39",
    "vacancy": "49.0"
}, {
    "city": "Kiev",
    "deltaFired": 0,
    "deltaHired": 0,
    "deltaStaffing": "1",
    "espGroup": 1,
    "fired": 6.2,
    "hired": 9,
    "hr": "Выасильченко",
    "ourShort": "Магазин 1",
    "rd": "Иваненко",
    "shr": "109.0",
    "staffing": "2.6",
    "vacancy": "49.0"
}, {
    "city": "Kiev",
    "deltaFired": 0,
    "deltaHired": 0,
    "deltaStaffing": "1",
    "espGroup": 1,
    "fired": 5.9,
    "hired": 5.5,
    "hr": "Выасильченко",
    "ourShort": "Магазин 1",
    "rd": "Иваненко",
    "shr": "109.0",
    "staffing": "39",
    "vacancy": "49.0"
}, {
    "city": "Kiev",
    "deltaFired": 0,
    "deltaHired": 0,
    "deltaStaffing": "1",
    "espGroup": 1,
    "fired": 6,
    "hired": 9.5,
    "hr": "Выасильченко",
    "ourShort": "Магазин 1",
    "rd": "Иваненко",
    "shr": "109.0",
    "staffing": "29.94",
    "vacancy": "49.0"
}, {
    "city": "Kiev",
    "deltaFired": 0,
    "deltaHired": 0,
    "deltaStaffing": "1",
    "espGroup": 1,
    "fired": 6,
    "hired": 9,
    "hr": "Выасильченко",
    "ourShort": "Магазин 1",
    "rd": "Иваненко",
    "shr": "109.0",
    "staffing": "29.987",
    "vacancy": "49.0"
}];