javascript - Sort numeric data.table values that are formatted

Question

I am using datatables to create my table.

Find below my minimum viable example:

jQuery(document).ready(($) => {
  function loadHardware() {
    var results = {
      "profRigHardware": [{
          "title": "Product1",
          "permalink": "http://test.com/computer-hardware/product1/",
          "smallImg": "http://test.com/content/uploads/2018/07/product1.jpg",
          "daily_netProfit": "165.99",
        },
        {
          "title": "Product2",
          "permalink": "http://test.com/computer-hardware/product2/",
          "smallImg": "http://test.com/content/uploads/2018/07/product2.jpg",
          "daily_netProfit": "161.99",
        },
        {
          "title": "Product3",
          "permalink": "http://test.com/computer-hardware/product3/",
          "smallImg": "http://test.com/content/uploads/2018/07/product3.jpg",
          "daily_netProfit": "-6.06",
        },
        {
          "title": "Product4",
          "permalink": "http://test.com/computer-hardware/product5/",
          "smallImg": "http://test.com/content/uploads/2018/07/product5.jpg",
          "daily_netProfit": "-19.2",
        },
        {
          "title": "Product5",
          "permalink": "http://test.com/computer-hardware/product4/",
          "smallImg": "http://test.com/content/uploads/2018/07/product4.jpg",
          "daily_netProfit": "-116.06",
        },
        {
          "title": "Product6",
          "permalink": "http://test.com/computer-hardware/product5/",
          "smallImg": "http://test.com/content/uploads/2018/07/product5.jpg",
          "daily_netProfit": "-0.06",
        },
        {
          "title": "Product7",
          "permalink": "http://test.com/computer-hardware/product5/",
          "smallImg": "http://test.com/content/uploads/2018/07/product5.jpg",
          "daily_netProfit": "-18.24",
        },
        {
          "title": "Product8",
          "permalink": "http://test.com/computer-hardware/product5/",
          "smallImg": "http://test.com/content/uploads/2018/07/product5.jpg",
          "daily_netProfit": "75.68",
        },
        {
          "title": "Product9",
          "permalink": "http://test.com/computer-hardware/product5/",
          "smallImg": "http://test.com/content/uploads/2018/07/product5.jpg",
          "daily_netProfit": "863.31",
        },
        {
          "title": "Product10",
          "permalink": "http://test.com/computer-hardware/product5/",
          "smallImg": "http://test.com/content/uploads/2018/07/product5.jpg",
          "daily_netProfit": "20.1",
        }
      ]
    };
    const rentabilityHtml = function(daily_netProfit) {
      if (daily_netProfit < 0) {
        return `<div style="color:red;"><b>$${daily_netProfit}/day</b></div>`
      } else {
        return `<div style="color:green;"><b>$${daily_netProfit}/day</b></div>`
      }
    }
    //transform data set
    let dataSet = results.profRigHardware.map((item, i) => [
      `<img src="${ item.smallImg }" alt="${ item.title }" height="42" width="42"> 
         <a href="${item.permalink}" target="_blank">
            ${item.title}
             </a>`,
      parseFloat(item.daily_netProfit),
    ])

    $('#allHardwareOverview').DataTable({
      data: dataSet,
      destroy: true,
      iDisplayLength: 25,
      responsive: true,
      "bInfo": false,
      "order": [
        [1, 'desc']
      ],
      columns: [{
          title: "Model"
        },
        {
          title: "Profitability",
          render: function(profit) {
            return rentabilityHtml(parseFloat(profit))
          }
        }
      ],
      "initComplete": function(settings, json) {
        $('#datatablediv').css('opacity', 1);
      }
    });
  }
  loadHardware();
});

<link href="https://cdn.datatables.net/1.10.18/css/jquery.dataTables.min.css" rel="stylesheet" />
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<link href="https://cdn.datatables.net/1.10.18/css/dataTables.bootstrap4.min.css" rel="stylesheet" />
<script src="https://cdn.datatables.net/1.10.18/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.18/js/dataTables.bootstrap4.min.js"></script>

<div class="tab-pane fade show active" id="all" role="tabpanel" aria-labelledby="all-tab">
  <div class="table-responsive overflow-x:auto;">
    <table id="allHardwareOverview" style="width:100%; float: left;" class="table table-bordered"></table>
  </div>
</div>

Answer 1

On the render() method you can check the mode type using render(data, type), and based on this mode type, just format the data only on display mode and return raw data for the other modes.

Read Documentation here: https://datatables.net/reference/option/columns.render

You can see how your example works with this change:

jQuery(document).ready(($) => {
  function loadHardware() {
    var results = {
      "profRigHardware": [{
          "title": "Product1",
          "permalink": "http://test.com/computer-hardware/product1/",
          "smallImg": "http://test.com/content/uploads/2018/07/product1.jpg",
          "daily_netProfit": "165.99",
        },
        {
          "title": "Product2",
          "permalink": "http://test.com/computer-hardware/product2/",
          "smallImg": "http://test.com/content/uploads/2018/07/product2.jpg",
          "daily_netProfit": "161.99",
        },
        {
          "title": "Product3",
          "permalink": "http://test.com/computer-hardware/product3/",
          "smallImg": "http://test.com/content/uploads/2018/07/product3.jpg",
          "daily_netProfit": "-6.06",
        },
        {
          "title": "Product4",
          "permalink": "http://test.com/computer-hardware/product5/",
          "smallImg": "http://test.com/content/uploads/2018/07/product5.jpg",
          "daily_netProfit": "-19.2",
        },
        {
          "title": "Product5",
          "permalink": "http://test.com/computer-hardware/product4/",
          "smallImg": "http://test.com/content/uploads/2018/07/product4.jpg",
          "daily_netProfit": "-116.06",
        },
        {
          "title": "Product6",
          "permalink": "http://test.com/computer-hardware/product5/",
          "smallImg": "http://test.com/content/uploads/2018/07/product5.jpg",
          "daily_netProfit": "-0.06",
        },
        {
          "title": "Product7",
          "permalink": "http://test.com/computer-hardware/product5/",
          "smallImg": "http://test.com/content/uploads/2018/07/product5.jpg",
          "daily_netProfit": "-18.24",
        },
        {
          "title": "Product8",
          "permalink": "http://test.com/computer-hardware/product5/",
          "smallImg": "http://test.com/content/uploads/2018/07/product5.jpg",
          "daily_netProfit": "75.68",
        },
        {
          "title": "Product9",
          "permalink": "http://test.com/computer-hardware/product5/",
          "smallImg": "http://test.com/content/uploads/2018/07/product5.jpg",
          "daily_netProfit": "863.31",
        },
        {
          "title": "Product10",
          "permalink": "http://test.com/computer-hardware/product5/",
          "smallImg": "http://test.com/content/uploads/2018/07/product5.jpg",
          "daily_netProfit": "20.1",
        }
      ]
    };
    const rentabilityHtml = function(daily_netProfit) {
      if (daily_netProfit < 0) {
        return `<div style="color:red;"><b>$${daily_netProfit}/day</b></div>`
      } else {
        return `<div style="color:green;"><b>$${daily_netProfit}/day</b></div>`
      }
    }
    //transform data set
    let dataSet = results.profRigHardware.map((item, i) => [
      `<img src="${ item.smallImg }" alt="${ item.title }" height="42" width="42"> 
         <a href="${item.permalink}" target="_blank">
            ${item.title}
             </a>`,
      parseFloat(item.daily_netProfit),
    ])

    $('#allHardwareOverview').DataTable({
      data: dataSet,
      destroy: true,
      iDisplayLength: 25,
      responsive: true,
      "bInfo": false,
      "order": [
        [1, 'desc']
      ],
      columns: [{
          title: "Model"
        },
        {
          title: "Profitability",
          render: function(profit, type) {
            if (type == "display")
                return rentabilityHtml(parseFloat(profit))
            else
                return profit;
          }
        }
      ],
      "initComplete": function(settings, json) {
        $('#datatablediv').css('opacity', 1);
      }
    });
  }
  loadHardware();
});

<link href="https://cdn.datatables.net/1.10.18/css/jquery.dataTables.min.css" rel="stylesheet" />
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<link href="https://cdn.datatables.net/1.10.18/css/dataTables.bootstrap4.min.css" rel="stylesheet" />
<script src="https://cdn.datatables.net/1.10.18/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.18/js/dataTables.bootstrap4.min.js"></script>

<div class="tab-pane fade show active" id="all" role="tabpanel" aria-labelledby="all-tab">
  <div class="table-responsive overflow-x:auto;">
    <table id="allHardwareOverview" style="width:100%; float: left;" class="table table-bordered"></table>
  </div>
</div>