Search code examples
javascriptjquerydatatables

sort two columns depending on the most recent data entered

I have a problem I have a table that I want to sort by date so that the most recent data is first and another column that is numeric that I also want to sort so that the fields that are empty go below those that are not and if I did it with "order" but when I do that it breaks the descending order of the date is there a way to sort without breaking the order of dates.
thank you for your help

$(document).ready(function(){
    var table = $('#servicios').DataTable({
      processing: true,
      serverSider: true,
       "order": [[ 2, 'desc' ],[ 3, 'desc' ]],
      "pageLength": 50,
      "lengthMenu": [[5,10,50,-1], [5,10,50,"All"]]
    });
 });
<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width">
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
  <link rel="stylesheet" href="https://cdn.datatables.net/1.12.1/css/jquery.dataTables.min.css">
  <title>JS Bin</title>
</head>
<body>
  <span><strong>STATE</strong></span>
  <select data-column="1" class="form-control filter-select">
        <option value="">All</option>
        <option value="&#10003;">&#10003;</option>
        <option value="&#9866;">&#9866;</option>
   </select>
<table id="servicios" class="display" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>State</th>
                <th>Date</th>
                <th>salary</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Tiger Nixon</td>
                <td>&#10003;</td>
                <td>2022-09-06</td>
                <td>$ 14.00</td>
            </tr>
            <tr>
                <td>Garrett Winters</td>
                <td>&#10003;</td>
                <td>2022-09-06</td>
                <td></td>
            </tr>
            <tr>
                <td>Ashton Cox</td>
                <td>&#9866;</td>
                <td>2022-09-05</td>
                <td>S/ 124.00</td>
            </tr>
            <tr>
                <td>Cedric Kelly</td>
                <td>&#9866;</td>
                <td>2022-09-06</td>
                <td></td>
            </tr>
            <tr>
                <td>Airi Satou</td>
                <td>&#10003;</td>
                <td>2022-09-06</td>
                <td></td>
            </tr>
            <tr>
                <td>Brielle Williamson</td>
                <td>&#9866;</td>
                <td>2022-09-03</td>
                <td>$ 14.00</td>
            </tr>
            <tr>
                <td>Herrod Chandler</td>
                <td>&#9866;</td>
                <td>2022-09-02</td>
                <td>S/ 124.00</td>
            </tr>
            <tr>
                <td>Rhona Davidson</td>
                <td>&#9866;</td>
                <td>2022-09-01</td>
                <td>$ 14.00</td>
            </tr>
            <tr>
                <td>Colleen Hurst</td>
                <td>&#9866;</td>
                <td>2022-09-01</td>
                <td></td>
            </tr>
            <tr>
                <td>Sonya Frost</td>
                <td>&#9866;</td>
                <td>2022-09-01</td>
                <td></td>
            </tr>
        </tbody> 
    </table>
  <script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/plug-ins/1.11.3/api/sum().js"></script>
</body>
</html>

as you can see, if I order it by amounts, the order of the dates is broken. enter image description here

Solution

  • I may not have understood the question fully, but thank you for the update - here is my approach:

    1. Add a new column to the table. This will be invisible to the users. It will contain a 1 if there is a value in the salary column. Otherwise it will contain a 0.

    2. Add a new orderData option which says that whenever a user sorts on the Date column, we will actually sort on the new column in (1) above, and then on the date column.

    Here is how (1) and (2) are implemented:

    columnDefs: [ 
  { 
    targets: [ 4 ],
    visible: false,  
    render: function ( data, type, row, meta ) {
      return row[3] ? 1 : 0;
    }
  },
  {
    targets: [ 2 ],
    orderData: [ 4, 2 ]
  }
]

    Here is a runnable demo:

    $(document).ready(function(){
    var table = $('#servicios').DataTable({
      processing: true,
       "order": [[ 2, 'desc' ],[ 3, 'desc' ]],
      "pageLength": 50,
      "lengthMenu": [[5,10,50,-1], [5,10,50,"All"]],
  
      columnDefs: [ 
        { 
          targets: [ 4 ],
          visible: false,  
          render: function ( data, type, row, meta ) {
            return row[3] ? 1 : 0;
          }
        },
        {
          targets: [ 2 ],
          orderData: [ 4, 2 ]
        }
      ]
      
    });
 });
    <!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width">
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
  <link rel="stylesheet" href="https://cdn.datatables.net/1.12.1/css/jquery.dataTables.min.css">
  <script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/plug-ins/1.11.3/api/sum().js"></script>
  <title>JS Bin</title>
</head>
<body>
  <span><strong>STATE</strong></span>
  <select data-column="1" class="form-control filter-select">
        <option value="">All</option>
        <option value="&#10003;">&#10003;</option>
        <option value="&#9866;">&#9866;</option>
   </select>
<table id="servicios" class="display" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>State</th>
                <th>Date</th>
                <th>salary</th>
                <th></th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Tiger Nixon</td>
                <td>&#10003;</td>
                <td>2022-09-06</td>
                <td>$14.00</td>
                <td></td>
            </tr>
            <tr>
                <td>Garrett Winters</td>
                <td>&#10003;</td>
                <td>2022-09-06</td>
                <td></td>
                <td></td>
            </tr>
            <tr>
                <td>Ashton Cox</td>
                <td>&#9866;</td>
                <td>2022-09-05</td>
                <td>$124.00</td>
                <td></td>
            </tr>
            <tr>
                <td>Cedric Kelly</td>
                <td>&#9866;</td>
                <td>2022-09-06</td>
                <td></td>
                <td></td>
            </tr>
            <tr>
                <td>Airi Satou</td>
                <td>&#10003;</td>
                <td>2022-09-06</td>
                <td></td>
                <td></td>
            </tr>
            <tr>
                <td>Brielle Williamson</td>
                <td>&#9866;</td>
                <td>2022-09-03</td>
                <td>$14.00</td>
                <td></td>
            </tr>
            <tr>
                <td>Herrod Chandler</td>
                <td>&#9866;</td>
                <td>2022-09-02</td>
                <td>S124.00</td>
                <td></td>
            </tr>
            <tr>
                <td>Rhona Davidson</td>
                <td>&#9866;</td>
                <td>2022-09-02</td>
                <td>$14.00</td>
                <td></td>
            </tr>
            <tr>
                <td>Colleen Hurst</td>
                <td>&#9866;</td>
                <td>2022-09-01</td>
                <td></td>
                <td></td>
            </tr>
            <tr>
                <td>Sonya Frost</td>
                <td>&#9866;</td>
                <td>2022-09-01</td>
                <td></td>
                <td></td>
            </tr>
        </tbody> 
    </table>

</body>
</html>