Discussion :

[mistergamer]

Bonjour,
j'ai à ma charge de réaliser un algorithme pour test. En ce moment je bloque sur une requête SQL que je trouve assez difficile.
Au début j'avais cru que j'avais fait une bonne requête, mais au final je me suis rendu compte que ce n'était pas le cas.
Je doit réaliser un filtre sur des produits selon des critères défini dans la base qui sont cumulé.

les tables :
Produit : idProduit int, nom, Critère1, Critère2, etc...
Filtre : idFiltre int, nom nvarchar;
ConditionFiltre : idConditionFiltre int, critèreFiltre int, #idFiltre_filtre int
ConditionFiltreValeurList : idConditionFiltreValeurList int, valeurConditionFiltre nvarchar, #idConditionFiltre_ConditionFiltre int

Ce que j'ai fait :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
SELECT *
FROM Produit, Filtre
INNER JOIN ConditionFiltre ON idFiltre = idFiltre_filtre
LEFT JOIN ConditionFiltreValeurList ON idConditionFiltre = idConditionFiltre_ConditionFiltre
WHERE idFiltre = 60 AND 
((critèreFiltre = 1 AND valeurConditionFiltre = Critère1) OR (critèreFiltre = 2 AND valeurConditionFiltre = Critère2)
OR (critèreFiltre = 3 AND valeurConditionFiltre = Critère3) OR (critèreFiltre = 4 AND valeurConditionFiltre = Critère4) OR etc...)

dans mon code si le produit appartient à au moins un de ces critères il apparaîtra, alors qu'il faut afficher les produits qui sont avec toutes ces critères
Si je met des "AND" au lieu des "OR" il n'y aura aucun produit, c'est normal puisque "critèreFiltre" ne sera jamais égale à 1, 2, 3, 4, etc dans une seule ligne de la table générer.
tout en sachant le problème de conversion puisque les critères sont des données différentes (int, float, date, string) alors que "valeurConditionFiltre" est en string.
je suis quasiment certain que j'ai oublié des détail à vous montré.

Pouvez-vous m'aider svp à résoudre cette recette parce que je suis au même point depuis plusieurs jours?

merci d'avance

[aieeeuuuuu]

je suis quasiment certain que j'ai oublié des détail à vous montré.

En effet, connaitre votre SGBD sera sans doute utile.
Et pour ce type de problème, un jeu d'essai sous forme de CREATE TABLE + INSERT me semble indispensable : sans cela, on va perdre beaucoup de temps a bien comprendre ce que vous voulez.

[mistergamer]

En effet, connaitre votre SGBD sera sans doute utile.
Et pour ce type de problème, un jeu d'essai sous forme de CREATE TABLE + INSERT me semble indispensable : sans cela, on va perdre beaucoup de temps a bien comprendre ce que vous voulez.

merci je vient de faire une analyse comme demandé. et je viens de relever d'autres éléments comment il y a des critère de comparaison comme (>, <, >=, <=), mais c'est des éléments que je peux résoudre après avoir résolue ce problème de condition.

[SQLpro]

il faut que vous fassiez autant de jointure avec vos tables de filtrage que de conditions exigées.

Sans le DDL de vos tables difficile de vous montrer, un exemple

A +

[aieeeuuuuu]

ou un NOT EXISTS : les produits répondant à tous les critères, sont les produits pour lesquels il n'existe pas de critère non avéré.

[SQLpro]

ou un NOT EXISTS : les produits répondant à tous les critères, sont les produits pour lesquels il n'existe pas de critère non avéré.

Dans ce cas il faut qu'il n'y ait que des ET et pas de OU !

A +

[mistergamer]

merci pour vos aide j'ai réussit à faire cette requette. Pouvez-vous me dire si c'est bon ou non :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
SELECT *
FROM Produit
INNER JOIN ConditionFiltre ON idFiltre = idFiltre_filtre
LEFT JOIN ConditionFiltreValeurList ON idConditionFiltre = idConditionFiltre_ConditionFiltre
WHERE
 
Critéère1 IN (SELECT valeurConditionFiltre FROM Filtre
INNER JOIN ConditionFiltre ON idFiltre = idFiltre_filtre
LEFT JOIN ConditionFiltreValeurList ON idConditionFiltre = idConditionFiltre_ConditionFiltre
WHERE idFiltre = 60 AND valeurConditionFiltre = Critère1) AND
 
Critéère2 IN (SELECT valeurConditionFiltre FROM Filtre
INNER JOIN ConditionFiltre ON idFiltre = idFiltre_filtre
LEFT JOIN ConditionFiltreValeurList ON idConditionFiltre = idConditionFiltre_ConditionFiltre
WHERE idFiltre = 60 AND valeurConditionFiltre = Critère2) AND
 
Critéère3 IN (SELECT valeurConditionFiltre FROM Filtre
INNER JOIN ConditionFiltre ON idFiltre = idFiltre_filtre
LEFT JOIN ConditionFiltreValeurList ON idConditionFiltre = idConditionFiltre_ConditionFiltre
WHERE idFiltre = 60 AND valeurConditionFiltre = Critère3) AND
 
Critéère4 IN (SELECT valeurConditionFiltre FROM Filtre
INNER JOIN ConditionFiltre ON idFiltre = idFiltre_filtre
LEFT JOIN ConditionFiltreValeurList ON idConditionFiltre = idConditionFiltre_ConditionFiltre
WHERE idFiltre = 60 AND valeurConditionFiltre = Critère4)
 
etc...

Qu'est ce que vous en pensez??

[StringBuilder]

Juste un truc : pourquoi avoir X colonnes "critere" dans votre table des produits ?

Pourquoi ne pas avoir ne table "critere", avec nue valeur, un numéro et un lien vers la table produit ?

Ceci permettrait de rendre votre requête bien plus dynamique (et certainement moins coûteuse) mais aussi de vous affranchir d'un nombre de critères fixe.

[StringBuilder]

Voici un exemple complet avec ma suggestion.

Evidemment, les critères sont inclusifs, et la requête ne fonctionne que pour les "critères parmi ceux du filtre".
Si tu veux ajouter des concepts de "ou logique", de "not in" ou "<, >, between" il faudra modifier un peu le modèle et la requête.
Cependant, je ne pense pas que ce soit le cas, puisque ton modèle ne gère pas non plus ces fonctionnalités.

Le code ci-dessous fonctionne sur SQL Server.
Au niveau des contraintes unique notamment, il faudra peut-être adapter selon ton SGBD.

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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);

En espérant que c'est assez clair.

[mistergamer]

Merci pour ta réponse,
La base de données est accessible seulement en lecture. Au aussi j'ai juste simplifié mais la table produit est le résultat de plusieurs jointures.
Merci encore pour vos aides ��
Merci a toute la communauté