SZV81KpU

1CREATE TABLE IF NOT EXISTS layers (
2	id INT unsigned NOT NULL auto_increment,
3	name VARCHAR(255) NOT NULL,
4	PRIMARY KEY(id)
5);
6
7CREATE TABLE IF NOT EXISTS tags (
8	id INT unsigned NOT NULL auto_increment,
9	name VARCHAR(255) NOT NULL,
10	PRIMARY KEY(id)
11);
12
13CREATE TABLE IF NOT EXISTS layers_tags (
14	layer_id INT unsigned NOT NULL,
15	tag_id INT unsigned NOT NULL,
16	CONSTRAINT fk_layer FOREIGN KEY(layer_id) REFERENCES layers(id),
17	CONSTRAINT fk_tag FOREIGN KEY(tag_id) REFERENCES tags(id)
18);
19
20INSERT INTO layers (name) VALUES
21  ('Paseo por el campo')
22  , ('Fotografía de campo')
23  , ('Computación cuantica')
24  , ('Computación fotografica')
25;
26
27INSERT INTO tags (name) VALUES
28  ('Campo')
29  , ('Computación')
30  , ('Fotografía')
31  , ('Cuantica')
32  , ('Paseo')
33 ;
34
35INSERT INTO layers_tags () VALUES
36(1, 1)
37, (1, 5)
38, (2, 1)
39, (2, 3)
40, (3, 4)
41, (3, 2)
42, (4, 2)
43, (4, 3)
44;
45
46-- Layers
47SELECT l.id, l.name FROM layers AS l;
48
49-- Tags
50SELECT t.id, t.name FROM tags AS t;
51
52-- Layers - Tags
53SELECT l.*, t.name FROM layers AS l JOIN layers_tags AS lt ON l.id = lt.layer_id JOIN tags AS t ON t.id = lt.tag_id;
54
55-- Tags name for an layer
56SELECT t.name FROM layers_tags AS lt JOIN tags AS t ON lt.tag_id = t.id WHERE lt.layer_id = 1;
57
58-- Intersection
59SELECT t.name FROM layers_tags AS lt JOIN tags AS t ON lt.tag_id = t.id WHERE lt.layer_id = 1 AND lt.tag_id IN (
60	SELECT t.id FROM layers_tags AS lt JOIN tags AS t ON lt.tag_id = t.id WHERE lt.layer_id = 2
61);
62
63-- Intersection IDs
64SELECT lt.tag_id FROM layers_tags AS lt WHERE lt.layer_id = 1 AND lt.tag_id IN (SELECT lt.tag_id FROM layers_tags AS lt WHERE lt.layer_id = 2);
65
66-- Intersection count
67SELECT COUNT(t.id) FROM layers_tags AS lt JOIN tags AS t ON lt.tag_id = t.id WHERE lt.layer_id = 1 AND lt.tag_id IN (SELECT t.id FROM layers_tags AS lt JOIN tags AS t ON lt.tag_id = t.id WHERE lt.layer_id = 2);
68
69-- Union
70SELECT t.name FROM layers_tags AS lt JOIN tags AS t ON lt.tag_id = t.id WHERE lt.layer_id = 1
71UNION SELECT t.name FROM layers_tags AS lt JOIN tags AS t ON lt.tag_id = t.id WHERE lt.layer_id = 2;
72
73-- Union count
74SELECT COUNT(u.id) FROM (
75	SELECT t.id FROM layers_tags AS lt JOIN tags AS t ON lt.tag_id = t.id WHERE lt.layer_id = 1
76	UNION SELECT t.id FROM layers_tags AS lt JOIN tags AS t ON lt.tag_id = t.id WHERE lt.layer_id = 2
77) AS u;
78
79-- Union IDs
80SELECT lt.tag_id FROM layers_tags AS lt WHERE lt.layer_id = 1
81UNION SELECT lt.tag_id FROM layers_tags AS lt WHERE lt.layer_id = 2
82;
83
84-- GROUP_CONCAT
85SELECT l.*, GROUP_CONCAT(t.name) AS tags FROM layers AS l JOIN layers_tags AS lt ON l.id = lt.layer_id JOIN tags AS t ON t.id = lt.tag_id GROUP BY(l.id);
86
87-- Tags id
88SELECT
89 l.id
90 , l.name
91 , (SELECT GROUP_CONCAT(lt.tag_id) FROM layers_tags AS lt WHERE lt.layer_id = l.id GROUP BY lt.layer_id) AS tags_id
92FROM layers AS l
93;
94
95-- Related tags order by score
96SET @layer := 4;
97SELECT
98  l.id
99  , l.name
100--  , (SELECT COUNT(lt.tag_id) FROM layers_tags AS lt WHERE lt.layer_id = l.id) AS tags_count
101  , (
102    SELECT COUNT(lt.tag_id)
103    FROM layers_tags AS lt
104    WHERE lt.layer_id = l.id
105    AND lt.tag_id IN (SELECT lt.tag_id FROM layers_tags AS lt WHERE lt.layer_id = @layer)
106  ) / (
107    SELECT COUNT(DISTINCT lt.tag_id) FROM layers_tags AS lt WHERE lt.layer_id = @layer OR lt.layer_id = l.id
108  ) AS score
109FROM layers AS l
110WHERE l.id != @layer
111ORDER BY score DESC
112;