Skip to end of metadata
Go to start of metadata

To deal with large amounts of data, we use the jquery library tablesorter with the Pager with Filter plugins.

Using these plugins, you set which column(s) in the table you would like to search on, and specify which columns are sorted. The actual searches and sorting are done on the server.

Behaviour and Server Notes

Searches need to be case insensitive and partial matches. So if the user enters "fio mal" it will match "fio", "Fiona Malikoff", "Malcolm Fraser" and "OFFIO". Split the search phrase into tokens on whitespace and then and look for each of them in first name, last name and (if appropriate) login separately.

Examples of this may be found in FindUserLessonsAction.getUserSet() and LearnerProgressDAO.buildLearnersByLessonQuery()

When the paging/search is done on the server, the parameters are passed through to the DAO layer and used as part of the Hibernate query. This reduces the number of Java objects required to be built by Hibernate. Two calls are required - one to get the data for the current page and one to get the overall number of matches (for the Page X out of Y indicator).

The input string from the user should be escaped using StringEscapeUtils.escapeSql(searchString) before building the query.

A typical search part of the query would be: " o.firstname LIKE CONCAT('%', :searchString, '%')". This does a case insensitive search using a bound variable.

When a search is done, the results are paged just the same as if a search wasn't done.

Sorting and Searching Code Example

In the following code, the table can be sorted on the first column (column[0]) or the second column (column[1]) and these parameters contain either 0 (sort ascending) or 1 (sort descending). The search/filter string from the first column comes through as fcol[0]. If it was possible to filter on the second column then it would come through as fcol[1].

int size = WebUtil.readIntParam(request, "size");
int page = WebUtil.readIntParam(request, "page");
Integer sortByCol1 = WebUtil.readIntParam(request, "column[0]", true);
Integer sortByCol2 = WebUtil.readIntParam(request, "column[1]", true);
String searchString = request.getParameter("fcol[0]");

int sorting = QaAppConstants.SORT_BY_NO;
if (sortByCol1 != null ) {
    sorting = sortByCol1.equals(0) ? QaAppConstants.SORT_BY_USERNAME_ASC : QaAppConstants.SORT_BY_USERNAME_DESC;
} else if ( sortByCol2 != null ) {
    sorting = sortByCol2.equals(0) ? QaAppConstants.SORT_BY_RATING_ASC : QaAppConstants.SORT_BY_RATING_DESC;

Still to do: Filtering out characters?

UI Notes

To use the search, make sure you are including all the following Javascript files and stylesheets. The widgets file is needed for the searching and for the bootstrap theme.

<link rel="stylesheet" href="${lams}css/jquery.tablesorter.theme.bootstrap.css">
<link rel="stylesheet" href="${lams}css/jquery.tablesorter.pager.css">
<script type="text/javascript" src="${lams}includes/javascript/jquery.tablesorter.js"></script>
<script type="text/javascript" src="${lams}includes/javascript/jquery.tablesorter-pager.js"></script>
<script type="text/javascript" src="${lams}includes/javascript/jquery.tablesorter-widgets.js"></script> 

The following code is in the main stylesheets for LAMS. This should ensure that the disabled search boxes (that appear in columns that do not have a search, when search is enabled) are not shown.

.tablesorter .disabled {display: none}

Typical tablesorter definition. Columns start at index 0, so the entry in header for "1" refers to the second column on the screen. In the example below you can search/filter and sort on the first column, sort only on the second column, and neither filter or sort on the third column.

The "uitheme" widget is needed for the bootstrap theme, as is the headerTemplate entry. If you omit the headerTemplate entry, you won't get the sort icons in the header.

   theme: 'bootstrap',
   widthFixed: true,
   sortInitialOrder: 'desc',
   headerTemplate : '{content} {icon}',
   widgets: ["uitheme", "zebra", "filter"],
   headers: { 1: { filter: false }, 2: { filter: false, sorter: false } },
   widgetOptions : {
	// include column filters
	filter_columnFilters: true,
	filter_placeholder: { search : '<fmt:message key=""/>' },
	filter_searchDelay: 700

This uses the Bootstrap theme (theme-bootstrap.css) with the striped lines widget ("zebra") and the searching widget ("filter"). It sorts and searches on the first column (column 0) but not the second or third columns.

The hint text "Search..." which appears in the search box is internationalized. A typical definition is:

The default search delay is set to 700ms, as the default (300ms) is too short and triggers searches before user has finished typing their search text.

Next define the pager javascript.

$(".tablesorter").each(function() {
	savePages: false,
	container: $(this).find(".ts-pager"),
	output: '{startRow} to {endRow} ({totalRows})',
	cssPageDisplay: '.pagedisplay',
	cssPageSize: '.pagesize',
	cssDisabled: 'disabled',
	ajaxUrl : "some url here....",
	ajaxProcessing: function (data) {
    	    if (data && data.hasOwnProperty('rows')) {
		var rows = [],
		    json = {},
		countRatedItems = data.countRatedItems;

		for (i = 0; i < data.rows.length; i++){
  		   // process the user data				
      = data.total_rows;
		json.rows = $(rows);
	  	return json;
    // bind to pager events
    .bind('pagerInitialized pagerComplete', function(event, options){

The URL specified in ajaxURL needs the parameters &page={page}&size={size} for paging.

The URL specified in ajaxURL needs the parameters &{sortList:column} for sorting.

The URL specified in ajaxURL needs the parameter &{filterList:fcol} for searching. This translates to a fcol[<columnnumber>]=<search value> on the URL.

Finally in the page itself, use the <lams:TSTable> tag to define the table.

<c:set var="numColumns" value="2"/>
<c:if test="${isCommentsEnabled}">
	<c:set var="numColumns" value="3"/>
<lams:TSTable numColumns="${numColumns}">
	<th title="<fmt:message key=''/>" > 
		<fmt:message key="" />
		<fmt:message key="label.rating" />
	<c:if test="${isCommentsEnabled}">
		<fmt:message key="label.comment" />


  • numColumns: Required. Number of columns in the table - needed so that the pager in the footer extends across the table.
  • data-session-id: Optional. Used for tables where an id is required for the URL call.
  • tableClass: Optional. Defaults to "tablesorter". Only include if you need a different class for your table.
  • pagerClass: Optional. Defaults to "ts-pager". Only include if you need a different class for your pager.
  • test: Optional. Set this to anything and "2" is added as a page size selection. Handy for testing the paging. Do not include this attribute in a release version.

The body of the tag is the header row for the table. Do not include the table row tags - they are built into the tag.

  • No labels