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:
<tr><td>05/25/08</td> <!-- snip --> </tr> <tr><td>08/01/08</td> <!-- snip --> </tr> <tr><td></td> <!-- snip --> </tr> <tr><td>12/11/07</td> <!-- snip --> </tr> <tr><td>11/10/07</td> <!-- snip --> </tr>
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
- 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.
Related Posts:
RSS Feed
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
not headers:
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!