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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
|
-- Un produit a un nom, et c'est tout
create table produit
(
produit_id int not null primary key,
produit_nom varchar(50) not null
);
-- Un critère à un nom, et c'est tout. Il peut-être intéressant d'améliorer le métamodèle pour gérer un type, des unités, etc. En effet, si tu veux faire des comparaisons, c'est pas évident avec de la data en vrac.
create table critere
(
critere_id int not null primary key,
critere_nom varchar(50) not null
);
-- Un produit peut avoir plusieurs critères valués et un critère valué peut concerner plusieurs produits
create table produit_critere
(
produit_critere_id int not null primary key,
produit_id int not null references produit(produit_id),
critere_id int not null references critere(critere_id),
produit_critere_valeur varchar(50) not null,
constraint uc_critere_produit unique (produit_id, critere_id)
);
-- Une recherche porte juste un nom
create table recherche
(
recherche_id int not null primary key,
recherche_nom varchar(50) not null
);
-- Une recherche porte sur un ensemble de critères
create table recherche_critere
(
recherche_critere_id int not null primary key,
recherche_id int not null references recherche(recherche_id),
critere_id int not null references critere(critere_id),
constraint uc_recherche_critere unique (recherche_id, critere_id)
);
-- Chacun des critères d'une recherche peut avoir plusieurs valeurs (énumération)
create table recherche_critere_valeur
(
recherche_critere_valeur_id int not null,
recherche_critere_id int not null references recherche_critere(recherche_critere_id),
recherche_critere_valeur_valeur varchar(50) not null,
constraint uc_recherche_critere_valeur unique (recherche_critere_id, recherche_critere_valeur_valeur)
);
-- On crée des critères
insert into critere (critere_id, critere_nom) values (1, 'RPM');
insert into critere (critere_id, critere_nom) values (2, 'Taille');
insert into critere (critere_id, critere_nom) values (3, 'Interface');
-- Le disque 1 est un disque de 1 To, à 7200rpm et SATA-II
insert into produit (produit_id, produit_nom) values (1, 'HD 1');
insert into produit_critere (produit_critere_id, produit_id, critere_id, produit_critere_valeur) values (1, 1, 1, '7200');
insert into produit_critere (produit_critere_id, produit_id, critere_id, produit_critere_valeur) values (2, 1, 2, '1 To');
insert into produit_critere (produit_critere_id, produit_id, critere_id, produit_critere_valeur) values (3, 1, 3, 'SATA-II');
-- Le disque 2 est un disque de 2 To, à 5400rpm et SATA-III
insert into produit (produit_id, produit_nom) values (2, 'HD 2');
insert into produit_critere (produit_critere_id, produit_id, critere_id, produit_critere_valeur) values (4, 2, 1, '5400');
insert into produit_critere (produit_critere_id, produit_id, critere_id, produit_critere_valeur) values (5, 2, 2, '2 To');
insert into produit_critere (produit_critere_id, produit_id, critere_id, produit_critere_valeur) values (6, 2, 3, 'SATA-III');
-- Le disque 3 est un disque de 4 To, à 10000rpm et SATA-III
insert into produit (produit_id, produit_nom) values (3, 'HD 3');
insert into produit_critere (produit_critere_id, produit_id, critere_id, produit_critere_valeur) values (7, 3, 1, '10000');
insert into produit_critere (produit_critere_id, produit_id, critere_id, produit_critere_valeur) values (8, 3, 2, '4 To');
insert into produit_critere (produit_critere_id, produit_id, critere_id, produit_critere_valeur) values (9, 3, 3, 'SATA-III');
-- Le disque 4 est un disque de 8 To, à 7200rpm et SATA-III
insert into produit (produit_id, produit_nom) values (4, 'HD 8 To');
insert into produit_critere (produit_critere_id, produit_id, critere_id, produit_critere_valeur) values (10, 4, 1, '7200');
insert into produit_critere (produit_critere_id, produit_id, critere_id, produit_critere_valeur) values (11, 4, 2, '8 To');
insert into produit_critere (produit_critere_id, produit_id, critere_id, produit_critere_valeur) values (12, 4, 3, 'SATA-III');
-- On recherche les disques SATA-III qui tournent à au moins 7200rpm (on énumère toutes vitesses >= 7200rpm)
insert into recherche (recherche_id, recherche_nom) values (1, 'Disques SATA-III au moins 7200rpm');
insert into recherche_critere (recherche_critere_id, recherche_id, critere_id) values (1, 1, 3);
insert into recherche_critere_valeur (recherche_critere_valeur_id, recherche_critere_id, recherche_critere_valeur_valeur) values (1, 1, 'SATA-III');
insert into recherche_critere (recherche_critere_id, recherche_id, critere_id) values (2, 1, 1);
insert into recherche_critere_valeur (recherche_critere_valeur_id, recherche_critere_id, recherche_critere_valeur_valeur) values (2, 2, '7200');
insert into recherche_critere_valeur (recherche_critere_valeur_id, recherche_critere_id, recherche_critere_valeur_valeur) values (3, 2, '10000');
-- On recherche les disques qui tournent à 7200rpm qui font au moins 2 To (on énumère toutes les tailles >= 2 To)
insert into recherche (recherche_id, recherche_nom) values (2, 'Disques 7200rpm au moins 2 To');
insert into recherche_critere (recherche_critere_id, recherche_id, critere_id) values (3, 2, 1);
insert into recherche_critere_valeur (recherche_critere_valeur_id, recherche_critere_id, recherche_critere_valeur_valeur) values (4, 3, '7200');
insert into recherche_critere (recherche_critere_id, recherche_id, critere_id) values (4, 2, 2);
insert into recherche_critere_valeur (recherche_critere_valeur_id, recherche_critere_id, recherche_critere_valeur_valeur) values (5, 4, '2 To');
insert into recherche_critere_valeur (recherche_critere_valeur_id, recherche_critere_id, recherche_critere_valeur_valeur) values (6, 4, '4 To');
insert into recherche_critere_valeur (recherche_critere_valeur_id, recherche_critere_id, recherche_critere_valeur_valeur) values (7, 4, '8 To');
-- Seuls les disques 3 et 4 répondent à la première recherche
select r.recherche_nom, p.produit_nom
from produit p -- On recherche tous les produit
inner join produit_critere pc on pc.produit_id = p.produit_id -- Qui ont pour critère
left outer join recherche_critere rc on rc.critere_id = pc.critere_id -- Les critères qui sont dans la recherche
-- v-- Et qui ont la valeur du critère égale à celle du filtre
left outer join recherche_critere_valeur rcv on rcv.recherche_critere_id = rc.recherche_critere_id and rcv.recherche_critere_valeur_valeur = pc.produit_critere_valeur
left outer join recherche r on r.recherche_id = rc.recherche_id
where r.recherche_id = 1
group by r.recherche_nom, p.produit_nom
having count(pc.critere_id) = count(rcv.recherche_critere_id); -- Et on ne garde que les produits qui ont autant de critères recherchés que de critères filtrés
-- Seul le disque 4 répond à la seconde recherche
select r.recherche_nom, p.produit_nom
from produit p
inner join produit_critere pc on pc.produit_id = p.produit_id
left outer join recherche_critere rc on rc.critere_id = pc.critere_id
left outer join recherche_critere_valeur rcv on rcv.recherche_critere_id = rc.recherche_critere_id and rcv.recherche_critere_valeur_valeur = pc.produit_critere_valeur
left outer join recherche r on r.recherche_id = rc.recherche_id
where r.recherche_id = 2
group by r.recherche_nom, p.produit_nom
having count(pc.critere_id) = count(rcv.recherche_critere_id); |
