jQuery DataTables numeric sort in column containing hidden HTML

Question:

Making use of jQuery DataTables , we can indicate the intended sorting type :

$('#example').dataTable( {
  "aoColumnDefs": [
    { "sType": "numeric", "aTargets": [ 0 ] }
  ]
});

But if HTML is present, hidden or not, the numeric sorting stops working:

var oTable = $("#products").dataTable({
  "aaData": [
    [1, "Dinner", "<span>? 1</span>1"],
    [2, "Study", "<span>? 54</span>54"],
    [2, "Study", "<span>? -5</span>-5"],
    [3, "Sleep", "<span>? 6</span>6"],
    [4, "Sleep", "<span> ?</span>"],
    [5, "Sleep", "<span>3 ?</span>3"],
    [6, "Study", "<span>? 60</span>60"]
  ],
  "aoColumns": [{
    "sClass": "center",
    "bSortable": false
  }, {
    "sClass": "center",
    "bSortable": false
  }, {
    "sClass": "center",
    "bSortable": true,
    "sType": "numeric"
  }]
});
tr.row_selected td {
  background-color: red !important;
}
td > span {
  display: none;
}
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<link href="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/css/jquery.dataTables.css" />
<script src="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js"></script>
<div id="table_container">
  <table id="products">
    <thead>
      <tr>
        <th>id</th>
        <th>name</th>
        <th>quantity</th>
      </tr>
    </thead>
    <tbody></tbody>
  </table>
</div>

Demonstration of the problem also in JSFiddle .

The problem here is that the hidden <span/> is necessary to provide additional functionality in the table, but it cannot be obstructing the ordering of the data present in each column.

Question

How to proceed with a numerical sort in a column whose cells contain hidden HTML?

Answer:

Effectively, the sorting is based on the contents of the cells, so even if hidden, the HTML present in the cell will make it impossible to sort it numerically.

The solution in these cases is to create an auxiliary function to sort the desired column(s).

For this purpose, DataTables allows you to expand its structure to apply a custom function for ascending and descending sorting, whose same affects the value passed in the sType parameter:

jQuery.fn.dataTableExt.oSort["meuValorSType-desc"] = function (x, y) {
    // código aqui
};

jQuery.fn.dataTableExt.oSort["meuValorSType-asc"] = function (x, y) {
    // código aqui
}

Solution

To sort numerically, and according to the values ​​presented in the example of the question, there are some considerations to take into account:

  • Remove the HTML
  • Be aware of negative numbers
  • Be aware of empty values

DataTables provides us with two parameters, above represented by x and y , which contain the cell values:

console.log(x);   // retorna: <span>? 54</span>54

As we have HTML in the cell value, and within that HTML the same value visible in the cell on which we intend to sort, we can pass the same to an object:

console.log($(x));   // retorna: Object[span]

Where after making use of jQuery's .text() method we are left with only its content:

console.log($(x).text());   // retorna: ? 54

Then we have to remove the ? and the blank space where for this purpose we use a regular expression that aims to leave only digits and the - sign, an expression that we use in the .replace() method of JavaScript that will return only the values ​​that we want to preserve:

console.log($(x).text().replace(/[^0-9.-]/g, ""));   // retorna: 54

Now that we have the cell value, we need to be aware that it is empty:

if (!meuValor) {
  // está vazio, aplicar lógica adequada
}

Putting all this together and applying it to the parameter represented by x and y :

// instancia variáveis com X e Y apenas com o número na célula
var myX = $(x).text().replace(/[^0-9.-]/g, ""),
    myY = $(y).text().replace(/[^0-9.-]/g, "");

// Se o X está vazio, atribui um valor negativo super elevado
// cujo mesmo sabemos nunca vir a ser utilizado nas células
if (!myX) myX = -9999999999999;

// Se o X está vazio, atribui um valor negativo super elevado
// cujo mesmo sabemos nunca vir a ser utilizado nas células
if (!myY) myY = -9999999999999;

Now that we have the logic in place, we can apply it to our custom functions for sorting:

/* Função para ordenação descendente para colunas com o sType="meuValorSType"'
 */
jQuery.fn.dataTableExt.oSort["meuValorSType-desc"] = function (x, y) {
    var myX = $(x).text().replace(/[^0-9.-]/g, ""),
        myY = $(y).text().replace(/[^0-9.-]/g, "");
    if (!myX) myX = -9999999999999;
    if (!myY) myY = -9999999999999;

    // Devolve à DataTables o resultado de X-Y para ela saber qual o menor dos dois
    return myX - myY;
};

/* Função para ordenação ascendente para colunas com o sType="meuValorSType"'
 */
jQuery.fn.dataTableExt.oSort["meuValorSType-asc"] = function (x, y) {

    // Não precisamos repetir código, chamamos a ordenação descendente
    // mas trocamos os valores de entrada.
    return jQuery.fn.dataTableExt.oSort["meuValorSType-desc"](y, x);
}

Example

Now that we've seen how to solve the question, let's apply all this to the code present in the question:

Example also in JSFiddle .

jQuery.fn.dataTableExt.oSort["myNumeric-desc"] = function(x, y) {
  var myX = $(x).text().replace(/[^0-9.-]/g, ""),
    myY = $(y).text().replace(/[^0-9.-]/g, "");
  if (!myX) myX = -9999999999999;
  if (!myY) myY = -9999999999999;
  return myX - myY;
};

jQuery.fn.dataTableExt.oSort["myNumeric-asc"] = function(x, y) {
  return jQuery.fn.dataTableExt.oSort["myNumeric-desc"](y, x);
}

var oTable = $("#products").dataTable({
  "aaData": [
    [1, "Dinner", "<span>? 1</span>1"],
    [2, "Study", "<span>? 54</span>54"],
    [2, "Study", "<span>? -5</span>-5"],
    [3, "Sleep", "<span>? 6</span>6"],
    [4, "Sleep", "<span> ?</span>"],
    [5, "Sleep", "<span>3 ?</span>3"],
    [6, "Study", "<span>? 60</span>60"]
  ],
  "aoColumns": [{
    "sClass": "center",
    "bSortable": false
  }, {
    "sClass": "center",
    "bSortable": false
  }, {
    "sClass": "center",
    "bSortable": true,
    "sType": "myNumeric"
  }]
});
tr.row_selected td {
  background-color: red !important;
}
td > span {
  display: none;
}
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<link href="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/css/jquery.dataTables.css" />
<script src="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js"></script>
<div id="table_container">
  <table id="products">
    <thead>
      <tr>
        <th>id</th>
        <th>name</th>
        <th>quantity</th>
      </tr>
    </thead>
    <tbody></tbody>
  </table>
</div>
Post Views: 14
Scroll to Top