Column name instead of index for sorting/filtering.
Column name instead of index for sorting/filtering.
It is very easy to broke program behavior, passing only column indexes to server for sorting or filtering. For example, we have a grid with several columns, which are sortable. Suppose we sort by first column, so datatables passes (0, asc) to server, where we need to translate zero to real column name. Then we decided to reorder columns in our grid, so first column has different meaning now, but datatables still passes zero as index to server - as result we have invalid sorting behavior.
There is iDataSort parameter which we can use, but it is limited by the number of grid columns. So for example if we have db table with columns [A, B, C, D] but UI displays only B and D, we still have to write some additional code which will convert index 0 to column B and index 1 to column D.
So, would it be better to have datatable's column parameter sName or something, which will be passed as "get" parameter in url instead of index? This way it will be very easy do build server query.
I understand that this may cause some security risk, but in any case (with indexes or names in URL) good server side has to do security checks for incoming data.
Sorry if this is something completely ridiculous :)
There is iDataSort parameter which we can use, but it is limited by the number of grid columns. So for example if we have db table with columns [A, B, C, D] but UI displays only B and D, we still have to write some additional code which will convert index 0 to column B and index 1 to column D.
So, would it be better to have datatable's column parameter sName or something, which will be passed as "get" parameter in url instead of index? This way it will be very easy do build server query.
I understand that this may cause some security risk, but in any case (with indexes or names in URL) good server side has to do security checks for incoming data.
Sorry if this is something completely ridiculous :)
This discussion has been closed.
Replies
Allan
Yes indeed it did :-). If you look for the variable "sColumns" in the information that DataTables sends to the server for each draw, you'll find a string of the column names concatenated together, and comma separated. You can see this in action here: http://datatables.net/1.5-beta/examples/server_side/column_ordering.html .
It also provides a method for the server to send information back to DataTables which is "out of order" and DataTables will re-arrange it as required. This is don't using an 'sColumns' variable from server to client. For example in the above example I have:
"sColumns": "platform,engine,browser,grade,version"
Hope this helps,
Allan
[code]
"fnServerData": function ( sSource, aoData, fnCallback ) {
aoData.push( { "sColumns" : "platform,engine,browser,grade,version" } );
$.ajax( {
"dataType": 'json',
"type": "POST",
"url": sSource,
"data": aoData,
"success": fnCallback
} );
}
[/code]
Your approach is clearly the way to go, but mine might be handy for someone stuck on an older release.
cheers,
Brent
That's great - thanks very much for sharing your code - always useful to know how do to something like this. Good to hear that 1.5.1 is working well for you :-)
Regards,
Allan
WHEN SEARCHING / FILTERING
=================================================
I was very disappointed with this: Every time I want to change the order of the columns in the view, I also had to change the controller/model because datatables only searches by the numeric column index.
I wanted to decouple the view from the controller. So by this approach, I can change the column orders at will, and the request for sorting/filtering will still work.
First, I capture the column names from the aoColumns object, then I serialize that array and pass it along as an additional parameter to the request. I have the controller read this array, and determine the column name based on the column index.
[code]
The View:
=============
"sAjaxSource": '/someController/getMyData',
"aoColumns":
[
{"mDataProp": "FIRST_NAME", "sTitle": "First Name"},
{"mDataProp": "MIDDLE_NAME", "sTitle": "Middle Name"},
{"mDataProp": "LAST_NAME", "sTitle": "Last Name"},
{"mDataProp": "PHONE_NUM", "sTitle": "Phone"}
],
"fnServerData": function (sSource, aoData, fnCallback)
{
var arrCols = new Array();
var objCols = this.fnSettings().aoColumns;
for(var i=0; i 0)
{
// A request to sort by at least one column was made,
// Test to see if we allow sorting by these column(s)
/////////////////////////////////////////////////////
$arrSort = array();
for($i=0; $i<$intSortCols; $i++)
{
// Keep testing till we get this many (intSortCols) Usually, only one column
$intSortCol = intval($_POST['iSortCol_'.$i]);
if($_POST['bSortable_'.$intSortCol] === "true")
{
// This column was selected for sort, and is in list of sortable columns,
// add it to sort array
/////////////////////////////
if(isset($_POST['sSortDir_'.$i]))
{$txtSortDir = strtoupper($_POST['sSortDir_'.$i]);}
else
{$txtSortDir = "ASC";}
$arrSort[] = array("num"=>$intSortCol, "dir"=>$txtSortDir, "type"=>"TXT");
}
//endIf
}
//endFor
// Now we have an array of requested sorts that are also authorized by the
// boolean bSortable property. Next we map the sort index against the column
// names to get the name of the column for each element in the sort array
///////////////////////////////////////////////
if(count($arrSort) > 0)
{
// Get the list of all columns from the input
$txtCols = $_POST['sColNames'];
$arrCols = explode("|", $txtCols);
for($i = count($arrSort)-1; $i>=0; $i--)
{
// Iterate $arrSort in reverse, so that the most important sort
// is applied last.
////////////////////////////////////
// Map the sort index against the array of column names
$this->SORT_KEY = $arrCols[$arrSort[$i]['num']];
$this->SORT_DIR = $arrSort[$i]['dir'];
$this->SORT_TYPE = $arrSort[$i]['type'];
// Finally, sort the data
usort($arrData, array($this, "cmp"));
}
//endFor
}
//endIf
}
//endIf
// Continue with filtering and/or pagination, then ...
// Assemble your result array, then ....
// Construct JSON for return value, then ...
echo $txtJSON;
}
//endFunction getMyData
/**
* Performs a comparison of two arrays by a value of one element.
*
* @param $a First element to be compared
* @param $b Second element to be compared
* @return $intResult integer value indicating if the first or second element is higher in sort order
* 0 = elements are equal, 1 = first element is higher, -1 = second element is higher
* @author JJB
* @revision 2010-06-22
*/
function cmp($a, $b)
{
$key1 = $a[$this->SORT_KEY];
$key2 = $b[$this->SORT_KEY];
if($this->SORT_TYPE == "NUM")
{$intResult = intval($key1) - intval($key2);}
else
{$intResult = strcmp(strval($key1), strval($key2));}
//endIf
if($this->SORT_DIR == "DESC")
{return -$intResult;}
else
{return $intResult;}
//endIf
}
//endFunction cmp
[/code]