sorting columns with a LEFT JOIN

lukemclachlan

Link to test case: unfortunately it's on the localserver.

Debugger code (debug.datatables.net): iqumis
Description of problem:

I have two LEFT JOIN 's with a total therefore of three mysql tables. The problem is sorting, in that order[0][column] does not relate to the column order in my database table. For example, the first datatables column is the third column in the 'measure' database table, whilst the second datatables column is the second column in the 'location' database table.

I have been searching for a solution and I can't get my head around this.

What I did try is to give the th's an id that matches the database column name, and pass this id to the function that lists the records:

$('#maintenanceListing th').click(function(){
        sorted = this.id;
                console.log(sorted);
       });
var dataRecordsFour = $('#maintenanceListing').DataTable({
        "lengthChange": false,
        "processing":true,
        "serverSide":true,
        'processing': true,
        'serverSide': true,
        'serverMethod': 'post',     
        "order":[],
        "ajax":{
            url:"ajax-action-maintenance.php",
            type:"POST",
            data:{action:'listRecords',brf_id:valued,identifier:'category',sorted:sorted},
            dataType:"json"
        },
        "columnDefs":[
            {
                "targets":[5,6],
                "orderable":false,
            },
        ],
        "paging": false,
        "oLanguage": {
            "sSearch": "Sök åtgärd: "
            }
    });

However the variable 'sorted' isn't being passed with the click function to ajax, despite it showing up in the console.

Can anyone guide me here?

/ Luke

lukemclachlan

for now I have changed the following line in my code:

if(!empty($_POST["order"])){
        $sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
} else {
    $sqlQuery .= 'ORDER BY measure_description ASC ';
}

to:

if(!empty($_POST["order"])){
            // the number 3 is the order of the name column in the database
            if ($_POST['order']['0']['column'] === '0') {
                $_POST['order']['0']['column'] = 'measure_description';
            } elseif($_POST['order']['0']['column'] === '1') {
                $_POST['order']['0']['column'] = 'last_completed';
            } elseif($_POST['order']['0']['column'] === '2') {
                $_POST['order']['0']['column'] = 'theinterval';
            } elseif($_POST['order']['0']['column'] === '3') {
                $_POST['order']['0']['column'] = 'category_name';
            } elseif($_POST['order']['0']['column'] === '4') {
                $_POST['order']['0']['column'] = 'location_name';
            } elseif($_POST['order']['0']['column'] === '5') {
                $_POST['order']['0']['column'] = '';
            }
            $sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
        } else {
            $sqlQuery .= 'ORDER BY measure_description ASC ';
        }

works like charm.

allan

Hi,

Are you using your own code for the server-side processing script? Our libraries should handle this sort of thing automatically.

Allan

rf1234

Since you are passing an empty array to the client side "order" method which means "no client side ordering" and seem to not use Editor, this really isn't a Data Tables problem ...

Looks like you have an issue generating the right SQL statement for your business problem. I would recommend you ask this on Stack Overflow.

But of course you could also not do any server side ordering and do it client side:
https://datatables.net/reference/option/order