On of my users tried to explain a bug to me today. Apparently the results on the search page would not sorting properly. Or they were sorting but not by date but by half or a quarter of the date. Or at all. Or they would sort correctly, but sometimes they didn’t and only half sort them. Needless to say, I was thoroughly confused and since this was not one of those “just read the error message to me” issues, I decided to visit the desk of the person who was complaining.
When I got there, I got a small demonstration. “Watch this!” she said, as if she was going to do some trick and I got scared for a second that she will do something that we could not have predicted, and inadvertently crash the whole application due to some hidden bug. You know the type of the bugs – the ones that are completely missed in code review, overlooked in testing and only come out when a user starts clicking buttons you didn’t even put into the design somehow. Fortunately she just typed a query into a search form, and tried to sort the results by date. Then she triumphantly pointed at the screen: “See! That’s what I mean”.
She was right. The results were indeed getting sorted according to the date column, but the algorithm was wrong. Instead of sorting by date, the table was sorted alphabetically/numerically with the obvious results. So I went back to my desk to figure out what went wrong.
The sorting was done by the Tablesorter Plugin so I assumed that the algorithm was right started digging in our code first. I soon figured out what was causing the issue: blank date values!
It’s simple, to avoid clutter missing dates are simply not displayed. So a table will look a bit like this:
05/25/08 08/01/08 12/11/07 11/10/07 If there are not blank cells in the column, tablesorter script correctly recognizes it as a date column. If it sees blank cells, it reverts back to a text sorting algorithm. Since only some queries would produce blank cells like that, this issue went unnoticed for quite a while. I guess we trusted tablesorter to do the identification thing properly.
The fix was trivial – force the tablesorter to treat date columns as date columns no matter what they contained. You pretty much have to specify the data type for each column:
$(document).ready(function()
{
$("#myTable").tablesorter(
{
headers:
{
0 : { sorter: "shortDate" },
1 : { sorter: "shortDate" },
2 : { sorter: "shortDate" },
6 : { sorter: "shortDate" },
13 : { sorter: "shortDate" },
14 : { sorter: "shortDate" },
16 : { sorter: "shortDate" },
17 : { sorter: "shortDate" },
19 : { sorter: "shortDate" }
},
widthFixed: true,
widgets: ['zebra']
});
});
Btw, guess how I knew that I needed to use the “shortDate” keyword? Because I looked at the tablesorter.js code naturally. Initially I tried “date” but of course that did not work. Next I stared at the online documentation for 20 minutes before I decided it was pointless, so I just downloaded un-minified version of the script, and scanned through it looking for parsers and their names.
Tablesorter is a great plugin, but it really could use more in-depth documentation. While I was digging around in the code, I decided to write down the names of all the parsers for future reference. Here they are:
- text
integer- digit
- currency
- floating
- ipAddress
- url
- isoDate
- percent
- usLongDate
- shortDate
- time
The auto detection in the script works pretty well most of the time. It can however fail for simple reasons such as blank lines. I’m surprised that the list above was nowhere to be found on the page. Oh well… It’s here if you need it.
This helps for initial rendering of the table, but as you start clicking headers this widget falls back to default sorting – ech
Thanks so much for the list of parsers, the documentation for Tablesorter is ridiculously sparse.
This article was helpful, although they changed ‘integer’ id to ‘digit’.
But i have problem with dynamic generate columns in table and column with date has class=”date” and date format is 31 Dec 2009, I want to use
headers: {'date': {sorter: "isoDate"}}not headers:
{5: {sorter: "isoDate"}}FYI, if you use firebug (http://getfirebug.com/) you get log messages such as this:
column:0 parser:digit
column:1 parser:text
column:2 parser:text
column:3 parser:shortDate
column:4 parser:text
column:5 parser:shortDate
Much easier than digging thru code.
FYI, firebug console debugging does nothing to tell us WHY the custom sort ordering does not work for most dates. Using the headers: options and setting the column value to any of the date values did not solve the problem for me, it only sorted once, descending (although the arrow showed ascending), and then as another reader pointed out, it reverted to default sorting once a column header was clicked, so clicking the date header essentially did nothing.
The only fix I’ve been able to get working is to mask the date needed for tablesorter in an html comment BEFORE the actual displayed date value, and setting the headers: option to shortDate on the date column.
I found the error in tablesorter to be a sort scope issue, the custom instantiation is not properly extended to be useful to the sort event within the tabesorter object. Rather than fix it, I went dirty with the html comment.
BTW the format it needed to properly sort dates in the comment was:
Anything else and I found the sorting failed.
Cheers
The problem I had was that my table contained dates – some what where href links, some what were plain text. the sorting was message cause the HTML for the links was being interpreted for the sort. This is working perfectly now!
I still don’t know why my dates were sorting alphabetically. I didn’t have any missing dates. Regardless, your solution to explicity cast the column as a date worked brilliantly.
Thank you.
This post is like the secret chapter at the back of the tablesorter documentation. Thanks!
Just another ‘thank you’ for the tablesorter parser list :) This really should be in their documentation!
Although its a small thing but it does matter alot. If you do not know this solution you would be trying different things like overridding date function or trying to add new parser etc.
Thanks for the good tip.
Thanks for this. Helped me a great deal with a problem I had trying to sort an event listing by date. Couldn’t figure it out and it was doing my head in but this post saved the day for me.
Cheers!
KJ
Great post – I had problems with the sorters too.
I want to have two header rows where :
1st row contains column totals
2nd Row contains column names
How can I prevent tablesorter from sorting when 1st row is clicked?
Thank you, Thank you, Thank you!
Does anyone know if it’s possible to get tablesorter to correctly sort a column of the days of the week? At the moment it just lists them alphabetically. I’ve searched high and low for answers but the only references I’ve found are to force it to parse a column by date etc. as suggested here.
Any suggestions / advice would be greatly appreciated.
Thanks
Hi all, I was wondering if anyone knew how to deal with IE 8 not properly sorting by digits when the column head is clicked. The first column is numerical with the column header text “ID”. Code sample below:
$(document).ready(function(){$("#sortableTable")
.tablesorter({
headers:
{
0 : { sorter: "digit" }
},
widgets: ["zebra"]
})
});
The problem is, in Chrome and Firefox (on Windows 7) I can click on the “ID” column header and the sorting will toggle from ascending to descending (and back).
In IE8 (also Win 7) you can click all you like on the ID column header.
Any help appreciated, thanks.
Adam
Is there a way to sort by day of week?
I agree that this information (the parsers/sorters list) should have been in the documentation.
Sadly, “floating” has evidently been removed. In the 2.0 version I’m looking at, the current list is:
textdigit
currency
ipAddress
url
isoDate
percent
usLongDate
shortDate
time
metadata
Which doesn’t help me. Since I’m trying to get it to sort numerically formatted data (US Locale):
11
22,222
33
But since it cuts off everything after the comma, it sorts 22,222 above 34. Wrong. So, either I’ll have to extend w/ my own sorter or write a textExtractor that removes numeric formatting. Sigh.
Thanks for this post! The table sorter documentation is lacking (because I couldn’t use the class metadata tags, and that is the only method they document), but this is exactly what I needed.
I had this issue of how the sorter was handling dates along with blank values. Turns out the sorter code treats a blank cell as “Jan 1, 1970” (0 in javascript). What I ended up doing was updating the “shortDate” parser to handle blank dates as a constant specified in the config:
tablesorter: new
function () {
var parsers = [],
widgets = [];
this.defaults = {
cssHeader: “header”,
cssAsc: “headerSortUp”,
cssDesc: “headerSortDown”,
cssChildRow: “expand-child”,
sortInitialOrder: “asc”,
sortMultiSortKey: “shiftKey”,
sortForce: null,
sortAppend: null,
sortLocaleCompare: true,
textExtraction: “simple”,
parsers: {}, widgets: [],
widgetZebra: {
css: [“even”, “odd”]
}, headers: {}, widthFixed: false,
cancelSelection: true,
sortList: [],
headerList: [],
dateFormat: “us”,
decimal: ‘/\.|\,/g’,
onRenderHeader: null,
selectorHeaders: ‘thead th’,
debug: false,
treatBlankDateAs: “01/01/0001”
};
…
ts.addParser({
id: “shortDate”,
is: function (s) {
return /\d{1,2}[\/\-]\d{1,2}[\/\-]\d{2,4}/.test(s);
}, format: function (s, table) {
var c = table.config;
if (s === “”) {
s = c.treatBlankDateAs;
}
s = s.replace(/\-/g, “/”);
if (c.dateFormat == “us”) {
// reformat the string in ISO format
s = s.replace(/(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{4})/, “$3/$1/$2”);
} else if (c.dateFormat == “uk”) {
// reformat the string in ISO format
s = s.replace(/(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{4})/, “$3/$2/$1”);
} else if (c.dateFormat == “dd/mm/yy” || c.dateFormat == “dd-mm-yy”) {
s = s.replace(/(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{2})/, “$1/$2/$3”);
}
return $.tablesorter.formatFloat(new Date(s).getTime());
}, type: “numeric”
});