Faceted Search (Layered Search) on MySQL database with example.

Last few day I was searching for the layered search using MySQL. Layered Search is actually a Faceted Search. (Magento, a highly professional e-commerce platform on php name it Layered Search). Faceted Search can be done using two way MySQL/Any DB Application or using Apache Solr.

In this post I will show you how we can do Faceted search using MySQL database. You need a specific database schema, but it’s feasible. Here’s a simple example:

product Table

+----+------------+
| id | name       |
+----+------------+
|  1 | blue paint |
|  2 | red paint  |
+----+------------+

classification Table

+----+----------+
| id | name     |
+----+----------+
|  1 | color    |
|  2 | material |
|  3 | dept     |
+----+----------+

product_classification Table

+------------+-------------------+-------+
| product_id | classification_id | value |
+------------+-------------------+-------+
|          1 |                 1 | blue  |
|          1 |                 2 | latex |
|          1 |                 3 | paint |
|          1 |                 3 | home  |
|          2 |                 1 | red   |
|          2 |                 2 | latex |
|          2 |                 3 | paint |
|          2 |                 3 | home  |
+------------+-------------------+-------+

So, say someones search for paint, you’d do something like:

SELECT p.* FROM product p WHERE name LIKE '%paint%';

This would return both entries from the product table.

Once your search has executed, you can fetch the associated facets (filters) of your result using a query like this one:

SELECT c.id, c.name, pc.value FROM product p
   LEFT JOIN product_classification pc ON pc.product_id = p.id
   LEFT JOIN classification c ON c.id = pc.classification_id
WHERE p.name LIKE '%paint%'
GROUP BY c.id, pc.value
ORDER BY c.id;

This’ll give you something like:

+------+----------+-------+
| id   | name     | value |
+------+----------+-------+
|    1 | color    | blue  |
|    1 | color    | red   |
|    2 | material | latex |
|    3 | dept     | home  |
|    3 | dept     | paint |
+------+----------+-------+

So, in your result set, you know that there are products whose color are blue and red, that the only material it’s made from is latex, and that it can be found in departments home and paint.

Once a user select a facet, just modify the original search query:

SELECT p.* FROM product p
   LEFT JOIN product_classification pc ON pc.product_id = p.id
WHERE 
   p.name LIKE '%paint%' AND (
      (pc.classification_id = 1 AND pc.value = 'blue') OR
      (pc.classification_id = 3 AND pc.value = 'home')
   )
GROUP BY p.id
HAVING COUNT(p.id) = 2;

So, here the user is searching for keyword paint, and includes two facets: facet blue for color, andhome for department. This’ll give you:

+----+------------+
| id | name       |
+----+------------+
|  1 | blue paint |
+----+------------+

So, in conclusion. Although it’s available out-of-the-box in Solr, it’s possible to implement it in SQL fairly easily.

 

Thanks stackoverflow.com