Sorting on multiple columns

mikefields

Hi,

I've been searching through the forum and documentation to find a way to sort a table column while considering the values in another column. Thread 1835 gives some great advice using fnSort and fnSortListener and this has allowed me to make the column sort in ascending order while ensuring that the values in the next column are also sorted in ascending order. However I would also like to be able to click on the column again and sort in descending order (as it would do by default) but ensuring that the valuse in the next column are still sorted in ascending order.

(For context, the first column is the club name for racing cyclists and the second column is the result for a race. People like to sort by club, but have the resulting rows displayed in ascending time order.)

The code I've written is:

[code]

$(document).ready(function() {
var oTable = $('#tablehtml').dataTable({
"iDisplayLength": 20,
"sPaginationType": "full_numbers",
"aLengthMenu": [[20, -1], [20, "All"]],
"sDom": 'T<"clear">lfrtip',
"oTableTools": {
"aButtons": [
"print"
]
},
});
oTable.fnSortListener( document.getElementById("Club"), 2);
$("#Club").click( function () {
oTable.fnSort( [[2,"asc"], [3,"asc"]] );
});
} );

Position

Name
Club
Time


1
Dan Craven
Rapha CC
00:21:01

....

[/code]

Is there a way of doing this, so the Club column uses the Time column when sorting the Club values in both ascending and descending order?

Thanks, Mike

allan

HI Mike,

What you can do is to add an 'if' condition into your event handler which will reserve the sorting if it is already being sorted upon. Some thing like:

[code]
var aaSorting = oTable.fnSettings().aaSorting;
if ( aaSorting[0][0] != 2 || aaSorting[0][1] == 'desc' ) {
oTable.fnSort( [[2,"asc"], [3,"asc"]] );
} else {
oTable.fnSort( [[2,"desc"], [3,"desc"]] );
}
[/code]
Allan

mikefields

Hi Allan,

Thanks for the response. I've been having a bit of trouble getting your suggestion to work, should I be using it like this:

[code]
oTable.fnSortListener( document.getElementById("Club"), 2);
$("#Club").click( function () {
var aaSorting = oTable.fnSettings().aaSorting;
if( aaSorting[0][0] !=2 || aaSorting[0][1] == "desc"){
oTable.fnSort( [[2,"asc"], [3,"asc"]]);
}else{
oTable.fnSort( [[2,"desc"], [3,"desc"]]);
}
}
[/code]

This only gives me the option to sort the Club column in ascending order, as it was doing before, so I must be doing something wrong but I can't work it out.

Thanks,

Mike

allan

Drop the call to fnSortListener - that will add the DataTables default click event handlers to the element - which you don't want - you just want your own.

Allan

mikefields

Hi Allan,

I'm still trying to get this to work and have attached the click event to both the Club column and the document (code is to document) but I still have the same results. I've also added an alert to see what values are in the aaSorting array and these are toggling between asc and desc for all columns except for "Club" which stays set to asc.

[code]



@import "Release-Datatables/media/css/demo_table.css";


$(document).ready(function() {
var oTable = $('#tablehtml').dataTable({
"iDisplayLength": 20,
"sPaginationType": "full_numbers",
"aLengthMenu": [[20, -1], [20, "All"]],
"sDom": 'T<"clear">lfrtip',

});
$(document).click(function() {
var aaSorting = oTable.fnSettings().aaSorting;
alert("[0][0] = " + aaSorting[0][0] + ", [0][1] = " + aaSorting[0][1]);
if(aaSorting[0][0] ==2){
if(aaSorting[0][1] == "asc"){
oTable.fnSort( [[2,"asc"], [3,"asc"]]);
}else{
oTable.fnSort( [[2,"desc"], [3,"desc"]]);
}
}
} );
} );

Position
Name

Club
Time


1
Dan Craven
Rapha CC
00:21:01

....

[/code]

Is the reason for this behaviour because I'm capturing the click event for the "Club" column and handling it in a different way? If so, how do I toggle the sorting value between asc and desc?

Thanks,

Mike

allan

I think you are very close with that! The only thing you need to do now is remove the default sort listener that DataTables adds. At the moment the click is firing your event, and the DataTables one :-) - hence the sort happens twice and always returns to the same direction (since it is circular with two options). So something like:

[code]
$(document).ready(function() {
var oTable = $('#example').dataTable();

$('#example thead th:eq(2)').unbind('click').click( function () {
var aaSorting = oTable.fnSettings().aaSorting;
if ( aaSorting[0][0] != 2 || aaSorting[0][1] == 'desc' ) {
oTable.fnSort( [[2,"asc"], [3,"asc"]] );
} else {
oTable.fnSort( [[2,"desc"], [3,"desc"]] );
}
} );
} );
[/code]
Note the 'unbind' of the click.

Allan

mikefields

Allan,

That works great, thanks very much for your assistance.

For the benefit of others, the final code is:

[code]

$(document).ready(function() {
var oTable = $('#tablehtml').dataTable();
$('#tablehtml thead th:eq(2)').unbind('click').click(function(){
var aaSorting = oTable.fnSettings().aaSorting;
if( aaSorting[0][0] !=2 || aaSorting[0][1] == 'desc'){
oTable.fnSort( [[2,'asc'], [3,'asc']]);
}else{
oTable.fnSort( [[2,'desc'], [3,'asc']]);
}
});
} );


Position
Name
Club
Time


....

[/code]

This means that when you sort on the "Club" column, the results are always sorting in ascending result times ("Time").

Mike

joatman

[EDIT]
Sorry, problem resolved - guess i could have tried to use my head - the solution was simply to add a "if aaSorting.length == 0" before the other conditions.

ONLY one other question: in this case, it shows the data as sorted by both headers, and there's really no way to tell which one's the primary sort and which one's secondary. is there some way to distinguish between the two?
[END EDIT]


Hi guys,
In my case the aaSorting variable comes out to be null for me, because i didn't want any initial sort, and had set aaSorting = [] in my initialization. any ideas how i can still get this exact situation to work?
Thanks,
Nilesh