Monday, July 14, 2008

jQuery Tablesorter with Paging AND Filtering

Sorry it's been a while since my last post. I can't seem to really get into this whole blogging thing. Anyway, today I figured I'd talk about a little project I did which required me to have an HTML table that was sortable, pageable, and filterable all on the client side.

First off, I'd like to give a little background info on the project. The site is very simple - only one "real" page. On that page the client wanted to be able to browse their entire inventory which was loaded into a single table in the database. Right off the bat one of my major issues that I ran into was the fact that this table contains about 1600 records, which would take a very long time for the browser to render as an HTML table (at least 3-5 seconds on Firefox 3). Unfortunately, they insisted that all the data be accessible through a single interface, so I was forced to make very heavy use of AJAX techniques in this project.

jQuery is such a sweet Javascript library, and lucky for me it has a ton of great plugins! One such plugin is the jQuery Tablesorter. It was essentially trivial for me to setup this plugin and its Pager extension to knock off 2 of my major requirements for the site. What's even more exciting about this is that the performance of this plugin is so good that sorting and paging these 1600 records felt like a snap! The reason for this is because the Tablesorter plugin actually maintains a JS cache of all the rows in the table. It's this cache that the sorting and paging functions work on to determine which records to show and what order to show them in. The hard part came when it was time to add filtering to the application.

Paging and sorting are useful tools for navigating a set of data, but when you've got 1600 records, the user really needs to be able to filter them to zero-in on what's of interest to them. At first, I setup the filters as a series of dropdowns that would trigger a full reload of the page with parameters passed back to the server so that the filtering could be done by modifying the query that returned the data that gets loaded into the front-end table. This turned out to be a mistake, however, since each time the filter was reset to display all the records, the cache had to be rebuilt for the sorting and paging functions, and whatever records were returned would get rendered by the browser for a split second on the initial page load. That means the user experiences the 3-5 second (minimum) wait time!

Instead, I decided that I'd have to delve into the code of these plugins to integrate a client-side filtering solution with the other required features. Please keep in mind that this is not pretty or clean code since it was written very specifically for the one project that I was working on. Basically I had to modify the appendTable function in jquery.tablesorter.js to look something like this:

function appendToTable(table,cache) {

if(table.config.debug) {var appendTime = new Date()}

var c = cache,
r = c.row,
n= c.normalized,
totalRows = n.length,
checkCell = (n[0].length-1),
tableBody = $(table.tBodies[0]),
rows = [];

var parsers = table.config.parsers;
var short_sel = parsers[1].format(jQuery("#short_only").val());
var ship_sel = parsers[6].format(jQuery("#ship_only_to").val());
var type_sel = parsers[2].format(jQuery("#type_only").val());
var size_sel = parsers[3].format(jQuery("#size_only").val());
var wall_sel = parsers[4].format(jQuery("#wall_only").val());

for (var i=0;i < totalRows; i++) {
var allpassed = true;

if (short_sel != "all" && short_sel != n[i][1]) {
allpassed = false;
}

if (ship_sel != "all" && ship_sel != n[i][6]) {
allpassed = false;
}

if (type_sel != "all" && type_sel != n[i][2]) {
allpassed = false;
}

if (size_sel != "all" && size_sel != n[i][3]) {
allpassed = false;
}

if (wall_sel != "all" && wall_sel != n[i][4]) {
allpassed = false;
}

if (allpassed == true) {
rows.push(r[n[i][checkCell]]);
}
if(!table.config.appender) {

var o = r[n[i][checkCell]];
var l = o.length;
for(var j=0; j < l; j++) {

tableBody[0].appendChild(o[j]);

}

//tableBody.append(r[n[i][checkCell]]);
}
}

if(table.config.appender) {
table.config.appender(table,rows);
}

rows = null;

if(table.config.debug) { benchmark("Rebuilt table:", appendTime); }

//apply table widgets
applyWidget(table);

// trigger sortend
setTimeout(function() {
$(table).trigger("sortEnd");
},0);
};

Anyway... the changes I made amounted to essentially 2 main pieces of code. First, I had code for retrieving the values to sort by:

var parsers = table.config.parsers;
var short_sel = parsers[1].format(jQuery("#short_only").val());
var ship_sel = parsers[6].format(jQuery("#ship_only_to").val());
var type_sel = parsers[2].format(jQuery("#type_only").val());
var size_sel = parsers[3].format(jQuery("#size_only").val());
var wall_sel = parsers[4].format(jQuery("#wall_only").val());

Like I said... it's not pretty code. The table.config.parsers array comes with the jQuery Tablesorter and it is used for normalizing the values found in the table before sorting them (i.e. so that dates and numbers can be sorted in the correct order, etc). I'm borrowing these parsers from the underlying plugin so that I can normalize the values to filter by and compare the result to what's stored in the table row cache. The order of the parsers in the array corresponds to the order of the columns in the table (and as you can see, I've hard coded the array indices here because for now this code is only being used in this one specific instance).

Another thing to note in the above code is that I'm grabbing the jQuery handles to my filter dropdowns by id. The reason for this is purely for performance. Originally I was getting them by their name attribute, but that took far longer than getting them by id. (I have a hunch that the reason for this is that the filter dropdowns appear at the top of the page so the jQuery routine is able to find and return them quickly since there can only be element with that specific id on the page. When I searched by name attribute, since there could be more than one element w/ the same name, jQuery had to go through the entire HTML DOM before returning.)

The other major block of code that I added was a for loop to do the actual filtering:

for (var i=0;i < totalRows; i++) {
var allpassed = true;

if (short_sel != "all" && short_sel != n[i][1]) {
allpassed = false;
}

if (ship_sel != "all" && ship_sel != n[i][6]) {
allpassed = false;
}

if (type_sel != "all" && type_sel != n[i][2]) {
allpassed = false;
}

if (size_sel != "all" && size_sel != n[i][3]) {
allpassed = false;
}

if (wall_sel != "all" && wall_sel != n[i][4]) {
allpassed = false;
}

if (allpassed == true) {
rows.push(r[n[i][checkCell]]);
}
if(!table.config.appender) {

var o = r[n[i][checkCell]];
var l = o.length;
for(var j=0; j < l; j++) {

tableBody[0].appendChild(o[j]);

}

//tableBody.append(r[n[i][checkCell]]);
}
}

Since I wanted only records that passed every filter criteria, I check each column for which there is a filter on each pass, and if any of the column values for that record do not match the corresponding filter value, they do not get included in the results. The r array is the in-memory cache of all the rows in the table, and only whatever it appends back to the table will be included for paging. Obviously the way to make this better would be to make it dynamically detect which columns are getting filtered.

Overall I'm not entirely sure that I should post this, but I'm going to anyway. The end result of what I did provided for a very nice user experience, but the way in which I did it (hard coded array indices, etc) was very ugly and represents some horrible development practices. In order to make this code more reusable, I or someone else will need to come up with a way of dynamically specifying where the filter values can be obtained and which columns get filtered.

Also, the way this is put together is not pretty at all: the fact that I edited the jquery.tablesorter.js file directly means that I cannot upgrade to a newer version of Tablesorter without re-doing everything I did to add filtering to this method. Luckily because of the dynamic nature of Javascript I could probably package this alternative version of the appendTable function in its own file and dynamically replace the one that Tablesorter uses at runtime. This would only solve the issue with upgrading Tablesorter if the new version of Tablesorter doesn't try to change the implementation of appendTable. Other than that, I can see no way to get this code to run between the point at which sorting is done and the point at which paging starts - maybe I just haven't quite looked hard enough.