DataTables sorts numbers without spaces and strings

user_1118217 0
Viewed 39

After fiddling with my table layout I give up, I am looking for up to date and elegant way to sort this table layout with such numbers.

I tried this popular solution.

jQuery.extend( jQuery.fn.dataTableExt.oSort, {
  "formatted_numbers-pre": function ( a ) {
    a = (a===" ") ? 0 : a.replace( /[^\d\-\.]/g, "" );
    return parseFloat( a );
  },

  "formatted_numbers-asc": function ( a, b ) {
    return a - b;
  },

  "formatted_numbers-desc": function ( a, b ) {
    return b - a;
  }
});


jQuery(document).ready(function() {
    jQuery('#mieszkaniaList').DataTable({
        responsive: true,
        "columnDefs": [
            { "type": "formatted_numbers", "targets": '_all'},
         ],
        "language": {
            "decimal": ",",
            "thousands": " ",
            "lengthMenu": "Pokaż _MENU_ mieszkań na stronę",
            "zeroRecords": "Niestety dla tego zakresu nie znaleźliśmy pasujących mieszkań ...",
            "info": "Strona _PAGE_ z _PAGES_",
            "infoEmpty": "Niestety brak mieszkań...",
            "processing":     "Przygotowujemy mieszkania...",
            "search": "Szukaj",
            "paginate": {
                "first":      "Pierwsza",
                "last":       "Ostatnia",
                "next":       "Następna",
                "previous":   "Poprzednia"
            },
            "infoFiltered": "(z _MAX_ mieszkań)"
        }
    });
});
Nr Area level nr2 Price Price per m2
01A 10 m2 1 1 502 200 PLN 7 200 PLN za m2
02A 20 m2 2 2 1 502 200 PLN 10 200 PLN za m2
03A 90 m2 3 3 2 502 200 PLN 15 200 PLN za m2
04A 120 m2 4 4 202 200 PLN 5 200 PLN za m2
datatable sorting
1 Answers

Your approach looks close to me, but I can suggest some changes:

  1. In your case you only need the -pre function - you do not need the -asc and -desc functions. In fact there is a note in the documentation which states that you cannot combine usage of -pre with the other two functions:

Note that in DataTables 1.10 a pre-formatter cannot be used with custom -asc and -desc methods - to use custom ordering functions you cannot apply a pre-formatter. This limitation will be addressed in the next major version of DataTables.

If you use -pre to convert your numbers-plus-text to numbers-only values, then sorting will use the numbers-only values automatically - and the data will automatically be handled as numeric, not text.

  1. You need to handle the fact that some of your data contains strings with numbers ("m2"). A very simple way to handle these is to replace all occurrences of m2 with nothing, before performing the regex replacement.

Here is a runnable demo:

$(document).ready(function() {

  $.fn.dataTable.ext.type.order['formatted_numbers-pre'] = function ( a ) {
    a = a.replaceAll("m2", "");
    a = (a===" ") ? 0 : a.replace( /[^\d\-\.]/g, "" );
    console.log( a ); // for debugging only
    return parseFloat( a );
  };

  var table = $('#example').DataTable( {
    "columnDefs": [
      { "type": "formatted_numbers", "targets": '_all'},
    ]
  } );

} );
<!doctype html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Demo</title>
  <script src="https://code.jquery.com/jquery-3.5.0.js"></script>
  <script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.22/css/jquery.dataTables.css">
  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">

</head>

<body>

<div style="margin: 20px;">

    <table id="example" class="display dataTable cell-border" style="width:100%">
        <thead>
            <tr>
                <th>Nr</th>
                <th>Area</th>
                <th>Level</th>
                <th>nr2</th>
                <th>Price</th>
                <th>Price per m2</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>01A</td>
                <td>10 m2</td>
                <td>1</td>
                <td>1</td>
                <td>502 200 PLN</td>
                <td>7 200 PLN za m2</td>
            </tr>
            <tr>
                <td>02A</td>
                <td>20 m2</td>
                <td>2</td>
                <td>2</td>
                <td>1 502 200 PLN</td>
                <td>10 200 PLN za m2</td>
            </tr>
        </tbody>
    </table>

</div>


</body>
</html>

There is a logging statement in the above code, so you can see what data the -pre function is generating.

user_125673650
Related