You probably heard it before: faceted search. Many webshops use this way of searching in their catalog. But what is it exactly? And how can you implement it on your own website? In this blogpost we will try to explain what it is and how you can implement it.
What is faceted search?
Faceted search is a technique for searching through a set of products or items with the help of specific filters. Every product in a catalog has certain properties on which these filters are based. Large websites such as amazon.com and bol.com use faceted search. The exact implementation on these websites differs (Amazon sometimes doesn’t show the number of products that can be found by applying the filter), but the main technique is the same.
Implement a faceted search on your own website
So how can you implement the faceted search technique on your website? Before we will dive into this technique, let’s identify some challenges first:
- Performance with many products. Visitors don’t want to wait too long to view the result of applying filters
- Clean URL-structure. Instead of getting URLs like ../?cat=3124&brand=2411&memory=321, it would be better to have URLs like .../tablets/apple/16gb/...
- SEO friendly. You don’t want to make every filter indexable by Google.
OK, let’s start. As an example we will use a simple webshop that is selling electronic devices, such as tablets. In their catalog every product has properties which belong to a single property-type:
- Brand (Apple, Samsung, HTC, etc)
- Memory size (8GB, 16GB, 32GB, etc)
- Colour (White, Black, etc)
- Ram (512mb, 1GB, 2GB, etc)
- Webcam (1MP, 2MP, 3,2MP)
In this example the PropertyTypes are: brand, memory, colour, ram and webcam. The properties itself are Apple, Samsung, 8GB, White, etc. To store the products with their properties, we will use the following tables:
Products (id, productname) PropertyTypes (id, name) Properties (id, name, propertytype_id) ProductsProperties (product_id, property_id)
Searching the catalog for products
Getting all products from the catalog is quite easy: just select all products:
SELECT * FROM products;
But, what will happen if three filters are selected (for instance: 16GB, Black, 3,2MP)? The most straightforward solution is to join products with properties three times (for every filter once!). However, when the number of resulting products is large, such a query can be painfully slow because the intermediate result of joining ProductsProperties can be very, very big! Instead of joining ProductsProperties for every selected filter we will use a trick here: we will select all rows in ProductsProperties which has one of the selected filters. Next, we will group the resulting rows on product_id and select the sum of this grouping. The query will be something like:
SELECT SUM(product_id) as total, product_id FROM ProductsProperties JOIN properties ON properties.id = productsproperties.property_id WHERE properties.name = ‘16GB’ OR properties.name=’Black’ OR properties.name = ‘3,2MP’ GROUP BY product_id;
The column ‘total’ expresses how many filters are matched with our search. So, only the products who have a total of 3 are the products we are looking for. This query is substantially faster because there is only one join which cannot make the intermediate result any bigger than the ProductsProperties table already is (N:1 relation)!
So, our first challenge is solved! Getting the appropriate products from the database is quite easy now! In the next part we will cover our second challenge and explain how you can implement the filters on your website.