[SOLVED] Hidden column sorting not correct

MrBaseball34

aoColumns definition:
[code]
"aoColumns": [null /* Location 0 */
, { "iDataSort": 7 } /* FormalDate 1 */
, null /* Program_Title 2 */
, { "iDataSort": 11 } /* Cost 3 */
, { "bSortable": false } /* Information 4 */
, { "bVisible": false } /* LocLatitude 5 */
, { "bVisible": false } /* LocLongitude 6 */
, { "bVisible": false
, "sType": "date" } /* StartDate 7 */
, { "bVisible": false } /* Months 8 */
, { "bVisible": false } /* Programs 9 */
, { "bVisible": false } /* Zip_Code 10 */
, { "bVisible": false
, "sType": "numeric" } /* rawPrice 11 */
]
[/code]
{
Cost is rawPrice with currency format
rawPrice is price with NO CENTS
}
Problem is the Cost/rawPrice is not sorting correctly, if is doing an alpha sort vs. numeric sort.
FormalDate/Startdate sort works great

JSON is returning correct data but, as you know, JSON returns everything as string.

Any ideas?

MrBaseball34

Any ideas? here is returned JSON:
[code]
{"sEcho":1,"iTotalRecords":500,"iTotalDisplayRecords":"18","aaData":[
["San Antonio ,TX","October 4, 2011","CISR Insuring Personal Residential Property","$169","View Details<\/a> | Register<\/a>","29.4239","-98.5235","2011-10-04"," "," "," ","169"],
["San Antonio ,TX","October 5-8, 2011","CIC Personal Lines","$409","View Details<\/a> | Register","29.4239","-98.5235","2011-10-05"," "," "," ","409"],
["Corpus Christi ,TX","October 5, 2011","CISR Agency Operations","$169","View Details<\/a> | Register<\/a>","27.7958","-97.4002","2011-10-05"," "," "," ","169"],
["Beaumont ,TX","October 6, 2011","CISR Insuring Personal Auto Exposures","$169","View Details<\/a> | Register<\/a>","30.0096","-94.114","2011-10-06"," "," "," ","169"],
["El Paso ,TX","October 10, 2011","CISR Insuring Commercial Property","$169","View Details<\/a> | Register","31.7688","-106.43","2011-10-10"," "," "," ","169"],
["El Paso ,TX","October 11, 2011","CISR Insuring Personal Auto Exposures","$169","View Details<\/a> | Register","31.7688","-106.43","2011-10-11"," "," "," ","169"],
["Tyler ,TX","October 11, 2011","CISR Insuring Personal Auto Exposures","$169","View Details<\/a> | Register<\/a>","32.2644","-95.3135","2011-10-11"," "," "," ","169"],
["El Paso ,TX","October 12-15, 2011","CIC Commercial Casualty","$409","View Details<\/a> | Register<\/a>","31.7688","-106.43","2011-10-12"," "," "," ","409"],
["Austin ,TX","October 12-15, 2011","CRM Analysis of Risk","$430","View Details<\/a> | Register<\/a>","30.2313","-97.716","2011-10-12"," "," "," ","430"],
["El Paso ,TX","October 12, 2011","Texas Insurance Ethics and Consumer Protection","$50","View Details<\/a> | Limited Seats<\/a>","31.7688","-106.43","2011-10-12"," "," "," ","50"]]}
[/code]

Last column is json is the sort column and 50 comes after 430 when sorted asc.

MrBaseball34

BTW, StartDate, Months, Programs and Zip_Code are used as parameters for filtering.

MrBaseball34

My FormalDate sort doesn't seem to work either.
Note that the datatable is loaded from a server-side script.

I modified the aoColumns like this, and it still didn't help:
[code]
"aoColumns": [null /* Location 0 */
, { "iDataSort": 7 } /* FormalDate 1 */
, null /* Program_Title 2 */
, { "iDataSort": 11 } /* Cost 3 */
, { "bSortable": false } /* Information 4 */
, { "bVisible": false } /* LocLatitude 5 */
, { "bVisible": false } /* LocLongitude 6 */
, { "bVisible": false
, "bSortable": true
, "sType": "date" } /* StartDate 7 */
, { "bVisible": false } /* Months 8 */
, { "bVisible": false } /* Programs 9 */
, { "bVisible": false } /* Zip_Code 10 */
, { "bVisible": false
, "bSortable": true
, "sType": "numeric" } /* rawPrice 11 */
]
[/code]
How is server-side script sorting handled?

MrBaseball34

Any ideas on how to fix this problem?

GregP

I can only give general advice since our server-side code isn't based on the DataTables examples; but sorting is entirely in the realm of the server. If you have server-side enabled, data is sorted on the server and then returned in the expected order.

If you have hidden columns, things get tricky. DataTables will send this in the request parameters:

iSortCol_0=3

The problem is that 3 refers to the VISIBLE column in the DOM, not the 'virtual' column in the data object. If you have no hidden columns, it's easy. Just sort on the fourth (remember, zero origin) column of data on the server side.

If you have hidden columns, you have to do a mapping somewhere along the way. The way we chose to handle it was to set an mDataProp for each of the columns, which was a perfect match for the column names on the server-side database. With mDataProp in place, the request parameters include:

isortCol_0=3
mDataProp_3=myColumn

Then it's a matter of mapping the sort column (3) to the particular mDataProp (based on the 3) to retrieve the name of the column. Then the server side could sort on that column by name instead of by index.

This way is also more future proof. You COULD come up with a hack. On the server side, you could say "When isortCol_0 is 3, I know that the server side column is actually 5" and do the mapping that way, but if you ever change the order of your columns, you would need to change this again. It's the most instantly-accessible way to do it if you don't want to start using mDataProp (which also requires sending your JSON as key-value pairs) but it's not nearly as flexible.

Hope that makes sense!

MrBaseball34

I added code to "map" the sorting but still it comes up incorrect.

MrBaseball34

Ok I got everything figure out. The section on ordering in the demo code just wasn't going to work for us at all. I only allow single column sorting so my solution works just fine.