Building facets navigation

Facets navigation is a very efficient way of organising navigation/filtering/searching whenever you have a lot of items in your dataset. I am sure that you have already seen and use facets navigation in the past, for instance when you were looking for a specific items while purchasing items through the web. The picture means more that a word so just look at the provided example.

Facets navigation can be achieved in at a multiple ways. Some search engines like Solr (as far as I am aware) has facets support already build in. But in this post I would like to show you how the facets navigation can be made using plain SQL, just for fun and to give you a good understanding of how it works internally.

Basically all you need is to align data in a specific way in the database. You will need three tables.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

Notice that main product table also holds the facets/characteristics. This will be useful while generating the consecutive navigational states (you know when user selects a specific facet this will influence both available facets and listed products). The core problem here is to effectively generate these navigational states.

Using these data structures you can pretty easily obtain an initial list of facets

1
2
3
4

-- Get all facets list with counts
SELECT facet_name, VALUE, COUNT(*) AS COUNT
FROM products_facets
GROUP BY facet_id, VALUE;

When user selects a facet from a previous list you now need to update the available facets list with

1
2
3
4
5
6

-- Get all facets list with counts when user selects some facets
SELECT pf.facet_name, pf.VALUE, COUNT(*) AS c
FROM products_facets pf
JOIN products p ON pf.product_id = p.id
WHERE p.name LIKE '%paint%' AND p.color='orange' AND p.dept='home'
GROUP BY pf.facet_id, pf.VALUE;

Now lets examine how the products queries are changing for the consecutive states

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

-- 1. no facets selected
SELECT p.* FROM products p;

-- 2. one facet selected (with additional query on product name)
SELECT p.* FROM products p LEFT JOIN products_facets pf ON pf.product_id = p.id
WHERE
p.name LIKE '%paint%' AND (
(pf.facet_name = 'color' AND pf.VALUE = 'orange')
);

-- 3. two facets selected (with additional query on product name)
-- NOTICE a HAVING clause appeared with the number of facets
SELECT p.* FROM products p LEFT JOIN products_facets pf ON pf.product_id = p.id
WHERE
p.name LIKE '%paint%' AND (
(pf.facet_name = 'color' AND pf.VALUE = 'orange')
OR (pf.facet_name = 'dept' AND pf.VALUE = 'home')
)
GROUP BY p.id
HAVING COUNT(p.id) = 2;

-- 4. three facets selected (with additional query on product name)
-- NOTICE a HAVING clause now has 3 since there are 3 facets selected
SELECT p.* FROM products p LEFT JOIN products_facets pf ON pf.product_id = p.id
WHERE
p.name LIKE '%paint%' AND (
(pf.facet_name = 'color' AND pf.VALUE = 'orange')
OR (pf.facet_name = 'dept' AND pf.VALUE = 'home')
OR (pf.facet_name = 'dangerous' AND pf.VALUE = 'no')
)
GROUP BY p.id
HAVING COUNT(p.id) = 3;

If you are not quite getting what is the purpose of HAVING clause I suggest that you try running these queries without it. This will give you a good understanding of what is happening.

And that’s it. Below I am posting some initial data for the data structures so that you could play around with the SQL queries more easily.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

CREATE TABLE products (
id INT AUTO_INCREMENT,
name VARCHAR(255) NOT NULL DEFAULT '',
color VARCHAR(50) NOT NULL DEFAULT '',
material VARCHAR(50) NOT NULL DEFAULT '',
dept VARCHAR(50) NOT NULL DEFAULT '',
dangerous VARCHAR(50) NOT NULL DEFAULT '',
purpose VARCHAR(50) NOT NULL DEFAULT '',
PRIMARY KEY (id)
);

INSERT INTO products (name, color, material, dept, dangerous, purpose) VALUES ("blue paint bath","blue","latex","home","no","bathroom");
INSERT INTO products (name, color, material, dept, dangerous, purpose) VALUES ("red paint kitchen","red","latex","home","no","kitchen");
INSERT INTO products (name, color, material, dept, dangerous, purpose) VALUES ("orange paint room out","orange","latex","outdoor","no","room");
INSERT INTO products (name, color, material, dept, dangerous, purpose) VALUES ("orange dangerous paint room","orange","latex","home","yes","room");
INSERT INTO products (name, color, material, dept, dangerous, purpose) VALUES ("blue paint kitchen dangerous","blue","latex","home","yes","kitchen");
INSERT INTO products (name, color, material, dept, dangerous, purpose) VALUES ("red paint bath","red","latex","home","no","bathroom");
INSERT INTO products (name, color, material, dept, dangerous, purpose) VALUES ("orange paint kitchen","orange","latex","home","no","kitchen");
INSERT INTO products (name, color, material, dept, dangerous, purpose) VALUES ("orange dangerous paint bath","orange","latex","home","yes","bathroom");

CREATE TABLE facets (
id INT AUTO_INCREMENT,
name VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (id)
);
INSERT INTO facets (name) VALUES ("color");
INSERT INTO facets (name) VALUES ("material");
INSERT INTO facets (name) VALUES ("dept");
INSERT INTO facets (name) VALUES ("dangerous");
INSERT INTO facets (name) VALUES ("purpose");

CREATE TABLE products_facets (
product_id INT NOT NULL,
facet_id INT NOT NULL,
facet_name VARCHAR(50) NOT NULL DEFAULT '',
VALUE VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (product_id, facet_id, VALUE)
);

INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (1,1,"color","blue");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (1,2,"material","latex");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (1,3,"dept","home");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (1,4,"dangerous","no");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (1,5,"purpose","bathroom");

INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (2,1,"color","red");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (2,2,"material","latex");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (2,3,"dept","home");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (2,4,"dangerous","no");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (2,5,"purpose","kitchen");

INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (3,1,"color","orange");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (3,2,"material","latex");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (3,3,"dept","outdoor");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (3,4,"dangerous","no");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (3,5,"purpose","room");

INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (4,1,"color","orange");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (4,2,"material","latex");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (4,3,"dept","home");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (4,4,"dangerous","yes");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (4,5,"purpose","room");

INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (5,1,"color","blue");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (5,2,"material","latex");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (5,3,"dept","home");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (5,4,"dangerous","yes");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (5,5,"purpose","kitchen");

INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (6,1,"color","red");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (6,2,"material","latex");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (6,3,"dept","home");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (6,4,"dangerous","no");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (6,5,"purpose","bathroom");

INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (7,1,"color","orange");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (7,2,"material","latex");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (7,3,"dept","home");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (7,4,"dangerous","no");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (7,5,"purpose","kitchen");

INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (8,1,"color","orange");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (8,2,"material","latex");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (8,3,"dept","home");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (8,4,"dangerous","yes");
INSERT INTO products_facets (product_id, facet_id, facet_name, VALUE) VALUES (8,5,"purpose","bathroom");

bamatosi

Research notes on text/data mining, software development and other stuff that attracts my geeky attention

Building facets navigation

Leave a Reply Cancel reply