Filtering the Data Displayed by YUI 3 DataTable

By YUI TeamMarch 1st, 2011

In addition to sorting, which is supported by YUI 3 DataTable, it is often useful to be able to filter the data and display a subset of the available rows. The Query Builder widget in the YUI 3 Gallery provides a UI for constructing a simple filter expression.

(Click the screenshot to play with this example.)

History

The first version was written by a colleague working with me on the Yahoo! Publisher Network (YPN). (He left soon afterwards to attempt honest employment. Following the precedent set by Jamie Zawinski, he opened a pub to sell beer — home brewed, no less! But I digress….) After hacking together the first version of Query Builder, he made the mistake of showing it to me. A few days later, he complained, “You rewrote the whole thing!” In fact, I have rewritten it several times over the years. YPN is gone, but the latest YUI 2 version of Query Builder powers all data tables in APT, Yahoo’s display advertising management platform. The port to YUI 3 is actually the least amount of work I have had to do to generate a new version!

How the example works

The core of the example is (1) the Query Builder configuration which specifies how the user can filter the data and (2) the extension to Y.DataSource.Local which implements the filter. (For server side pagination, you would send the filter data to the server and bake it into your SQL query.)

To configure Query Builder, the example first defines a list of the variables that can be filtered:

var var_list =
[
	{
		name: 'title',
		type: 'string',
		text: 'Title'
	},
	{
		name: 'year',
		type: 'number',
		text: 'Year',
		validation: 'yiv-integer:[0,2100]'
	},
	{
		name: 'quantity',
		type: 'number',
		text: 'Quantity',
		validation: 'yiv-integer:[0,]'
	}
];

Each variable is assigned a name (matching the key in the DataTable column configuration) and a type. The default types are ‘string’, ‘number’, and ‘select’, but you can extend this by building custom plugins (see below). For each type that you use, you must also define a set of operators:

var ops =
{
	string:
	[
		{ value: 'contains',    text: 'Contains' },
		{ value: 'starts-with', text: 'Starts with' },
		{ value: 'ends-with',   text: 'Ends with' }
	],

	number:
	[
		{ value: 'equal',   text: '=' },
		{ value: 'less',    text: '<=' },
		{ value: 'greater', text: '>=' }
	]
};

This specifies the operators that the user can apply to each variable type. (If you need different sets of operators for variables of the same fundamental type, you can clone the type. See the Plugins section below.)

Y.FormManager is used to validate the values entered by the user before the filter is applied. The validation key for each variable in the above Query Builder configuration provides CSS data which is interpreted by Y.FormManager.

If all validations pass, a request is sent to the data source. The extension to Y.DataSource.Local is quite simple. It merely filters the data before returning the results:

Y.extend(CustomDataSource, Y.DataSource.Local,
{
	_defDataFn: function(e)
	{
		var data = filterData(e.data, e.request.filter);
		var response =
		{
			results: data.slice(e.request.recordOffset,
						e.request.recordOffset + e.request.rowsPerPage),
			meta:
			{
				totalRecords: data.length
			}
		};

		this.fire("response", Y.mix({response: response}, e));
	}
});

The filter element of the request is obtained from QueryBuilder.toDatabaseQuery(), which returns a list of [variable, operator, value] tuples. Also note that the response must include information on the total number of records, since this changes based on the filter being applied.

filterData() simply loops over the tuples from toDatabaseQuery(), applying the filter operators defined in a two level lookup table:

var filters =
{
	string:
	{
		contains: function(value, filter)
		{
			return (value.indexOf(filter) >= 0);
		},
		'starts-with': function(value, filter)
		{
			return (value.substr(0, filter.length) == filter);
		},
		'ends-with': function(value, filter)
		{
			return (value.substr(-filter.length) == filter);
		}
	},

	number:
	{
		equal: function(value, filter)
		{
			return (parseInt(value, 10) == parseInt(filter, 10));
		},
		less: function(value, filter)
		{
			return (parseInt(value, 10) <= parseInt(filter, 10));
		},
		greater: function(value, filter)
		{
			return (parseInt(value, 10) >= parseInt(filter, 10));
		}
	}
};

After all this, DataTable simply displays what it receives from the data source.

Plugins

Y.QueryBuilder.plugin_mapping defines the mapping of type names to actual classes. You can augment this mapping in two ways:

  1. Clone an existing type by defining a new name for the same class. This allows different sets of operators for different variables of the same fundamental type.
  2. Create a new type by implementing the plugin API. Studying the source code for the existing plugins is the best way to get a feel for how this API works.

Generalizing Query Builder

Query Builder does not support parentheses, so you can either AND all the conditions or OR all the conditions. While it is possible to introducing parentheses into a graphical representation of a Boolean expression, all the designs that I have seen are too cumbersome to use. A textual representation is much simpler and easier to manipulate. Expression Builder incorporates Query Builder into a widget that allows constructing a textual representation without having to remember the syntax or type everything in by hand.

About the author: John Lindal (@jafl5272 on Twitter) is one of the lead engineers constructing the foundation on which Yahoo! APT is built. Previously, he worked on the Yahoo! Publisher Network.

4 Comments

  1. I plugged in the YUI DataTable Column sorting and this bombs out. Is this because of the pagination or the Filter Table? Has this been tested by anyone to work with this table?

    http://developer.yahoo.com/yui/3/examples/datatable/datatable_sort.html

    Any help would be greatly appreciated.

    Thanks

  2. The issue appears to be that Y.Plugin.DataTableSort is not compatible with Y.Plugin.DataTableDataSource. Y.Plugin.DataTableSort plugs into the recordset as well as the datatable. But every time the datasource returns data, a new Y.RecordSet is created, so the connection is lost!

    Note that, even without the bug in YUI 3, Y.Plugin.DataTableSort does not understand pagination at all, so it cannot work with this example.

  3. Hi John,

    Thanks for the update.

    I have one final question for you…

    It looks to be using “AND” only in the filtering… Is it possible to plug in “OR” when selecting multiples of the same field?

    For example, I want to return all records that begin with “P” from the “Title” field and all of the records that begin with “T” from the “Title” field.

    This currently returns 0 records. Is there a workaround or plugin one could use that would recognize this and use the “OR” operator when the same multiple fields are begin used?

    Thanks for your time.

  4. The AND is in the example code. You can modify filterData() and its helper, applyFilter(), to implement OR instead.