Sort entire DataTables on selected text in columns containing select elements when pagination is use

nicsoft

I am using DataTables for my tables. I am having a few columns with select elements. What I want is to sort those columns based on the text that is selected. In all examples I can find, the sorting is based on the value, not the text. This is not working for me and I don't know how to debug this.

This is the example which is the standard way of doing it (with the exception that I convert the column to visible) and sorts on the value.



[code]$.fn.dataTableExt.afnSortData['dom-select'] = function ( oSettings, iColumn, iColumnVis)
{
iColumn = oSettings.oApi._fnColumnIndexToVisible( oSettings, iColumn );

var aData = [];


$( 'td:eq('+iColumn+') select', oSettings.oApi._fnGetTrNodes(oSettings) ).each( function () {
aData.push( $(this).val() );

} );
return aData;
};[/code]


What I have tried to do is extract the text and push it to aData. Some of the options that has been selected is just empty.

Instead of "aData.push( $(this).val() );" I do this:

[code] if($.trim($("#" + $(this).attr('id') + " option:selected").text())!=""){
aData.push($("#" + $(this).attr('id') + " option:selected").text());
}else{
aData.push("");
}[/code]

I sorts but still not in alphabetic order. And it only sorts the visible rows, not the rows I have to paginate to.

However, it seems to work fine when I chose to view enough rows so all existing rows can be presented on one single page (hence, no pagination needed to view some other rows).

How can I achieve sorting on the entire table using the select elements' selected text when pagination is used?

UPDATE:

Could there be some incompatible issues? I'm using DT 1.9.3 but no the latest for all plugins. I would really not like to update those, I tried some which broke part of my page. This problem is the last for this release so starting with upgrading is not really what I want in this phase (I already know I will suffer from changes in the TableTools update since there hans been some changes in how the html is rendered).

Actually, for the first column with selects ([1]), I'm wrapping the select in a form which is needed in order to use malsup's resetForm() method. But since the sorting behaviour is no different between this column and columns with only the select element that are not being wrapped in the form, I guess it shouldn't matter.

Here is my initialization of the table:

[code]
$('#mytickettable').dataTable( {
"bJQueryUI": true,
"oLanguage": {
"sUrl": "https://" + hostname + "/modules/core/localization/locale/" + lang.user_locale + "/LC_MESSAGES/datatables.tr",
"sSearch": "Search all columns:"
},
"iDisplayLength": 25,
"sPaginationType": "full_numbers",
"sDom": 'RHF<"top"TClpf>rt<"bottom"ip><"clear">',
"oTableTools": {
"aButtons": [{
"sExtends": "text",
"sButtonText": lang.localized_text.SHOW_HIDE_TICKET_OVERVIEW,
"fnClick": ticketoverview_handler
},
{ "sExtends": "text",
"sButtonText": lang.localized_text.SHOW_HIDE_QUICK_FILTER,
"fnClick": function ( nButton, oConfig, oFlash ) {
$("#agentfilterform").toggle();
$("#show_closed_tickets").toggle();
if(page=='myticketsmenu'){
$("#agentfilter_asigned_ownerform").toggle();
}
}
},
{
"sExtends": "text",
"sButtonText": lang.localized_text.REFRESH_TABLE,
"fnClick": function ( nButton, oConfig, oFlash ) {
oTable.fnReloadAjax();
}
},
{
"sExtends": "xls",
"sButtonText": "Excel",
"sAction": "flash_save",
"sFileName": "Ticketlist.xls",
"mColumns": "visible"
},
{
"sExtends": "csv",
"sButtonText": "CSV",
"sFileName": "Ticketlist.csv",
"sAction": "flash_save",
"mColumns": "visible"
// "bShowAll": false
}]
},
"oColVis": {
"aiExclude": [12],
"bRestore": true,
"buttonText": lang.localized_text.SHOW_HIDE_COLUMNS,
"iOverlayFade": 0
},
"aaSorting": [[0, 'desc']],
"aoColumnDefs":
[{"asSorting":["desc","asc","desc"],"aTargets":[0]},
{"sWidth":"50px","aTargets":[0]},
{"sWidth":"65px","aTargets":[3]},
{"sWidth":"175px","aTargets":[4]},
{"sWidth":"110px","aTargets":[5]},
{"sWidth":"110px","aTargets":[6]},
{"sWidth":"125px","aTargets":[7]},
{"sWidth":"110px","aTargets":[8]},
{"sSortDataType":"dom-select","aTargets":[3]},
{"sSortDataType":"dom-select","aTargets":[5]},
{"sSortDataType":"dom-select","aTargets":[6]},
{"bVisible":false,"aTargets":[9]},
{"bVisible":false,"aTargets":[10]},
{"bSearchable":true,"aTargets":[11]},
{"bSearchable":true,"aTargets":[12]},
{"bVisible":false,"aTargets":[12]},
{"sSortDataType":"dom-select","aTargets":[1]},
{"bVisible":false,"aTargets":[1]},
{"bSearchable":true,"aTargets":[1]},
{"sWidth":"65px","aTargets":[1]},
{"sSortDataType":"dom-select","aTargets":[2]},
{"sWidth":"75px","aTargets":[2]}]
,
"bScrollCollapse": true,
"bStateSave": false,
"bProcessing": true,
"sAjaxSource": "https://" + hostname + "/modules/core/ticket/get_ticket_list.php",
"fnServerParams": function (aoData) {
aoData.push({"name":"show_closed", "value":show_closed});
aoData.push({"name":"page", "value":page});
},
"fnInitComplete": tablesetuphandler,
"fnPreDrawCallback": function(oSettings){
var $tableRow = $('#mytickettable tbody tr:eq(' + markedRow + ')');
$($tableRow).removeClass('row_selected');
},
"fnDrawCallback": function(oSettings){
$(".ColVis_Button").button(); //fixbutton
if (tableInitiated) {
markedRow = markNewRow(0,0 );
var table = document.getElementById("mytickettable");
var row = table.rows[markedRow+1];
var cell = row.cells[id_index];
var $id = cell.firstChild.nodeValue;
if(oTable.fnSettings().fnRecordsDisplay()==0){
}else if(old_id!=$id && oTable.fnSettings().fnRecordsDisplay()>0){
load_ticket_overview($id);
}
}

}

});

[/code]

nicsoft

Since I was having some problems I thought it could be that I had some text of the selected values empty. To mitigate this I checked if the text was empty and than just did aData.push("") if that was the case.

The way I checked if the value was empty was like this:

if($.trim($("#" + $(this).attr('id') + " option:selected").text())!=""){}

This was also the way I was pushing the data to aData (except trim, but I didn't matter since printing it out with trimming still didn't recognize all texts (see below).

aData.push($("#" + $(this).attr('id') + " option:selected").text())


Now this strange thing which I don't understand; the only time that the trimmed text wasn't empty was when the word "Problem" was the selected text. When it was "Incident" or anything else it was considered empty. Please if anyone can have any theory, why is that? (may not be a DT-question actually, but still interesting to know why...)

So I should use aData.push( $(":selected", this).text() ); instead. But first when I tried it I didn't use it when checking if it was empty but only for pushing the data. Now I only use aData.push( $.trim($(":selected", this).text())); to add the data, I don't check if it is empty or not since it works anyways.