Sorting & Filtering Tables

So a large portion of the code I write is to output data in table format.  When you have hundreds of rows, it becomes hard to decipher all the data. Plus, the end users are use to Excel, which allows them to sort and filter their data.

I originally tried to implement sorting on the server side, but the down side was that would cause the entire page to reload. I had been working with jQuery but wasn’t sure if it was possible. ¬†After a minute of searching I came across Tablesorter. Set up is very straight forward.

After adding in jQuery and Tablesorter, implementing the code takes one line.

	$().ready(function() {
		$("#tablesorter").tablesorter({sortList:[[0,0]], widgets: ['zebra']});
		// With sortList, I'm telling TableSorter that I want to sort
		//   the first row in ASC order.
		// The zebra widget adds zebra lines to the table to 
		//   aid in readability.
	});

In order for the table to sort you need to make sure that your headings are included in the THEAD tag and the data in the TBODY tag. After that your tables are ready to be sorted.

The next problem I ran into was that the users wanted to be able to filter their results. Again I started with a server-side implementation with the same drawbacks. Then I started tinkering with an idea and came up with the following function.

function filter(id, xclass)
{
	this.id=id;
	this._class=xclass;
}

/**
 * 	In your template you need to add a specific class and id for each
 *     column you want to filter, ie,
 * 	You want to filter by vendor you would add:
 * 		class="vendor" id="vendor-ID#"
 * 	to the vendor column, also the id is incremented so
 *     you can hide the specific row as the funtion runs.
 *
 * 	For each row you need to add the following class & id:
 * 		class="row" id="row-ID#"
 **/

function filterRows()
{
	if(jQuery.support.cssFloat) {
		$('.row').css("display", "table-row");
	} else {
		$('.row').css("display", "block");
	}
	/* So I just run through each row and check to see if it is a match
	 * for the value of the filter dropdown.  
			 * If it is not, the row is hidden.
	 */
	$.each(filterArray, function(i,item) {
		string = $('#'+filterArray[i].id).val();
		xclass = filterArray[i]._class;
	
		if(string.length > 0) {
			$('#tablesorter').find('.'+xclass).each(function(item) {					
				if($(this)[0].id) {
					var value = $(this)[0].innerHTML;
					var row = $(this)[0].id.split("-");
					value = jQuery.trim(value);

					if(value != string) {
						$('#row-'+row[1]).css("display", "none");
					}
				}
			});
		} 
	});
}

To set up each of my columns to be filtered I add this code at the top of my page.

var filterArray = [new filter("VendorId",'vendor'),
				new filter("CategoryId",'category'),
				new filter("WarehouseId",'warehouse'),
				new filter("StorageId",'storage')];
				
// I create an array of each column I want to filter so that I can generate
// the .change() function later and be able to pass in the name and class 
// to the filterRows() function.

$().ready(function() {
	$("#tablesorter").tablesorter({sortList:[[0,0]], widgets: ['zebra']});
	
	$.each(filterArray, function(i,item) {
		$('#'+filterArray[i].id).change(function() {
			filterRows();
		});
	});
});

Each column that you want to filter you add in a SELECT with the id from your filterArray declared earlier.


And finally I format each row and cell that I want to filter by like so.



<tr class=" row" id="row-0">
	<td>A/C Filters</td> 
	<td>12x12x1</td> 
	<td class="vendor" id="vendor-0">Air Filters Inc</td>
	<td class="category" id="category-0">Office</td> 
	<td class="warehouse" id="warehouse-0">Warehouse #1</td> 
	<td class="storage" id="storage-0">Attic</td> 
	<td>6.0</td> 
</tr> 

So now my users can take a table with thousands of rows and sort and filter the data without having to wait on page refreshes. Plus with the available style sheets from TableSorter, my tables look much better than I could ever make them. To see each part in action, check out the example I posted.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>