jQuery Facet Browser
As part of a website I was building for a client, I needed a user interface for browsing a large dataset of music. I could have gone with the usual HTML table where you clicked on the headings, but it didn't really have the right feel for me. So I went a bit further and took some inspiration from both the iTunes interface and extended it through towards the interface for the mSpace browser, where you can reorder the columns however you like. And so, that's what we have here - a search interface in which the columns of the interface can be dragged around.
Here's a demo using some open data from the US Environmental Protection Agency.
You'll see that the first column forms the primary search criterion. The number in brackets shows how many possibilities there are for the second column for each item. As you select an item from the first column you drill down into the database, and the second column becomes the second search criteron and so on through the columns. You'll notice if there's only a single value available in the next column, it will be automatically selected.
The best bit though, is that you can decide in what order to drill through the data. For example, move the MPG column to the front by dragging the column header from its current position to the front. You'll see that the available values in the second column now change and you can drill through the data starting from the MPG value. And you can shuffle the columns in any order you like. Want to find cars that will have cheap UK road tax? Move the CO2 column to the front and the car manufacturer and model next! So, not the Bugatti Veyron SuperSport, then.
What's happening underneath is that the javascript is constructing a chain of facet names and values that are sent to the server to resolve. The panel below shows the values sent to the server for the demo above.
You can see that it basically gives the facet chain and the values that are selected. It's up to the server how to deal with those.
However the server decides to deals with it, it should return a JSON document that contains
three maps. The first, called facets, should give the list of facet names to show
for the selected facet. The second, called counts, should give, for each facet, the
number of results available for each facet. The keys in the maps should match. The last is optional
and is called results. This should give a list of rows (as objects) that match the
facet path.
Server-Side
To help deploy this server-side system, I've created a PHP class, FacetHelper, that will help you to create
SQL queries for resolving the chain, assuming that the facets map one-to-one to columns in a table. This is
included in the package you download above. There's
also a small test service that uses that class. Let's look at the class first.
The main method of the class is called FacetHelper::calculateFacet and will perform the search of the database
based on the incoming facets. The facets this method takes need to be in a specific array-based format, and the
method FacetHelper::getFacetList will help convert the $_GET array into the appropriate
format.
The FacetHelper methods are non-static, so you must instantiate the class. To do this, you need to pass to its constructor a map which maps the names of the facets coming from the Javascript into column names in your database table. This helps to decouple the facet browser from its data source while also providing some anonymity to your database schema.
The FacetHelper class uses RedBeanPHP to access the database. So, as well as including RedBeanPHP library in your service, you also need to setup the database connection (as the FacetHelper assumes that's already available).
I use Composer to handle my PHP dependencies. There is a single-file version of RedBeanPHP which you can simply include, but if you're using Composer, add this to your composer.json file:
{
"require": {
"gabordemooij/redbean": "v3.4.4"
}
}
Run php composer.phar update and you'll have a RedBeanPHP folder in your vendor folder. You need to
including the Composer autoloader and you're all set. Note that there's a composer.json file already
in the package, so you can just download and run composer.
So, here's a really simple service that does those things above:
<?php
// Load the composer dependencies (includes RedBeanPHP)
require_once "vendor/autoload.php";
use RedBean_Facade as R;
// Connect to the database
R::setup('mysql:host=myhost.com;dbname=my_database',
'username','password');
// Include the facet helper
require_once "classes/FacetHelper.php";
// -------------------------------------------------------------- //
// Create a map that maps the facet name to the column name
$table = "carinfo_table_name";
$map = array(
"Manufacturer" => "ManufacturerColumnName",
"Model" => "ModelColumnName",
"MPG" => "MPGColumnName",
"CO2" => "CO2ColumnName",
);
// Instantiate a new facet helper pointing to the table of our car information
$facetHelper = new FacetHelper( $table, $map );
try
{
$r = $facetHelper->calculateFacet(
$facetHelper->getFacetList($_GET),
true, 0, 20 );
}
catch( Exception $e )
{
$r = array();
$r["error"] = $e->getMessage();
$r["success"] = false;
}
header( "Content-type: application/json" );
print json_encode( $r );
die();
?>
Line 2-10 we're getting RedBeanPHP available for the FacetHelper.
Line 17-23 we're setting up the facet to column name mapping.
Line 26 we instantiate our helper.
Line 30 we call the helper to perform the search. We have hardcoded some of the variables here which
we can get from the $_GET variables if we so wish
Line 34 we catch exceptions if there's a problem
Line 41-42 we return JSON to the javascript.
This service above is really very similar to the facets.php file in the download package.
The instantiation of the facet plugin needs to point to this service using the ajaxURL option.
The javascript also sends through the variable all = true in the parameters if results are
to be generated. This can be mapped directly to the parameter $getResults of the
method FacetHelper::calculateFacets as is done in the service in the package (the example above
always passed true). The Javascript is also sends the required number of results variable through
to the API in the GET parameter number.
Again, I didn't put that in the example above, but it's written into the facets.php
in the download package.
facets.php in the download package is set up for the car database we're using as
an example here. If you want to try the demo, it requires
the car database that this page uses, and you can download that here.
Just plop it in the demo directory. The README in the package gives information about getting the demo
running.
Contributing
You can fork the code on GitHub.
License
The code is released under the MIT license, which basically means you can do whatever you like except pretend it's yours.