Default ordering when orderable set to false

mihomes

Came across a unique problem today. I have turned off the sorting ability for the user, however, I would still like to sort the column by default. Orderable set to false for the column, but I want it sorted by desc by default on display. In other words, it will be 'locked' in desc order.

I am using this for a table where I allow the user to set an automatic update interval (15 seconds, 30 seconds, 1 minute, etc) and they can also choose how many rows they want displayed (10, 25, 50, etc). The table redraws every x seconds and shows the latest x number of rows. Since this is only meant to show the latest results and limited to the row count they choose I have disabled paging.

Hopefully that explanation makes sense.

"columns": [
    {
        "data": "timestamp",
        "orderable": false
    },
        ... and so on
],
"order": [[0,'desc']],
... and so on

I am using server-side obviously for this. What happens is the sql statement breaks because it adds an empty "ORDER BY' clause.

    static function order ( $request, $columns )
    {
        $order = '';

        if ( isset($request['order']) && count($request['order']) ) {
            $orderBy = array();
            $dtColumns = self::pluck( $columns, 'dt' );

            for ( $i=0, $ien=count($request['order']) ; $i<$ien ; $i++ ) {
                // Convert the column index into the column data property
                $columnIdx = intval($request['order'][$i]['column']);
                $requestColumn = $request['columns'][$columnIdx];

                $columnIdx = array_search( $requestColumn['data'], $dtColumns );
                $column = $columns[ $columnIdx ];

                if ( $requestColumn['orderable'] == 'true' ) {
                    $dir = $request['order'][$i]['dir'] === 'asc' ?
                        'ASC' :
                        'DESC';

                    $orderBy[] = ''.$column['db'].' '.$dir;
                }
            }
            
            $order = 'ORDER BY '.implode(', ', $orderBy);
        }

        return $order;
    }

The $request['order'] is set, but it fails to add on if ( $requestColumn['orderable'] == 'true' ). The result is the empty "ORDER BY" added to the sql.

Is there anyway to rectify this or will I need to add in some custom code to handle this?

mihomes

I guess I should have been a little clearer - I realize to 'fix' this issue it is simply a matter of removing the if condition, but I don't understand why this conditional check was there in the first place. Do the plugins or something else rely on this condition?

This solution also adds a 'sorting_desc' class to my column header, but of course clicking on it will not do anything now as it is locked in desc order. Depending on how you style your table this might be a little annoying... for instance, if you have css hover actions for header which allow sorting. In my case (not sure if this is default or not) it will show the pointer cursor when hovered over the cell which leads one to believe it can be clicked and sorted even though it can't.

allan

Hi,

No - plug-ins don't depend on it as far as I am aware. The condition is there so you can control that behaviour from the client-side. If you don't want it there, just remove it :-).

Moreover, since you are using server-side processing here, you can disable sorting on the client-side and modify the server-side script to simply add the sorting parameters you need.

Allan

mihomes

I could do that, but then you lose the sorting icons for display so the user knows they are sorted in x order. So if I disabled, I would need to add custom sorting parameters, and add a custom sorting class for the icons. I might as well just leave it as is where they can click on the column to sort, but nothing happens.

allan

Oh I see. You could remove the the click event listener that DataTables adds to the header elements using $().off().

Allan