Bootstrap Datatables sort for various measurement units not working

I have a column which has length values defined in various units, for eg:- 10mm, 200cm, 500mm, etc., where mm = millimeter and cm = centimeter. I am using the server-side script to get this from my MySql Database. The best way to get them to sort from asc to desc or viceversa properly would be to convert all values to mm and then sort. I have tried various ways to achieve this, including doing a readup on Orthogonal data, but, I am not able to make a sense of it. Every time the sort is haphazard, with values mixing up and such.

My PHP script is simple:-

$columns = array(
    array( 'db' => 'length', 'dt' => 'length' )
);

As for everything I have tried on the JS level, here goes:-

Method 1. Set sort-value when type is sort

Result: Haphazard sort with no rhythm

new DataTable('#lt-table', {
    ajax: {
        url: 'lt-sorter.php',
        type: 'POST'
    },
    columns: [
        { data: 'null',
            render: function ( data, type, row ) {
                if ( type === 'sort' ) {
                    if (row.length.includes('mm')) {
                        var sortValue = row.length.replace('mm', '');
                    } else if (row.length.includes('cm')) {
                        var sortValue = row.length.replace('cm', '') * 10;
                    }
                return sortValue;
                } else {
                    return row.length;
                }
            }
        },
    processing: true,
    serverSide: true
});

Method 2. createdRow to add data-sort

Result: Data-sort is set successfully. But, still, haphazard sort with no rhythm. Also tried data-order with same result

createdRow: function (row, data, dataIndex) {
    if (data.length.includes('mm')) {
        $(row).find('td:eq(0)').attr('data-sort', data.length.replace('mm', ''));
    } else if (data.length.includes('cm')) {
        $(row).find('td:eq(0)').attr('data-sort', data.length.replace('cm', '') * 10);
    }
}

Method 3. Hidden value

Result: Hidden value completely disregarded. Virtually no sorting happens

return '<span style="display:none">' + row.length.replace('mm', '') + ' </span>' + row.length;

I am obviously colossally messing up somewhere, but, can someone point out what I am doing wrong?

Solution

I did some research and I was able to get this basic solution working with something similar to what you described in method 1.

I created a function to convert the units into mm 'convertToMM()' and then registered a custom type with data tables that utilizes the function. I have put comments throughout the code and I hope that helps explain what's going on

The snippet below shows a basic static table that has 2 columns Length and Width, which are all in different metric units mm, cm and m. Try run the snippet yourself and see the sorting in action.

If you have any questions or if anything is unclear please let me know.

const customType = "metric-unit"; // name of custom data type to use with data tables, this can be anything i just called it metric-unit

   // Register the custom data type, this tells data tables how it should sort the values.
   $.fn.dataTable.ext.type.order[customType] = function (d) {
       return convertToMM(d); // convert data to millimeters for sorting
   };

    $(document).ready(function() {
        $('#lt-table').DataTable({
            "columnDefs": [
                // For each column in the table that has the units you want to sort assign the type to them
                {
                "type": customType, // the data type to use
                "targets": 0, // 0 in this instance is Length, Adjust the column index according to your table structure
                "render":unitRender // name of function that handles render logic
                },
                {
                    "type": customType, // the data type to use
                    "targets": 1, // 1 in this instance is Width, Adjust the column index according to your table structure
                    "render": unitRender // name of function that handles render logic
                }
            ]
        });
    });

    function unitRender(data, type, row){
        if (type === 'sort') {
            return convertToMM(data); // use converted values for sorting
        }
        return data; // display the original value
    }

    function convertToMM(value) {
        var strValue = value.toString(); // ensure it's a string
        var unit = strValue.match(/[a-zA-Z]+/)[0]; // extract unit
        var number = parseFloat(strValue.match(/[0-9.]+/)[0]); // extract number

        switch(unit) {
            case 'cm':
                return number * 10; // convert cm to mm
            case 'm':
                return number * 1000; // convert m to mm
            case 'mm':
                return number;
            default:
                return number; // in case an unhandled unit sneaks in
        }
    }

<html>
<head>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous">
    <link rel="stylesheet" href="//cdn.datatables.net/2.1.8/css/dataTables.dataTables.min.css">
    <script src="https://code.jquery.com/jquery-3.7.1.min.js" integrity="sha256-/JqT3SQfawRcv/BIHPThkBvs0OEvtFFmqPF/lYI/Cxo=" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz" crossorigin="anonymous"></script>
    <script src="//cdn.datatables.net/2.1.8/js/dataTables.min.js"></script>
</head>
<body>
<table id="lt-table">
    <thead>
        <tr>
            <th>Length</th>
            <th>Width</th>
        </tr>
    </thead>
    <tbody>
    <tr>
         <td>500cm</td>
         <td>400cm</td>
     </tr>
     <tr>
            <td>600mm</td>
            <td>200mm</td>
     </tr>
     <tr>
            <td>1m</td>
            <td>3m</td>
      </tr>
       <tr>
            <td>5mm</td>
            <td>3mm</td>
      </tr> 
            
</tbody>
</table>
</body>