Last night I needed a simple HTML table with sortable columns, and after a quick Googling, found the TableSorter jQuery plugin. I'd come across it before, but never actually used it. Added a class to my table (for targeting and to leverage the CSS that it comes with), added the required line of JavaScript to initialize it, and I was up and running.
As I tuned the presentation, however, I ran into a couple issues. First, the included styling sets the table width to 100%, but that was easily fixed with a .css("width", "auto") call. I also wanted alternating row backgrounds, and a quick look showed that there is a 'zebra' widget built in that does exactly that, it just has to be enabled.
The biggest problem was with number formatting. Some of the sortable columns contained large numbers, and when I wrapped the output with numberFormat() to add commas, the sorting reverted to textual instead of numeric. A quick look through the source showed why: the 'digit' parser respects only numbers, the decimal point, and an optional leading +/-. So the commas made it skip any numeric sorting and fall back to textual sorting. Fortunately, the fix was simple. Before initializing the table, I just added a new parser that piggybacks on the 'digit' parser's implementation:
jQuery.tablesorter.addParser({
id: "commaDigit",
is: function(s, table) {
var c = table.config;
return jQuery.tablesorter.isDigit(s.replace(/,/g, ""), c);
},
format: function(s) {
return jQuery.tablesorter.formatFloat(s.replace(/,/g, ""));
},
type: "numeric"
});
The only difference between it and the 'digit' parser is that it strips all commas from the string before handing off to the backing methods (the blue stuff). Works like a champ. The separator should probably be parameterized like the decimal stuff is, but I only deal with comma separators in this app, so I didn't bother.
The only thing I wasn't able to figure out (though I'll admit I didn't try very hard) was setting the initial sort to be descending, so when you click a column header for the first time it does a descending sort rather than ascending (with subsequent clicks reversing the sort as normal). Setting up the on-initialization sort to be descending is simple, but I didn't see a way to parameterize user-triggered sorts.
Thanks for publishing this. I was researching this very problem and came across your solution. Thanks again.
how do we handle negative numbers? Doesnt seem to like that much.
Phingers,
Can you describe your problem a little better? I didn't have any issue sorting negative numbers that contained commas using the parser. What specific format are you using? I don't recall for sure, but I believe the built-in stuff doesn't allow a space between the +/- and the number itself.
Just ran into the exact situation you describe in the last paragraph – would be nice to set the initial user click to sort a column by descending. Looking for a solution right now…
Barney, I wrote the docs for Christian for Tablesorter. He's generally very receptive to improvements and plugins so you should drop him a line with your tweak. If nothing else, publishing it on the tablesorter.com site would help others.
Thanks for this. But I have one more problem. If i set comma for decimal parameter
decimal: ',',
the sort for numbers with leading + and – doesn't work properly :( What's wrong here?
pawel,
If you use ',' for the decimal parameter, then you won't want to use the commaDigit parser, as it strips all commas (turning 1,000 – the number one – into 1000), so your sort will be all kinds of wonky. Just change the two replace calls to remove whatever you use for a thousands separator (likely '.') instead of the comma.
My problem is, I would like use comma for decimal and point as thousands separator (german format). But if i set "," as decimal like this:
$("#stock_table").tablesorter({ decimal: ',', widgets: ['zebra'], sortList: [[5,0]], }); )tablesorter can't handle it, when I have some numbers beginning with 0 and leading +/-. Try it out for table with 6 numbers in column: (+1,4; +0,3; -0,13; +4,5; +0,15; -0,1) Commas should be here decimal separator, after i set "decimal: ','". But tablesorter iterprets it not as digit . It doesn't work too, if I use some other symbol for decimal, like "_" or "a". I suppose, the reg expression for Digit ist writtin wrong :( Could You help me with this problem? Thank You for Your answer!
pawel,
I honestly have no idea. Tablesorter isn't my project, and I've not dug around in the code beyond a cursory skim. I don't have need for a custom decimal character (I'm in the US), so I've never looked at how it handles them. Have you emailed Christian about it? No idea if he responds to support requests such as this, but worth a shot if Googling about isn't turning up an answer.
I think, I have to email Christian ;-) and report it here later ;-) Anyway, thanks for Your answer.
it kinda works and it doesn't for negative numbers that are either use a decimal comma or a decimal period.
in my case, I found this works just fine (spanning both english and french version of decimal delimiters – commas and points, and both positive/negative number, sorting them in proper order)… I just added this custom parser to jquery.tablesorter.js itself, but you can use the same code for front-end declarations too:
ts.addParser({ id: "commaDigit", is: function(s, table) { var c = table.config; return $.tablesorter.isDigit(s.replace(/[,.]/g, ""), c); }, format: function(s) { return $.tablesorter.formatFloat(s.replace(/[,.]/g, "")); }, type: "numeric" });Now, I would definitely kill for a tip on how to change the default on-click sorting to DESC instead of ASC… any news there?
@Serb
I haven't looked into the initial DESC sort any more since I posted, and the couple people I've talked to about it haven't had any insight to offer either. I'd imagine hacking the code to be pretty simple (just have to flip some bit's initial value somewhere), but I haven't bothered to dig in.
found it.
the line that says the following in jquery.tablesorter.js file:
// get current column sort order
this.order = this.count++ % 2;
just change that to this instead:
this.order = ++this.count % 2;
Awesome! Thanks Serb.
Yeah, it only took me 3 hours and about a dozen posts all over the net to find a clue where to look for it…
Does anyone know how to configure tablesorter so that some of the columns will sort Descending on first click, while other columns will sort Ascending on first click.
The "use case" is I want Alpha columns (like people's names) to sort A…Z, but if user clicks on say the number column that it would sort Descending (100…1). I was not successful so far using the "headers" config. I think it is using that in conjunction with sortlist. The sortlist example is always showing it firing on page load.
Thanks!
jeremy.
This works like a charm as longs as you have values with commas in the first row.
My problem is my values range from 0 – 2,000 so if the value in the first row doesn't have a comma in it it treats all comma'd values in that corresponding column as strings. For the time being I've put in a hidden row with 9,999 as it's value and it works, but this is obviously not the best solution.
Any suggestions?
kidcardboard,
Are you explicitly registering the parser for your table column, or letting TableSorter figure it out on it's own? If the latter, see if registering it explicitly helps. I don't really know anything about how TableSorter does it's thing, but if you crack it open, it might yield some info that'll help if it still doesn't do the right thing.
That was it. I registered it explicitly for each column and it works now. ty
Hi, I'm encountering the same problem as kidcardboard's. What do you mean by "explicitly registering the parser for the table column"? Could you please explain? Thanks.
Ja,
Check out this example from the TableSorter docs: http://tablesorter.com/docs/example-meta-parsers.html
Thanks a lot for both solutions: the DESC first, and the thousand separator parser. I was working a lot of time without them but now I can improve my tables, thanks :)
This is awesome! So helpful.
How come it didn't make it into the official plugin? I think it's a must!
Thanks,
Amos
I'm struggling here, I've tried to add your code, and even explicitly registering the parsers in the th tags, but it's not working. Has a change in jQuery or the tablesorter project changed the way this is implemented?
I'm sorry, please ignore my last comment. All good!
Ugh, apologies for the comment spam! Just to finish this I had to follow instructions here: http://tablesorter.com/docs/example-parsers.html
That was the only way I was able to get this to work. I'll stop commenting now.
Thank you! Ive been searching for this solution for almost a week and this solved my problem!
Awesome I was trying to find a solution for this issue and your solution did it….
thank you ! thank you! thank you!
Sorry for the dumb question, but I'm struggling. I put your parser code into my js file and referenced it with: $("table").tablesorter( {sorter:'commaDigit'})
And it isn't working. Here's my whole JS file if you don't mind telling me what I'm doing wrong (Thanks a ton):
$(document).ready(function() { jQuery.tablesorter.addParser({ id: "commaDigit", is: function(s, table) { var c = table.config; return jQuery.tablesorter.isDigit(s.replace(/,/g, ""), c); }, format: function(s) { return jQuery.tablesorter.formatFloat(s.replace(/,/g, "")); }, type: "numeric" }); $(function() { $("table").tablesorter( {sorter:'commaDigit'}) }); });it should be
$(function() { $("table").tablesorter( { headers:{ 3:{sorter:'commaDigit'}//3 referin to the header number } ) }); });Thanks for the help.
More Simple:
$(document).ready(function() { var mydigits= function(node) { // extract data from markup and return it var s=node.innerHTML; return s.replace(/,/g, ""); } $(function() { $("#{$chart_id}_table").tablesorter( { textExtraction:mydigits }) }); });Thanks!! I have been trying to figure this out for the past few hours, this worked perfect!