Server-Side Column Sorting

GregP

I feel this is the zillionth time this has been asked, but my brain's not working to find the information.

We are using server-side processing, and would like to sort on initialization/by default on a certain column. Let's say the visible column to sort on is 5 (from zero origin). During initialization, this is sent to the server in the query string as iSortCol_0=5. Problem being, this doesn't correspond to anything that makes sense to the server. The server would be "happier" knowing something like iSortCol_0=sessionID where "sessionID" is actually the same string set for the 5th visible column using mDataProp in the aoColumns parameter.

Does that make sense? To rephrase, I use column names already with mDataProp so that if I choose to move things around, everything just clicks into place. No more need to re-calculate a bunch of numbers. So similarly, if I re-order, I'd rather have the query string tell the server to sort on the column whose mDataProp is "sessionID" instead of "5".

Since this has probably already been asked, a link to the existing thread would work; otherwise I'm curious if anyone has found any solutions.

MrBaseball34

The 5 corresonds to the index of the $aColumns array in your server side script. This would, in turn, reference your sort column.

GregP

I don't think that's the case. As I click on the different columns and watch the GET request in the console, the number (in this case, 5) always corresponds to the visible column in the browser, not the data column.

Since we're using Java rather than PHP, there's not really an $aColumns array. The Java takes the request, extracts whatever information it needs, and generates the JSON to be returned. The way the processing occurs, there's no median array of values.

But I think that's just me over-explaining with unimportant information: In this case, the Java could indeed say "Oh, I see I'm sorting on 5, which I know to be the sessionID" and then perform the heavy lifting as needed. However, the problem then becomes if I decide via the aoColumns property that sessionID is now going to be column 0, I would have to update that mapping on the server side as well so that it can say "Oh, I'm sorting on 0, which is sessionID". Better to just be able to say "sort on sessionID".

Not sure if that makes any sense, but the take-away is that the number is representing the visible column; in other words, the 6th member (remember, zero origin) of aoColumns.

GregP

So, interestingly, it's actually even easier on the server developer if I can send him the number that corresponds to the aData object. So in this case, the column is actually 7 (there are 2 invisible columns at 1 and 3). If I can just send the server "7" instead of 5, all is golden.

Any way to do this?

allan

Hi Greg,

What you might be interested in is the mDataProp_(int) option which is also now sent to the server (or sColumns which is similar, but possibly less effective - depreciated for mDataProp). If you set mDataProp for your columns, then whatever you set, will also be sent to the server (it is an integer if you don't). With sNames, you just get a string and you need to split it up when doing the query - which is useful if you don't want to set mDataProp, but a little more work :-)

Allan

GregP

Hey Allan,

We're using mDataProp, but I don't see anything being sent to the server. Here's the query string from the draw request:

sEcho=767&iColumns=10&sColumns=&iDisplayStart=0&iDisplayLength=25&iSortingCols=1&iSortCol_0=5&sSortDir_0=desc&bSortable_0=true&bSortable_1=true&bSortable_2=true&bSortable_3=true&bSortable_4=true&bSortable_5=true&bSortable_6=true&bSortable_7=true&bSortable_8=true&bSortable_9=true&_=1314133692963

No mDataProp_(int) in there!

MrBaseball34

I was unaware that you were using java server side. I will let Allan continue here.

MrBaseball34

BTW, did you see this:
iSortCol_0=5

This tells you which col to sort, I think.

GregP

Yup, that's exactly the parameter I see. Unfortunately, the "5" represents the visible column rather than the data column (which is 7). I'm holding onto the hope that I'm doing something wrong still. ;-) With server-side set to "true" I would imagine that it should always favour the data as represented on the server rather than as rendered on the client.

allan

If you go to this page http://datatables.net/release-datatables/examples/data_sources/server_side.html and make the first column hidden with:

[code]
$('#example').dataTable().fnSetColumnVis(0, false );
[/code]

and then click the last column to sort by it, the request includes:

> iSortCol_0=4

which is what is expected (remembering that the column counting starts at 0).

DataTables should always be working with the data column index, rather than the visible index. If its using the visible index than that's an error - but I'm not sure what would cause that in this case. Any chance I can see it?

Allan

GregP

Allan, I'll see if I can get a tunnel set up to see the page in my environment.

In the meantime, there's a disconnect between the suggestion and what I'm doing, which could end up shining a light on the solution:

What I am currently doing to make the columns visible (or not) is simply to declare mDataProp within the aoColumns property. Any columns with the mDataProp are visible; any others are not. fnSetColumnVis() never factors in.

Perhaps therein lies the problem? Since aoColumns (vs. aoColumnDefs) is an array whose length matches the HTML table (not the length of the data array) it's not able to pick up on the index of the data array?

Would switching to aoColumnDefs help this?

---

Update: also, since using strings in mDataProp changes DT's default of using the array index, is there a disconnect created by THAT instead?

Also: in the example page you point to above, I do indeed see the mDataProp_(int) appearing, along with other variables I don't have in my query. It's a mystery to me. Again, perhaps it's due to my mDataProp declarations all being strings?

GregP

Only just noticed the sName parameter, too. It seems redundant to define both mDataProp and sName with the same string (I understand they will not always be the same, but in my case they are) but perhaps that's an approach to take as well?

DougC

I agree with Greg on this... it would be better if it passed back the mDataProp string. I'm using asp.net MVC, and I don't want to have create a generic sort method. Knowing the index doesn't do me any good at all.

So, instead of iSortCol_0=4 I'd prefer to see sSortCol="OrderType"