Server-side sorting broken after column reordering

Ironwil616

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.

allan

> 1. Can't change settings once DataTables is initialized. Changes to order and visibility break sorting and filtering by columns.

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

Ironwil616

I'm not sure how the sName is going to help with my sorting index issue. The example page I went to gave an example using mDataProp, but didn't include the server code that interprets the sorting column, so I'm not sure how this was done. When using regular jQuery, one thing I've used that for is to assign click events to a series of elements by class, and then get the ID of the element that fires the click event before firing the event handling code. After clicking on the table header that causes a sort to fire off, I'd like to grab an ID or other attribute I've assigned to it and add the sort information to the aoData collection manually. Right now my initialization code looks like this:

[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.

allan

If you have a look in "examples/server_side/scripts/objects.php" you'll see how the server-side processing script works (PHP obviously, but hopefully it will be relatively easy to do in ASP). I had forgotten that it was using mDataProp actually - which is much more preferable then sName in this case (makes life much cleaner on the client-side).

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