Having trouble using columns.render and orthogonal data to sort by a value not displayed

Noah Corlew

I am using Ajax to retrieve data from an SQL view and displaying the datatable in a blade template using server-side rendering.

I am attempting to sort columns using orthogonal data, (specifically minutes_since_completed in this example) however it does not appear to be working. Currently the column is sorted alphabetically by the data shown, which is a date followed by a label.

var dt = $('#Datatable').DataTable({
lengthMenu : [[10, 25, 50, 100, 500, -1], [10, 25, 50, 100, 500, "All"]],
searching : true,
processing : true,
stateSave : false,
ordering : true,
serverSide: true,
pagingType : "full_numbers",
ajax : {
    'url' : "...",
    'type' : 'GET',
},
columns: [
    ...
    {
        name : 'completed_at_pretty',
        data : {completed_at_pretty: 'completed_at_pretty', minutes_since_completed: 'minutes_since_completed'},
        orderable: true,
        render: function ( data, type, row) {
            if (type === 'display' || type === 'filter') {
                if(data.completed_at_pretty != null) {
                    return `
                        <span>${data.completed_at_pretty}</span><br>
                        <span class="label label-blue">${time_since(data.minutes_since_completed)}</span>`
                }
                else {
                    return `<div class="center">-</div>`
                }
            }
            return data.minutes_since_completed;
        }
    ...

What am I doing wrong here? Any help would be appreciated, thanks!

kthorngren

When using server side processing the sorting, searching and paging functions are performed by the server side script. Using orthogonal data is only used with client side processing. You will need to update your server script to sort as desired.

data : {completed_at_pretty: 'completed_at_pretty', minutes_since_completed: 'minutes_since_completed'},

This won't work. The columns.data docs explain the various options you can use. Try using data: 'completed_at_pretty' instead.

If you want to combine multiple data points in the row for display then see this Column rendering example.

Kevin

Noah Corlew

This won't work.

It does seem to work, I am able to display both data points. I will note that minutes_since_completed is an attribute on the laravel model that I am creating this datatable on.

When using server side processing the sorting, searching and paging functions are performed by the server side script. Using orthogonal data is only used with client side processing.

This answered my question, thanks. I was storing completed_at_pretty within my sql view as a varchar, and completed_at as a timestamp. Rather than doing that horrible thing, I can use completed_at in the datatable and prettyfy it client side.