jQuery Datatables numerical column sorting containing hidden HTML

Asked

Viewed 1,179 times

4

Making use of jQuery Datatables, we can indicate the type of ordering sought:

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

But if HTML is present, hidden or not, type ordering numeric crashing:

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" rel="stylesheet" />
<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 <span/> hidden is required to provide additional functionality in the table, but it cannot be obstructing the sorting of data present in each column.

Question

How to do a numeric sort in a column whose cells contain hidden HTML ?

jquery classification

1 answer

4


In fact, sorting is based on the contents of the cells, so even 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 structure has an impact on the value passed in the parameter sType:

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 using the values shown in the question example, there are some considerations to take:

  • 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 values of the cells:

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

Since we have HTML in the value of the cell, and within that HTML the same value visible in the cell over which we intend to perform the sorting, we can pass it to an object:

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

Where then making use of the method .text() of jQuery we are left with only its content:

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

Next we have to remove the ? and the white space where we use a regular expression to leave only digits and the sign -, expression that we use in the method .replace() of Javascript that will return us only the values we intend to preserve:

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

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

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

Joining all this and applying 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 ready, we can apply the same in our custom functions for ordering:

/* 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 have seen how to resolve the issue, we will apply all this to the code in the question:

Also in the 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" rel="stylesheet" />
<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>

Browser other questions tagged jquery classification

You are not signed in. Login or sign up in order to post.