Server-side sorting broken after column reordering
Server-side sorting broken after column reordering
Ironwil616
Posts: 50Questions: 0Answers: 0
I was having a mountain of issues with DataTables after any hiding/reodering would occur. Once I moved a column, the drop-down lists or text input column filters wouldn't work anymore, because they were still mapped to the original column index. I tried a number of painful fixes for this, but wasn't able to find one that took care of all possibilities. DataTables works wonderfully, but suffers when trying to use multiple plugins at once. If I change column visibility or reorder columns, all the indexes get messed up and are never updated. Also, since DataTables does not support changing options after initialization (at least that's what I read on the forums), this becomes a rather sticky problem.
The issues are:
1. Can't change settings once DataTables is initialized. Changes to order and visibility break sorting and filtering by columns.
2. Can't reference columns by string values instead of indexes. I read that this is actually supposedly possible, but since I've yet to see a single example, I'm assuming that it's not.
I was processing over 7000 records, so it wasn't feasible to dump it all and leave everything to client-side code. After implementing a server-side solution, most of my problems went away. Instead of using plugins to set up and wire up filtering for individual columns, I grabbed my drop-down list filter name/value pairs in the 'fnServerData' function, and pushed them into aoData. Problem solved. Now I don't care what the index of the column is, since I have the name of the column and its value. However, a problem that didn't exist when I was rolling a client-side solution reared its ugly head in server-side. Now, if I sort a column, I'm getting the same index issue as I had with the column filters. In my application, the columns for indexes 2 and 3 are Name and Description, respectively. If I sort prior to reordering, everything works fine. After moving Name to the spot directly following Description, however, attempting to sort by Name will instead sort by Description. I need a way to remove the issue of column indexes from the equation entirely.
What I'd really like to do is assign ID values to all column headers, and be able to reference those instead of indexes when sorting or filtering occurs. Trying to write up fixes for inaccurate indexing is cumbersome and brittle.
The issues are:
1. Can't change settings once DataTables is initialized. Changes to order and visibility break sorting and filtering by columns.
2. Can't reference columns by string values instead of indexes. I read that this is actually supposedly possible, but since I've yet to see a single example, I'm assuming that it's not.
I was processing over 7000 records, so it wasn't feasible to dump it all and leave everything to client-side code. After implementing a server-side solution, most of my problems went away. Instead of using plugins to set up and wire up filtering for individual columns, I grabbed my drop-down list filter name/value pairs in the 'fnServerData' function, and pushed them into aoData. Problem solved. Now I don't care what the index of the column is, since I have the name of the column and its value. However, a problem that didn't exist when I was rolling a client-side solution reared its ugly head in server-side. Now, if I sort a column, I'm getting the same index issue as I had with the column filters. In my application, the columns for indexes 2 and 3 are Name and Description, respectively. If I sort prior to reordering, everything works fine. After moving Name to the spot directly following Description, however, attempting to sort by Name will instead sort by Description. I need a way to remove the issue of column indexes from the equation entirely.
What I'd really like to do is assign ID values to all column headers, and be able to reference those instead of indexes when sorting or filtering occurs. Trying to write up fixes for inaccurate indexing is cumbersome and brittle.
This discussion has been closed.
Replies
If you are using server-side processing, are you setting sName for each column? There is an example of how to do ColVis with server-side processing here: http://datatables.net/release-datatables/extras/ColReorder/server_side.html (note that it does require the server-side script to take into account that the column indexes are variable).
> 2. Can't reference columns by string values instead of indexes. I read that this is actually supposedly possible, but since I've yet to see a single example, I'm assuming that it's not.
I think a plug-in API method is what is needed here - it would take a string (the sName for the column) and convert that into a column index on-the-fly, which would then be passed to fnFilter (or whatever API method is needed). There isn't currently an API method for this action, but it would just be a case of looping over oSettings.aoColumns and looking for a matching sName for each column.
An alternative, which would use the ID method you use, would again just get the index of the TH cell (the jQuery method $().index() will be helpful) and to take account of hidden columns you could use the plug-in API method fnVisibleToColumnIndex: http://datatables.net/plug-ins/api#fnVisibleToColumnIndex .
Regards,
Allan
[code]
// This is ASP.NET MVC using C#. The 'Model.HtmlID' is sent from the
// server, which allows me to utilize save state for several project
// statuses on the same page.
var oTable = $('#<%: Model.HtmlID %>').dataTable({
"bStateSave": true,
"bProcessing": true,
"bServerSide": true,
"bAutoWidth": false,
"sDom": 'RC<"clear">lfrtip',
"sPaginationType": "full_numbers",
"sScrollX": "100%",
"sAjaxSource": "/AllProjects/AllProjectsJS",
"fnServerData": function (sSource, aoData, fnCallback) {
// Add some data to send to the source, and send as 'POST'
var status = $("#SelectStatus").val();
var myProjectsOnly =
$("#MyProjectsCheckbox").is(":checked");
// Drop-down list column filters. To avoid trouble with
// column indexes, I populate the drop-down lists and
// grab their values by ID.
var typeID = $("#Type").val();
var classificationID = $("#Classification").val();
aoData.push( { "name": "status", "value": status },
{ "name": "myProjectsOnly", "value": myProjectsOnly },
{ "name": "typeID", "value": typeID },
{ "name": "classificationID", "value": classificationID } );
$.ajax({
"dataType": 'json',
"type": "POST",
"url": sSource,
"data": aoData,
"success": fnCallback
});
},
"oLanguage": {
"sSearch": "Filter:"
},
"aoColumnDefs": [
{ "bSortable": false, "aTargets": [0, 3, 26] },
{ "sClass": "centered", "aTargets": ["centered"] }
]
});
oTable.fnSetFilteringDelay(1000);
$('.dropdownfilter', this).change( function () {
var th = $(this).parent();
var tr = $(this).parents("tr");
var index = tr.children().index(th);
oTable.fnFilter( $(this).val(), index );
});
[/code]
The bit of code that gets the index for the drop-down list column filters isn't really necessary. It was from when I was working on an inaccurate index fix for when only some of the columns had filters. However, assigning them to oTable.fnFilter makes them fire off the POST, and I grab their values via some jQuery. Adding them as name/value pairs to aoData removes any problems with inaccurate column indexes caused from hiding or moving columns around. In the fnServerData function where I do this, I would also like to grab the ID of the 'th' element that was clicked for sorting. Then I could say goodbye to column index woes.
With the information at the server, you just need to convert from the column index that DataTables sends to the array of mDataProp options that DataTables also sends. In that way you know what column name to sort the DB on :-)
Allan