Sélection sur différents critères [Résolu]

[hsoulard]

Bonjour,

Je suis novice en SQL et je tente de faire quelque chose qui dépasse mes compétences, alors si une âme charitable peut m'aider, elle sera la bienvenue.

Je cherche à faire une sélection de produits à partir de leurs caractéristiques.

Dans une première table, j'ai mes produits (version simplifiée) :

Code :

1
2
3
4
5
6
7
 
id_prod   type       marque
----------------------------
1         Type1      MarqueA
2         Type2      MarqueA
3         Type1      MarqueB
4         Type1      MarqueB

Dans une deuxième table, j'ai les caractéristiques de chaque produit (les caractéristiques sont propres à chaque type de produit) :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
id_prod   caract     valeur
------------------------------
1         Caract1    10
1         Caract2    20
1         Caract3    30
2         Feature1   aaa
2         Feature2   bbb
2         Feature3   ccc
3         Caract1    10
3         Caract2    120
3         Caract3    30
4         Caract1    10
4         Caract2    20
4         Caract3    230

L'idée est de trouver tous les produits d'un certain type, d'une ou plusieurs marques, qui présentent certaines caractéristiques.

Par exemple :
- Type = Type1 ET
- Marque = MarqueA OU MarqueB ET
- Caractéristiques : Caract1 = 10 ET Caract3 = 30

Ce qui doit me donner les produits 1 et 3.

En un mot : je fais comment ?

Merci pour votre aide.

Hervé.

[ced]

Bonjour,

Une simple jointure entre les 2 tables sur id_prod, puis les différentes conditions à remplir dans le WHERE, le tout avec un petit DISTINCT en début de requête devrait faire l'affaire...
On te laisse essayer ?

[hsoulard]

Envoyé par ced

Bonjour,

Une simple jointure entre les 2 tables sur id_prod, puis les différentes conditions à remplir dans le WHERE, le tout avec un petit DISTINCT en début de requête devrait faire l'affaire...
On te laisse essayer ?

Bonjour,

J'ai bien essayé, mais c'est la partie sélection des caractéristiques qui me pose problème. J'ai voulu faire un truc du genre :

Code :

1
2
3
4
5
6
7
8
9
 
SELECT DISTINCT produits.id_prod
FROM produits
LEFT JOIN caracteristiques ON produits.id_prod = caracteristiques.id_prod
WHERE
    produits.type LIKE 'Type1' AND
   (produits.marque LIKE 'MarqueA' OR produits.marque LIKE 'MarqueB') AND
   ((caracteristiques.caract LIKE 'caract1' AND caracteristiques.valeur = 10) AND
    (caracteristiques.caract LIKE 'caract3' AND caracteristiques.valeur = 30))

Mais ça ne marche pas. Si je n'ai qu'une caractéristique à vérifier, c'est OK :

Code :

1
2
3
4
5
6
7
8
 
SELECT DISTINCT produits.id_prod
FROM produits
LEFT JOIN caracteristiques ON produits.id_prod = caracteristiques.id_prod
WHERE
    produits.type LIKE 'Type1' AND
   (produits.marque LIKE 'MarqueA' OR produits.marque LIKE 'MarqueB') AND
   (caracteristiques.caract LIKE 'caract1' AND caracteristiques.valeur = 10)

Je ne sais pas exprimer la seconde partie de la sélection.

Merci.

[dehorter olivier]

Bonjour

SELECT DISTINCT produits.id_prod
FROM produits
LEFT JOIN caracteristiques ON produits.id_prod = caracteristiques.id_prod
WHERE
produits.type LIKE 'Type1' AND
(produits.marque LIKE 'MarqueA' OR produits.marque LIKE 'MarqueB') AND
((caracteristiques.caract LIKE 'caract1' AND caracteristiques.valeur = 10) AND
(caracteristiques.caract LIKE 'caract3' AND caracteristiques.valeur = 30))

A priori normal que cela fonctionne mal. Tu indiques que le produit doit avair le "caract" caract1 ET caract3 en même temps. J'imagine que ce n'est pas possible ? oui, non ?

Si oui, ta requête devrait plutôt ressemblée à ceci :

Code sql :

1
2
3
4
5
6
7
8
9
 
SELECT DISTINCT produits.id_prod
FROM produits
LEFT JOIN caracteristiques ON produits.id_prod = caracteristiques.id_prod
WHERE
    produits.type LIKE 'Type1' AND
   (produits.marque LIKE 'MarqueA' OR produits.marque LIKE 'MarqueB') AND
   ((caracteristiques.caract LIKE 'caract1' AND caracteristiques.valeur = 10) 
OR    (caracteristiques.caract LIKE 'caract3' AND caracteristiques.valeur = 30))

Par contre, une question pourquoi utilisez le LIKE ? d'autant que l'on ne voit aucun caractères génériques. Aussi il vaut remplacer les LIKE par de simple =

[hsoulard]

Envoyé par dehorter olivier

Bonjour
A priori normal que cela fonctionne mal. Tu indiques que le produit doit avair le "caract" caract1 ET caract3 en même temps. J'imagine que ce n'est pas possible ? oui, non ?

Si oui, ta requête devrait plutôt ressemblée à ceci :

Code sql :

C'est oui, mais il faut qu'un produit présente les deux caractéristiques à la fois telles que définies.

Voici un exemple peut-être plus concret :

Je cherche un "lave-vaisselle" (mon type) de la marque "Brandt" ou "AEG" (les marques) qui puisse laver "13 couverts" (caractéristique nb_couverts = 13) ET dont le niveau sonore est de "44 db" (caractéristique niveau_sonore = 44).

Je ne veux pas de lave-vaisselle Brandt ou AEG 13 couverts qui émettent 49 db, ni de lave-vaisselle Brandt ou AEG 12 couverts qui émettent 44 db.

S'il le faut, je peux changer de schéma de base de données.

Par contre, une question pourquoi utilisez le LIKE ? d'autant que l'on ne voit aucun caractères génériques. Aussi il vaut remplacer les LIKE par de simple =

Exact. Merci.

[Yanika_bzh]

Comme le dit dehorter olivier, votre probleme vient de votre test logique.
Vous ne faites qu'une jointure avec vos caracteristiques, or vous faites 2 test logiques dessus avec une clause AND... Un tuple ne pourra pas vous ramener de resultat pour valeur=10 ET valeur=30.

Pour votre probleme, vous avez plusieurs solutions, ecrire autant de jointures que vous aurez de clauses AND dans votre test, utiliser des sous requetes, ...

Exemple avec des sous requetes :

Code :

1
2
3
4
5
6
7
8
9
10
SELECT 
	a.id_prod
FROM
	produits a
WHERE
	EXISTS (SELECT 1 FROM caracteristiques b WHERE b.id_produit=a.id_produit AND b.caracat='caract1' AND b.valeur=10)
	AND
	EXISTS (SELECT 1 FROM caracteristiques b WHERE b.id_produit=a.id_produit AND b.caract='caract3' AND b.valeur=30)
	AND
	a.marque IN ('MarqueA','MarqueB')

A tester et a adapter

Bon courage

[hsoulard]

Envoyé par Yanika_bzh

Comme le dit dehorter olivier, votre probleme vient de votre test logique.
Vous ne faites qu'une jointure avec vos caracteristiques, or vous faites 2 test logiques dessus avec une clause AND... Un tuple ne pourra pas vous ramener de resultat pour valeur=10 ET valeur=30.

Oui, c'est ce que j'ai fini par comprendre. Mais je ne voyais pas vraiment de solutions. Je me disais que des sous-requêtes devaient être une voie, mais de là à les écrire....

Envoyé par Yanika_bzh

Pour votre probleme, vous avez plusieurs solutions, ecrire autant de jointures que vous aurez de clauses AND dans votre test, utiliser des sous requetes, ...

Je vais tester votre proposition et l'étudier pour la comprendre. Merci.

[dehorter olivier]

la requête de Yanika_bzh doit fonctionner sans problème

Il me semble avoir lu un petit papier de SQLPro indiquant que les requêtes IN étaient un peu plus optimisées que celles utilisant le EXISTS ...


Par contre, je trouve que la structure de vos tables mériterait d'être optimisée

id_prod caract valeur
------------------------------
1 Caract1 10
1 Caract2 20
1 Caract3 30
2 Feature1 aaa
2 Feature2 bbb
2 Feature3 ccc
3 Caract1 10
3 Caract2 120
3 Caract3 30
4 Caract1 10
4 Caract2 20
4 Caract3 230

cette table mériterait d'être scindée en 2

table caract

id_prod id_caract valeur
------------------------------
1 1 10
1 2 20
1 3 30
3 1 10
3 2 120
3 3 30
4 1 10
4 2 20
4 3 230

table Feature

id_prod id_Feature valeur
------------------------------
2 1 aaa
2 2 bbb
2 3 ccc

Les requêtes ne s'en trouveraient que mieux

[hsoulard]

Envoyé par dehorter olivier

la requête de Yanika_bzh doit fonctionnée sans pb

Je viens de tester, ça marche nickel. En plus, je crois même l'avoir comprise. J'ai découvert les EXISTS et SELECT 1, mais une petite recherche et j'ai compris leurs utilisations.

Envoyé par dehorter olivier

Par contre, je trouve que la structure de vos table mériterait d'être optimisée

Ca c'est bien possible.

Par contre, je ne comprends comment vous scindez la table. Dans l'exemple "caract1" et "feature1" sont des noms de caractéristiques, tandis que "10" et "aaa" sont leurs valeurs. Je ne peux pas les séparer dans des tables différentes (d'autant que cet exemple est limité et qu'il y a beaucoup d'autres possibilités).

Ou bien, proposez-vous de créer une table des caractéristiques avec leur nom et de créer une autre table qui les associe avec leur valeur pour chaque produit ?

[Yanika_bzh]

Envoyé par dehorter olivier

Il me semble avoir lu un petit papier de SQLPro indiquant que les requêtes IN étaient un peu plus optimisées que celles utilisant le EXISTS ...

Ce n'est pas vrai

[dehorter olivier]

Envoyé par Yanika_bzh

Ce n'est pas vrai

je viens de vérifier et c'est même l'inverse

Envoyé par hsoulard

Par contre, je ne comprends comment vous scindez la table. Dans l'exemple "caract1" et "feature1" sont des noms de caractéristiques, tandis que "10" et "aaa" sont leurs valeurs. Je ne peux pas les séparer dans des tables différentes (d'autant que cet exemple est limité et qu'il y a beaucoup d'autres possibilités).

Ou bien, proposez-vous de créer une table des caractéristiques avec leur nom et de créer une autre table qui les associe avec leur valeur pour chaque produit ?

Ce qui me gènait était que dans la colonne "valeur" il se trouve des valeurs numériques (Caract) et des valeurs alphanumériques (Feature), mais si ce n'est pas possible; cela ne pose pas de problème majeur

[CinePhil]

Dans une première table, j'ai mes produits (version simplifiée) :
id_prod type marque
1 Type1 MarqueA
2 Type2 MarqueA
3 Type1 MarqueB
4 Type1 MarqueB

Dans un modèle normalisé, le type et la marque seraient externalisés et il n'y aurait dans ta table des produits que des clés étrangères vers les table type et marque.
MCD :
marque -0,n----proposer----1,1- produit -1,1----typer----0,n- type_produit

Tables :
marque (mrq_id, mrq_nom...)
type_produit (tpr_id, tpr_libelle)
produit (prd_id, prd_id_marque, prd_id_type, prd_nom...)

Est-ce que "version simplifiée" signifie qu'en réalité ton modèle est mieux normalisé ?

Dans une deuxième table, j'ai les caractéristiques de chaque produit (les caractéristiques sont propres à chaque type de produit) :
id_prod caract valeur
------------------------------
1 Caract1 10
1 Caract2 20
1 Caract3 30
2 Feature1 aaa
2 Feature2 bbb
2 Feature3 ccc
3 Caract1 10
3 Caract2 120
3 Caract3 30
4 Caract1 10
4 Caract2 20
4 Caract3 230

Là aussi, les caractéristiques devraient être externalisées et il ne devrait y avoir ici que la clé étrangère référençant l'identifiant de la caractéristique.
Et comme "les caractéristiques sont propre à chaque type de produit", il faut d'abord associer les caractéristiques aux types de produits.
MCD :
type_produit -1,n----avoir----0,n- caracteristique

Tables :
type_produit (tpr_id, tpr_libelle)
caracteristique (car_id, car_nom, car_unite...)
tpr_avoir_car (tac_id_type_produit, tac_id_caracteristique)

Remarque : on pourrait aussi externaliser l'unité (m, db, kg, l, nombre, texte...), ajouter un type de valeur lui aussi externalisé (entier, décimal, varchar...)

Ensuite il faut associer les produits aux caractéristiques :
MCD :
produit -1,n----avoir----caracteristique

Tables :
produit (prd_id, prd_id_marque, prd_id_type, prd_nom...)
caracteristique (car_id, car_nom...)
prd_avoir_car (pac_id_produit, pac_id_caracteristique, pac_valeur)

Remarques : Vu que les valeurs peuvent être de plusieurs types, on choisira le type VARCHAR et on prévoira des fonctions de conversion dans les requêtes au endroits appropriés.

Il faudra aussi prévoir dans le MCD une contrainte d'inclusion entre les deux associations "avoir" pour éviter de donner à un produit une valeur de caractéristique qui n'est pas de son type.

J'arrête là pour le moment, je reviendrai plus tard sur les requêtes mais je donne une indication : pour chercher un produit qui a plusieurs caractéristiques définies, il faut faire des OR entre les conditions du WHERE, grouper par produit et vérifier que le nombre de lignes retournées est égal au nombre de caractérstiques. Ainsi, pas besoin de faire de multiples jointures avec la même table.

Bon apétit !

[hsoulard]

Envoyé par CinePhil

Est-ce que "version simplifiée" signifie qu'en réalité ton modèle est mieux normalisé ?

Oui (je crois). Dans les tables, il y a des clés étrangères vers les caractéristiques, les marques, les types. En fait ces tables sont construites à partir des autres tables du système sous-jacent (Prestashop) exclusivement pour cette fonction de recherche spécifique.

Envoyé par CinePhil

J'arrête là pour le moment, je reviendrai plus tard sur les requêtes mais je donne une indication : pour chercher un produit qui a plusieurs caractéristiques définies, il faut faire des OR entre les conditions du WHERE, grouper par produit et vérifier que le nombre de lignes retournées est égal au nombre de caractérstiques. Ainsi, pas besoin de faire de multiples jointures avec la même table.

Je comprends l'énoncé de l'approche, mais de là à la traduire en SQL...

Envoyé par CinePhil

Bon apétit !

J'y vais de ce pas.

[pacmann]

Salut,

Envoyé par dehorter olivier

je viens de vérifier et c'est même l'inverse
Ce qui me gènait était que dans la colonne "valeur" il se trouve des valeurs numériques (Caract) et des valeurs alphanumériques (Feature), mais si ce n'est pas possible; cela ne pose pas de problème majeur

Je ne sais pas ce que vous pensez être vrai ou pas vrai.
Sauf spécificité d'un SGBD quelconque, aucun des deux n'est vrai.

Sous Oracle, IN ou EXISTS, ça marche aussi bien l'un que l'autre, selon les cas : pas de victoire flagrante pour l'un des mots clefs.

Donc ça dépend : du SGBD, du cas pratique, des volumétries, du plan d'exécution, ... pas de généralisation possible.

Voilà, c'était juste pour faire mon chieur

[CinePhil]

Envoyé par hsoulard

Je suis novice en SQL
...
Je comprends l'énoncé de l'approche, mais de là à la traduire en SQL...

Comme tu débutes en SQL, on va y aller progressivement...

Rappel d'un exemple de besoin que tu as donné :

Envoyé par hsoulard

Je cherche un "lave-vaisselle" (mon type) de la marque "Brandt" ou "AEG" (les marques) qui puisse laver "13 couverts" (caractéristique nb_couverts = 13) ET dont le niveau sonore est de "44 db" (caractéristique niveau_sonore = 44).

Envoyé par hsoulard

En fait ces tables sont construites à partir des autres tables du système sous-jacent (Prestashop)

Ne connaissant pas la structure de l'outil Prestashop, je vais me baser sur celle que j'ai donnée dans mon précédent message, ce sera au moins pédagogique.

Envoyé par CinéPhil

marque (mrq_id, mrq_nom...)
type_produit (tpr_id, tpr_libelle)
produit (prd_id, prd_id_marque, prd_id_type, prd_nom...)
caracteristique (car_id, car_nom, car_unite...)
tpr_avoir_car (tac_id_type_produit, tac_id_caracteristique)
prd_avoir_car (pac_id_produit, pac_id_caracteristique, pac_valeur)

1)

Envoyé par hsoulard

Je cherche un "lave-vaisselle" (mon type)

Quels sont les identifiants des produits qui sont de type 'lave-vaisselle' ?

Code :

1
2
3
4
SELECT prd.prd_id
FROM produit prd
INNER JOIN type_produit tp ON tp.tpr_id = prd.prd_id_type
WHERE tp.tpr_libelle = 'lave_vaisselle'

2)

de la marque "Brandt" ou "AEG" (les marques)

Quels sont les lave-vaisselles de marque "Brandt" ou de marque "AEG" ?

Code :

1
2
3
4
5
6
SELECT prd.prd_id
FROM produit prd
INNER JOIN type_produit tp ON tp.tpr_id = prd.prd_id_type
INNER JOIN marque m ON m.mrq_id = prd.prd_id_marque
WHERE tp.tpr_libelle = 'lave_vaisselle'
    AND m.mrq_nom IN ('AEG', 'Brandt')

3)

qui puisse laver "13 couverts" (caractéristique nb_couverts = 13)

Quels sont les lave-vaisselles de marque "Brandt" ou de marque "AEG" qui ont la valeur 13 pour la caractéristique 'nb_couverts' ?

Code :

1
2
3
4
5
6
7
8
9
10
SELECT prd.prd_id
FROM produit prd
INNER JOIN type_produit tp ON tp.tpr_id = prd.prd_id_type
INNER JOIN marque m ON m.mrq_id = prd.prd_id_marque
INNER JOIN prd_avoir_car pac ON pac.pac_id_produit = prd.prd_id
    INNER JOIN caracteristique c ON c.car_id = pac.pac_id_caracteristique
WHERE tp.tpr_libelle = 'lave_vaisselle'
    AND m.mrq_nom IN ('AEG', 'Brandt')
    AND c.car_nom = 'nb_couverts'
    AND pac.pac_valeur = '13'

4)

ET dont le niveau sonore est de "44 db" (caractéristique niveau_sonore = 44)

Quels sont les lave-vaisselles de marque "Brandt" ou de marque "AEG" qui ont la valeur 13 pour la caractéristique 'nb_couverts' OU dont le niveau sonore est de "44 db" (caractéristique niveau_sonore = 44) ET qui ont ces deux caractéristiques ?

1ère méthode :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT prd.prd_id
FROM produit prd
INNER JOIN type_produit tp ON tp.tpr_id = prd.prd_id_type
INNER JOIN marque m ON m.mrq_id = prd.prd_id_marque
INNER JOIN prd_avoir_car pac ON pac.pac_id_produit = prd.prd_id
    INNER JOIN caracteristique c ON c.car_id = pac.pac_id_caracteristique
WHERE tp.tpr_libelle = 'lave_vaisselle'
    AND m.mrq_nom IN ('AEG', 'Brandt')
    AND 
    (
        (
            c.car_nom = 'nb_couverts'
            AND pac.pac_valeur = '13'
        )
        OR
        (
            c.car_nom = 'niveau_sonore'
            AND pac.pac_valeur = '44'
        )
    )
GROUP BY prd.prd_id
HAVING COUNT(pac.pac_id_produit) = 2

Inconvénient : si on a davantage de caractéristiques à vérifier, ça rallonge la requête.

Autre méthode, ensembliste, à vérifier si ça fonctionne sur tous les SGBD :

Code :

1
2
3
4
5
6
7
8
9
10
11
SELECT prd.prd_id
FROM produit prd
INNER JOIN type_produit tp ON tp.tpr_id = prd.prd_id_type
INNER JOIN marque m ON m.mrq_id = prd.prd_id_marque
INNER JOIN prd_avoir_car pac ON pac.pac_id_produit = prd.prd_id
    INNER JOIN caracteristique c ON c.car_id = pac.pac_id_caracteristique
WHERE tp.tpr_libelle = 'lave_vaisselle'
    AND m.mrq_nom IN ('AEG', 'Brandt')
    AND (c.car_nom, pac.pac_valeur) IN (('nb_couverts', '13'), ('niveau_sonore', '44'))
GROUP BY prd.prd_id
HAVING COUNT(pac.pac_id_produit) = 2

Pour ajouter une caractéristique, il suffit d'ajouter un couple (nom de caractéristique, valeur) dans le dernier IN et d'incrémenter le HAVING de 1.

À vérifier quand même car avec le IN sur la marque, cela risque déjà de multiplier les lignes.

En tout cas, ça décrit le principe dont je donnais l'idée dans mon précédent message.

[hsoulard]

Envoyé par CinePhil

En tout cas, ça décrit le principe dont je donnais l'idée dans mon précédent message.

Eh bien, j'ai matière à réfléchir

Merci beaucoup pour ce post très détaillé et véritablement pédagogique.