Make Non Displayable Data To Appear In The Bottom Of The Table

Goal: Any cell that do not contain any data (null or whitepsace) should be always be located at the bottom of the table when you use ASC or DESC. It should be happening on a specif

Solution 1:

You can use $.fn.dataTable.ext.type.order as follows:

<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width">
  <title>Custom Sort</title>
</head>
<body>
  
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.10.25/datatables.min.css"/>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.10.25/datatables.min.js"></script>

 

<table id="example" class="display" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tbody>

        </tbody>
        <tfoot>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </tfoot>
    </table>  
  
  
<script>
  
var data = [
    [
        "Jim Winters",
        "Director",
        "Edinburgh",
        "8422",
        "2011/07/25",
        "$5,300"
    ],
    [
        null,
        "System Architect",
        "Edinburgh",
        "5421",
        "2011/04/25",
        "$3,120"
    ],
    [
        "Garrett Winters",
        "Director",
        "Edinburgh",
        "8422",
        "2011/07/25",
        ""
    ],
    [
        "",
        "Director",
        "Edinburgh",
        "8422",
        "2011/07/25",
        ""
    ],
    [
        "Jim West",
        "Director",
        "Edinburgh",
        "8422",
        "2011/07/25",
        ""
    ],
    [
        "Sandra Brown",
        "Director",
        "Edinburgh",
        "8422",
        "2011/07/25",
        ""
    ]  
]

$( document ).ready(function() {
  console.log( "ready!" );

  $.extend( $.fn.dataTable.ext.type.order, {
    "sortme-asc": function ( a, b ) {
      let x = formatAsc( a );
      let y = formatAsc( b );
      return (x < y) ? -1 : ((x > y) ? 1 : 0);
    },
  
    "sortme-desc": function ( a, b ) {
      let x = formatDesc( a );
      let y = formatDesc( b );
      return (x < y) ? 1 : ((x > y) ? -1 : 0);
    }
  } );

  $('#example').DataTable( {
    "data": data,
    "columnDefs": [ {
      "targets": 0,
      "type": 'sortme'
    } ]
  } );
  
  function formatAsc( z ) {
    return z && z.trim() !== '' ? z : '~~~'; // choose whatever you prefer here
  }  

  function formatDesc( z ) {
    return z && z.trim() !== '' ? z : ''; // choose whatever you prefer here
  }  

});
 
</script>
  
</body>
</html>

This uses two functions (one for asc and one for desc) to provide a replacement value for the null and empty string values. You can use whatever text values you want, to ensure that everything in the column will be sorted before/after the strings you choose to use in these functions.

There is an issue with this:

The initial display does not trigger the custom sort - and unfortunately, I have not (yet?) found a way around that. I tried initComplete, but that did not work. I will update if I find a solution for that part.

---

Update:

In my original code snippet, I had the $('#example').DataTable( {...} code placed before the $.extend( $.fn.dataTable.ext.type.order, {...}) code. I swapped them around so that the order function comes first, and is therefore available for the initial display sort order.