Column sort for various measurement units (cm, mm) not working

noob404

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?

allan

You are using server-side processing, so the sorting is 100% done server-side. The data-sort and client-side rendering function will have zero effect.

I guess the first question is - do you need server-side processing? Do you have tens of thousands of rows? If not, remove the serverSide option and just have the serve dump the full JSON for the table's rows back to the client.

If you do need it, you'll need to modify the server-side script to apply the sorting you want to that SQL column. Ideally you'd sort the data as a number (int, float, dec, whatever) in a common unit. Then sorting would just happen automatically and you can display the converted unit to the end user.

Allan

noob404

Thanks a lot for the input. I will look into it further and let you know how it goes.

noob404

Update - Tried removing serverSide and tried adding data-sort and data-order both using createdRow. But, I still see the same effect. When sorting ascending, for example, I have 2mm above 5cm and then a few lines below 5cm, I have 3mm, for example.

noob404

Tried almost everything else Allan mentioned, but, setting a hidden column as mentioned here - https://datatables.net/forums/discussion/25782/how-to-sort-using-a-hidden-column-in-1-10-0 is the only thing that worked for me, but, as Allan mentioned, I had to turn off serverSide for it to work.

Thank you for this wonderful project.

allan

If you can show me a example of your JSON data I'll be able to show you how to do it without a hidden column.

Allan

noob404

Hey Allan, thanks a lot for helping out. I'd really love to figure out a way to achieve this without a hidden column.

Here's the sample JSON captured from Firefox using the createdRow method (Method 2 in original post):-

draw    0
recordsTotal    15
recordsFiltered 15
data    [ {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, … ]
0   Object { length: "29mm" }
1   Object { length: "30mm" }
2   Object { length: "30cm" }
3   Object { length: "29mm" }
4   Object { length: "45mm" }
5   Object { length: "50cm" }
6   Object { length: "25mm" }
7   Object { length: "5mm" }
8   Object { length: "7cm" }
9   Object { length: "9mm" }
10  Object { length: "10cm" }
11  Object { length: "21cm" }
12  Object { length: "22mm" }
13  Object { length: "29mm" }
14  Object { length: "31cm" }

Raw:

{"draw":0,"recordsTotal":15,"recordsFiltered":15,"data":[{"length":"29mm"},{"length":"30mm"},{"length":"30cm"},{"length":"29mm"},{"length":"45mm"},{"length":"50cm"},{"length":"25mm"},{"length":"5mm"},{"length":"7cm"},{"length":"9mm"},{"length":"10cm"},{"length":"21cm"},{"length":"22mm"},{"length":"29mm"},{"length":"31cm"}]}

Please let me know if this isn't what you were looking for.

Also, another thing that I noticed - Sort only considers the first digit in the column. So, 20cm < 30mm when sorted by it. Tried setting type to num. But, that made no difference.

kthorngren

You will need to normalize the data to the same unit of measure, ie, convert cm to mm by multiplying by 10. I would use columns.render to set the orthogonal data for the sort and type operations to the numeric value converted to MM. Here is a running example with your sample data:
https://live.datatables.net/zifuvine/1/edit

Kevin

noob404

Hey Kevin,
This is exactly what I am trying to achieve. I will go through it and let you know if I face any issues.

Thanks a bunch for the live example.

noob404

Update - Kevin's solution works perfectly for me!

Thanks to both Kevin and Allen!

allan

Might be a useful new data type for a plugin - "dimensions" or something like that. Added to my list

Allan