Ordering From AJAX Data

theflarenet

Hi,

I have a table where I server-side process my data (0 to 10) and only display data elements 1 through 7. Element 0 is of course used for indexing as the primary key and 8-10 was added for additional sorting so I can order 8 (ASC), 9 (DESC), 10 (ASC) as they are not displayed. How is this possible? I tried tackling this by using "render" but I don't see an option to make it either ASC or DESC.

$('#test').DataTable({
            "processing": true,
            "paging": true,
            "pageLength": 50,
            "serverSide": true,
            "ajax": "test.php",
             columns: [
                {data: 1, render: { sort: 8 } },
                {data: 2, render: { sort: 9 } },
                {data: 3, render: { sort: 10 } },
                {"data": 4},
                {"data": 5},
                {"data": 6},
                {"data": 7}
            ]
});

This also didn't work prior to using "render" since elements 8, 9, and 10 are not displayed:
'order': [[8, 'asc'], [9, 'desc'], [10,'asc']],

Any workarounds?

kthorngren

Maybe columns.orderData is what you are needing to use.

Kevin

theflarenet

Kevin, this looks promising but I'm unable to get this to work:

            columns: [
//                {data: 1, render: { sort: 8 } },
//                {data: 2, render: { sort: 9 } },
//                {data: 3, render: { sort: 10 } },
//                { "orderData": [ 1, 8 ] },
                {"data": 1, "orderData": 8},
                {"data": 2},
                {"data": 3},
                {"data": 4},
                {"data": 5},
                {"data": 6},
                {"data": 7}
            ],

I'm not sure whether or not I've done this correctly; essentially my goal here is to do an ORDER BY [8], [9] DESC, [10] but I don't know how to set it up accordingly.

kthorngren

You need to define the columns 8-10 then hide them. The columns need to exist in your html table.

            columns: [
                {"data": 1, "orderData": 8},
                {"data": 2},
                {"data": 3},
                {"data": 4},
                {"data": 5},
                {"data": 6},
                {"data": 7},
                {"data": 8, visible: false},
                {"data": 9, visible: false},
                {"data": 10, visible: false},
            ],

I put together a small example:
http://live.datatables.net/lurujoru/1/edit

I didn't use columns in my example but the net result is the same. The example has 6 columns. Column 0 is Full Name and not sortable. Columns 1 and 2 just exists to have data. Column 3 is the firstname, 4 is lastname and 5 just exists.

You can experiment with setting column 4 and 3 to asc or desc to see how it affects the table. Also the order of order is important. I have [[ 4, 'asc' ], [ 3, 'desc' ], [ 5, 'asc' ]] which priorities the lastname first for sorting. If I reverse 3 and 4, [[ 3, 'asc' ], [ 4, 'desc' ], [ 5, 'asc' ]], the sorting won't work right.

I'm not sure if this is what you are looking for. If not please post more details with example data.

Kevin

theflarenet

Clever workaround however the whole server-side process fetching fails once it attempts: {"data": 10, visible: false},. For some reason it only fetches 0-9 and places the data into the table accordingly. I have no idea why. Even adding an ORDER that's 2 or more puts the thing to a hault. Perhaps the GET request gets too long?!

I look forward to see other solutions/workarounds posted!

kthorngren

server-side process fetching fails once it attempts: {"data": 10, visible: false},

Do you get any errors or console log messages?

What is the JSON data that's returned? You can use the debugger to collect this info.

You can use POST, example here:
https://datatables.net/examples/server_side/post.html

EDIT:
I created a server side example using the same code:
http://live.datatables.net/juleboxa/1/edit

The data returned include first_name and last_name fields. I rendered them into the first column to display first_name last_name. Seems to work the same.

Kevin

theflarenet

Surpisingly... the failed GET request is: test.php?draw=1&columns%5B0%5D%5Bdata%5D=1&columns%5B0%5D%5Bname%5D=&columns%5B0%5D%5Bsearchable%5D=true&columns%5B0%5D%5Borderable%5D=true&columns%5B0%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B0%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B1%5D%5Bdata%5D=2&columns%5B1%5D%5Bname%5D=&columns%5B1%5D%5Bsearchable%5D=true&columns%5B1%5D%5Borderable%5D=true&columns%5B1%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B1%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B2%5D%5Bdata%5D=3&columns%5B2%5D%5Bname%5D=&columns%5B2%5D%5Bsearchable%5D=true&columns%5B2%5D%5Borderable%5D=true&columns%5B2%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B2%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B3%5D%5Bdata%5D=4&columns%5B3%5D%5Bname%5D=&columns%5B3%5D%5Bsearchable%5D=true&columns%5B3%5D%5Borderable%5D=true&columns%5B3%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B3%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B4%5D%5Bdata%5D=5&columns%5B4%5D%5Bname%5D=&columns%5B4%5D%5Bsearchable%5D=true&columns%5B4%5D%5Borderable%5D=true&columns%5B4%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B4%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B5%5D%5Bdata%5D=6&columns%5B5%5D%5Bname%5D=&columns%5B5%5D%5Bsearchable%5D=true&columns%5B5%5D%5Borderable%5D=true&columns%5B5%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B5%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B6%5D%5Bdata%5D=7&columns%5B6%5D%5Bname%5D=&columns%5B6%5D%5Bsearchable%5D=true&columns%5B6%5D%5Borderable%5D=true&columns%5B6%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B6%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B7%5D%5Bdata%5D=8&columns%5B7%5D%5Bname%5D=&columns%5B7%5D%5Bsearchable%5D=true&columns%5B7%5D%5Borderable%5D=true&columns%5B7%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B7%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B8%5D%5Bdata%5D=9&columns%5B8%5D%5Bname%5D=&columns%5B8%5D%5Bsearchable%5D=true&columns%5B8%5D%5Borderable%5D=true&columns%5B8%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B8%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B9%5D%5Bdata%5D=10&columns%5B9%5D%5Bname%5D=&columns%5B9%5D%5Bsearchable%5D=true&columns%5B9%5D%5Borderable%5D=true&columns%5B9%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B9%5D%5Bsearch%5D%5Bregex%5D=false&order%5B0%5D%5Bcolumn%5D=0&order%5B0%5D%5Bdir%5D=asc&start=0&length=50&search%5Bvalue%5D=&search%5Bregex%5D=false&_=1501304935098

Other requests with data elements 0-through-9 instead of 0-through-10 work flawlessly with test.php but in this case it renders a Server Error.

Unfortunately, I cannot use the example since I'm locked in a SQL Server 2008 environment so the MySQL version and SQL Server 2012+ version are out of the question. I'm currently using this:

/*
 * ODBC connection
 */
$connectionInfo = array("UID" => $gaSql['user'], "PWD" => $gaSql['password'], "Database"=>$gaSql['db'],"ReturnDatesAsStrings"=>true);
$gaSql['link'] = sqlsrv_connect( $gaSql['server'], $connectionInfo);
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );


/* Ordering */
$sOrder = "";
if ( isset( $_GET['order'] ) )
{
    $sOrder = "ORDER BY ";
    if ( $_GET['columns'][0]['orderable'] == "true" )
    {
        $sOrder .= "".$aColumns[ intval( $_GET['order'][0]['column'] ) ]." ".
            ($_GET['order'][0]['dir']==='asc' ? 'asc' : 'desc');
    }
}


/* Filtering */
$sWhere = "";
if ( isset($_GET['search']['value']) && $_GET['search']['value'] != "" ) {
    $sWhere = "WHERE (";
    for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
        $sWhere .= $aColumns[$i]." LIKE '%".addslashes( $_GET['search']['value'] )."%' OR ";
    }
    $sWhere = substr_replace( $sWhere, "", -3 );
    $sWhere .= ')';
}


/* Individual column filtering */
for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
    if ( isset($_GET['columns'][$i]) && $_GET['columns'][$i]['searchable'] == "true" && $_GET['columns'][$i]['search']['value'] != '' ) {
        if ( $sWhere == "" ) {
            $sWhere = "WHERE ";
        } else {
            $sWhere .= " AND ";
        }
        $sWhere .= $aColumns[$i]." LIKE '%".addslashes($_GET['columns'][$i]['search']['value'])."%' ";
    }
}

/* Paging */
$top = (isset($_GET['start']))?((int)$_GET['start']):0 ;
$limit = (isset($_GET['length']))?((int)$_GET['length'] ):10;
$sQuery = "SELECT TOP $limit ".implode(",",$aColumns)."
        FROM $sTable
        $sWhere ".(($sWhere=="")?" WHERE ":" AND ")." $sIndexColumn NOT IN
        (
            SELECT $sIndexColumn FROM
            (
                SELECT TOP $top ".implode(",",$aColumns)."
                FROM $sTable
                $sWhere
                $sOrder
            )
            as [virtTable]
        )
        $sOrder";

$rResult = sqlsrv_query($gaSql['link'],$sQuery) or die("$sQuery: " . sqlsrv_errors());

$sQueryCnt = "SELECT * FROM $sTable $sWhere";
$rResultCnt = sqlsrv_query( $gaSql['link'], $sQueryCnt ,$params, $options) or die (" $sQueryCnt: " . sqlsrv_errors());
$iFilteredTotal = sqlsrv_num_rows( $rResultCnt );

$sQuery = " SELECT * FROM $sTable ";
$rResultTotal = sqlsrv_query( $gaSql['link'], $sQuery ,$params, $options) or die(sqlsrv_errors());
$iTotal = sqlsrv_num_rows( $rResultTotal );

$output = array(
    "draw" => intval($_GET['draw']),
    "recordsTotal" => $iTotal,
    "recordsFiltered" => $iFilteredTotal,
    "data" => array()
);

while ( $aRow = sqlsrv_fetch_array( $rResult ) ) {
    $row = array();
    for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
        if ( $aColumns[$i] != ' ' ) {
            $v = $aRow[ $aColumns[$i] ];
            $v = mb_check_encoding($v, 'UTF-8') ? $v : utf8_encode($v);
            $row[]=$v;
        }
    }
    If (!empty($row)) { $output['data'][] = $row; }
}

echo json_encode( $output );

kthorngren

Other requests with data elements 0-through-9 instead of 0-through-10 work flawlessly

The above has 10 columns and is requesting 10 columns. "0-through-10" would be 11 columns. "elements 0-through-9" do you have another Datatable requesting this?

renders a Server Error

What is the error?

Its not clear what you are trying to accomplish. Maybe if you describe the data you have and how you want it displayed we can help.

Kevin

theflarenet

That is correct. My current test is requesting 10 columns (0-9) and it's working flawlessly unlike 10+ columns (0-11) where it fails.

I've attached a screenshot of the error which baffles me because if I remove the 11th data request, it works fine. It appears that more than 10 elements in the request renders this error.

The data is quite simple... the first 10 columns are text and the additional 3 (hidden) ones are primary keys of 3 of the 10 text columns. My goal is to sort those three with their primary keys instead of text: ORDER BY [8], [9] DESC, [10]

I wish there was a way to add a explicit ORDER BY in the query through SSP implementation (where you configure the SQL connection, table, columns, etc). I do not want to go through compiling a View and plugging it in for the table each time.

tangerine

<I wish there was a way to add a explicit ORDER BY in the query through SSP implementation

There are numerous posts in here concerning revised implementations of the basic ssp class.

theflarenet

Good to know. What's troubling is, I don't think those implementations will work hand-in-hand with the SQL Server 2008 revised version I'm utilizing. I'm having trouble trying to rewrite/translate with my limited knowledge between MySQL <-> SQLSRV2008 syntax differences.