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