Serverside table data returned from Editor PHP library is now always sorted by the 1st column.

dabrolga

I have a number of simple serverside tables on a couple of sites that have for many years returned the data from the Editor PHP library unsorted,. The data is ordered to my requirements by the MariaDB view that I pull the data from. Since updating from Editor v2.3.2 to v2.5.1 all my serverside table data is now returned sorted by the first column whereas previously it was unsorted by DataTables. I have not asked for any ordering so I would expect it to be still unsorted. I have tried adding order: [] to my JS but that has no effect as no ordering is specified in the request sent for data.

The problem is being caused by this code In the Editor PHP library Editor.php (I think intoduced in v:2.4.0):

        // Paging makes little sense without an ordering clause, so if there is
        // no order to apply (possible in DT2 on the third click of a header)
        // we apply the primary key as the ordering value.
        if (!isset($http['order']) || count($http['order']) === 0) {
            $query->order($this->_pkey[0] . ' asc'); 
        }

My fix is to comment out the above code, which solves the problem, but it is a bit of an ugly fix.

An example of my JS:

    var table = $("#table_1").DataTable({
        ajax: {
            url: "/php/members.php",
            type: "POST",
        },
        serverSide: true,
        searchDelay: 800,
        columns: [
            {
                data: null,
                defaultContent: "",
                className: "control",
                searchable: false,
                responsivePriority: 10,
            },
            {
                data: "no",
                render: $.fn.dataTable.render.text(),
                responsivePriority: 10,
            },
            {
                data: "nm",
                render: $.fn.dataTable.render.text(),
                responsivePriority: 10,
            },
            {
                data: "email",
                render: $.fn.dataTable.render.text(),
            },
            {
                data: "mob",
                render: $.fn.dataTable.render.text(),
                searchable: false,
            },
            {
                data: "day",
                render: $.fn.dataTable.render.text(),
                searchable: false,
            },
            {
                data: "night",
                render: $.fn.dataTable.render.text(),
                searchable: false,
            },
            {
                data: "secemail",
                render: $.fn.dataTable.render.text(),
            },
            {
                data: "stat",
                render: $.fn.dataTable.render.text(),
                searchable: false,
                responsivePriority: 10,
            },
            {
                data: "cur",
                render: function (data, type, row) {
                    if(data == true) {
                        return "Y";
                    } else {
                        return "N";
                    };
                },
                className: "dt-center",
                searchable: false,
                responsivePriority: 10,
            },
        ],
        ordering: false,
        pagingType: "full_numbers",
        responsive: true,
    });

And the associated PHP:

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// Build our Editor instance and process the data coming from _POST
Editor::inst($db, 'members', 'ID')
    ->fields(
        Field::inst('members.memb', 'no'),
        Field::inst('members.name', 'nm'),
        Field::inst('members.pday', 'day'),
        Field::inst('members.pnight', 'night'),
        Field::inst('members.mobile', 'mob'),
        Field::inst('members.membemail', 'email'),
        Field::inst('members.membemail2', 'secemail'),
        Field::inst('members.status', 'stat'),
        Field::inst('members.isfinancial', 'cur')
    )
    ->validator(function($editor, $action, $data) {
        if ($action !== Editor::ACTION_READ) return ERRMSG['action'];  // Only allow read
    })
    ->process($_POST)
    ->json();

Is there some method to prevent orderng that I have missed?

allan

Interesting! This is the commit that added that, and as noted there, it was to stop SQL Server throwing an error.

Perhaps there needs to be a configurable option for it? I'll need to have a think about that.

Allan

dabrolga

Thanks for considering this Allan. My thoughts were using order: [] as the option (but maybe not passing an empty value) as it would make it consistent with non serverside tables.