Article métadonnées et filtres

[vinzzzz]

Bonjour,

Concernant l'article traitant des métadonnées, et plus particulièrement la partie intérrogation:
http://sqlpro.developpez.com/cours/m...adonnees/#L2.4

Lorsque l'on souhaite récupérer l'ensemble des données qui satisfont plusieurs conditions sur un ensemble de caractéristiques, j'ai du mal à comprendre comment la requête suivante peut répondre à cette problématique:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT *
FROM   TR_PROSPECT_PRP PRP
       LEFT OUTER JOIN T_DONNEE_DON DON
            ON PRP.PRP_ID = DON.CRE_LIGNE
WHERE  DON.TBL_ID = 44            -- lien pour la table TR_PROSPECT_PRP
  AND  DON.CAR_ID = 123           -- lien pour une caractéristique donnée, par exemple 'CHIFFRE D''AFFAIRE'
  AND  CRE.CRE_VALEUR = valeur 1  -- valeur demandée pour la caractéristique sus mentionnée
  AND  DON.CAR_ID = 475
  AND  CRE.CRE_VALEUR = valeur 2
  AND  DON.CAR_ID = 74
  AND  CRE.CRE_VALEUR = valeur 3
  AND  DON.CAR_ID = 7
  AND  CRE.CRE_VALEUR = valeur 4

Dans cette exemple si je comprends bien, on souhaite récupérer les données qui satisfont 4 contraintes utilisant 4 caractéristiques différentes modélisées dans la table de métadonnées.

Or il me semble que lorsqu'on arrive ici:

Code :

1
2
3
4
5
6
7
 
SELECT *
FROM   TR_PROSPECT_PRP PRP
       LEFT OUTER JOIN T_DONNEE_DON DON
            ON PRP.PRP_ID = DON.CRE_LIGNE
WHERE  DON.TBL_ID = 44            -- lien pour la table TR_PROSPECT_PRP
  AND  DON.CAR_ID = 123           -- lien pour une caractéristique donnée, par exemple

Il ne reste déjà plus que les lignes associées à la caractéristique 123, et les conditions suivantes qui impliquent la présence d'autres caractéristiques ne peuvent donc être satisfaite...

Dit autrement, je comprends que cette requête ne peut que renvoyer un ensemble vide. Dans la table T_DONNEES_DON, chaque ligne est associée à une valeur unique de CAR_ID, et ne peut donc satisfaire une condition du type CAR_ID=8 AND CAR_ID=9...

Pouvez -vous m'indiquer ce que je n'ai pas compris ?

Merci d'avance

[CinePhil]

Je crois que tu as raison et qu'il manque des parenthèses et des OR :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
WHERE  DON.TBL_ID = 44
  AND  
  (
    (
        DON.CAR_ID = 123
        AND  CRE.CRE_VALEUR = valeur 1
    )
    OR  
    (
        DON.CAR_ID = 475
        AND  CRE.CRE_VALEUR = valeur 2
    )
    OR  
    (
        DON.CAR_ID = 74
        AND  CRE.CRE_VALEUR = valeur 3
    )
    OR  
    (
        DON.CAR_ID = 7
        AND  CRE.CRE_VALEUR = valeur 4
    )
  )

[vinzzzz]

Certes. Le probleme de cette requête c'est qu'elle permet en effet de faire une requête de type OR sur plusieurs conditions, mais que si l'on souhaite faire plutôt faire un AND, on en revient au problème indiqué au départ, avec ou sans parenthèses...

Je crois que sur ce type de modèle, filtrer les données est loin dêtre aussi simple qu'il n'y parait... En tout cas je n'ai pas trouvé d'autres solutions que d'executer des requêtes successives (une par condition) et de traiter les résultats dans le code client...

[CinePhil]

Reprenons la requête complète :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT *
FROM   TR_PROSPECT_PRP PRP
       LEFT OUTER JOIN T_DONNEE_DON DON
            ON PRP.PRP_ID = DON.CRE_LIGNE
WHERE  DON.TBL_ID = 44
  AND  DON.CAR_ID = 123
  AND  CRE.CRE_VALEUR = valeur 1
  AND  DON.CAR_ID = 475
  AND  CRE.CRE_VALEUR = valeur 2
  AND  DON.CAR_ID = 74
  AND  CRE.CRE_VALEUR = valeur 3
  AND  DON.CAR_ID = 7
  AND  CRE.CRE_VALEUR = valeur 4

Déjà, il y a dans cette requête une erreur d'alias puisque "CRE" n'existe pas !

Corrigeons-la :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT *
FROM   TR_PROSPECT_PRP PRP
       LEFT OUTER JOIN T_DONNEE_DON DON
            ON PRP.PRP_ID = DON.CRE_LIGNE
WHERE  DON.TBL_ID = 44
  AND  DON.CAR_ID = 123
  AND  DON.CRE_VALEUR = valeur 1
  AND  DON.CAR_ID = 475
  AND  DON.CRE_VALEUR = valeur 2
  AND  DON.CAR_ID = 74
  AND  DON.CRE_VALEUR = valeur 3
  AND  DON.CAR_ID = 7
  AND  DON.CRE_VALEUR = valeur 4

Maintenant, remplissons les tables...
TR_PROSPECT_PRP (PRP_ID, PRP_NOM, PRP_PRENOM...)
1, 'Dupont', 'Jean'
2, 'Durand', 'Jacques'
3, 'Duchmol', 'Eddy'

T_DONNEE_DON (TBL_ID, CAR_ID, CRE_LIGNE, CRE_VALEUR)
1, 12, 2, 'xyz'
44, 475, 1, 'toto'
44, 475, 2, 'valeur 2'
12, 475, 4, 'titi'
44, 123, 3, 'valeur 1'

Faisons la jointure avec la restriction sur TBL_ID :

Code :

1
2
3
4
5
SELECT *
FROM   TR_PROSPECT_PRP PRP
       LEFT OUTER JOIN T_DONNEE_DON DON
            ON PRP.PRP_ID = DON.CRE_LIGNE
WHERE  DON.TBL_ID = 44

PRP_ID, PRP_NOM, PRP_PRENOM, TBL_ID, CAR_ID, CRE_LIGNE, CRE_VALEUR
1, 'Dupont', 'Jean', 44, 475, 1, 'toto'
2, 'Durand', 'Jacques', 44, 475, 2, 'valeur 2'
3, 'Duchmol', 'Eddy', 44, 123, 3, 'valeur 1'

Voyons les autres restrictions :

Code :

1
2
  AND  DON.CAR_ID = 123
  AND  DON.CRE_VALEUR = valeur 1

=> Seule la 3ème ligne satisfait cette condition :
3, 'Duchmol', 'Eddy', 44, 123, 3, 'valeur 1'

Ajoutons la condition suivante :

Code :

1
2
  AND  DON.CAR_ID = 475
  AND  DON.CRE_VALEUR = valeur 2

=> La 3ème ligne ne satisfait pas cette condition donc elle est supprimée et la requête ne retourne aucun résultat !

Maintenant avec OR...
La requête complète :

Code :

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
SELECT *
FROM   TR_PROSPECT_PRP PRP
       LEFT OUTER JOIN T_DONNEE_DON DON
            ON PRP.PRP_ID = DON.CRE_LIGNE
WHERE  DON.TBL_ID = 44
  AND  
  (
    (
        DON.CAR_ID = 123
        AND  DON.CRE_VALEUR = valeur 1
    )
    OR  
    (
        DON.CAR_ID = 475
        AND  DON.CRE_VALEUR = valeur 2
    )
    OR  
    (
        DON.CAR_ID = 74
        AND  DON.CRE_VALEUR = valeur 3
    )
    OR  
    (
        DON.CAR_ID = 7
        AND  DON.CRE_VALEUR = valeur 4
    )
  )

Le résultat de la jointure et de la première restriction ne change pas :
PRP_ID, PRP_NOM, PRP_PRENOM, TBL_ID, CAR_ID, CRE_LIGNE, CRE_VALEUR
1, 'Dupont', 'Jean', 44, 475, 1, 'toto'
2, 'Durand', 'Jacques', 44, 475, 2, 'valeur 2'
3, 'Duchmol', 'Eddy', 44, 123, 3, 'valeur 1'

Simplifions le WHERE :

Code :

1
2
3
4
5
WHERE  DON.TBL_ID = 44
  AND  
  (
    [/*tout ce qu'il y a dans la parenthèse*/]
  )

Et comme [tout ce qu'il y a dans la parenthèse] est une succession de OR, chaque ligne satisfaisant au moins 1 OR sera conservée. Examinons les morceaux entre les OR :

Code :

1
2
3
4
(
        DON.CAR_ID = 123
        AND  DON.CRE_VALEUR = valeur 1
    )

=> La ligne 3 satisfait la condition :
3, 'Duchmol', 'Eddy', 44, 123, 3, 'valeur 1'

Code :

1
2
3
4
(
        DON.CAR_ID = 475
        AND  DON.CRE_VALEUR = valeur 2
    )

=> La ligne 2 satisfait la condition :
2, 'Durand', 'Jacques', 44, 475, 2, 'valeur 2'

Code :

1
2
3
4
(
        DON.CAR_ID = 74
        AND  DON.CRE_VALEUR = valeur 3
    )

=> Aucune ligne ne satisfait la condition.

Code :

1
2
3
4
(
        DON.CAR_ID = 7
        AND  DON.CRE_VALEUR = valeur 4
    )

=> Aucune ligne ne satisfait la condition.

Résultat final : on conserve les lignes 2 et 3 :
2, 'Durand', 'Jacques', 44, 475, 2, 'valeur 2'
3, 'Duchmol', 'Eddy', 44, 123, 3, 'valeur 1'

=> Nous avons ici les prospects qui ont au moins une des valeurs pour les caractéristiques testées

Mais peut-être que ce résultat n'est pas celui souhaité ?
Ne voulait-on pas plutôt, avec ce modèle, les prospects qui répondent à un ensemble de valeurs pour certaines caractéristiques ?

Alors il faut faire autant de jointures qu'il y a de caractéristiques à tester, ce qui peut devenir très lourd !
C'est peut-être aussi pour ça que ce modèle est peu utilisé ?

[vinzzzz]

Envoyé par CinePhil

Reprenons la requête complète :
(...)
Résultat final : on conserve les lignes 2 et 3 :
2, 'Durand', 'Jacques', 44, 475, 2, 'valeur 2'
3, 'Duchmol', 'Eddy', 44, 123, 3, 'valeur 1'

=> Nous avons ici les prospects qui ont au moins une des valeurs pour les caractéristiques testées

Mais peut-être que ce résultat n'est pas celui souhaité ?
Ne voulait-on pas plutôt, avec ce modèle, les prospects qui répondent à un ensemble de valeurs pour certaines caractéristiques ?

En effet c'est le problème ici

Envoyé par CinePhil

Alors il faut faire autant de jointures qu'il y a de caractéristiques à tester, ce qui peut devenir très lourd !

En effet, perso je fais des requêtes successives dans mon code client en récupérant les IDs et en les combinant suivant les conditions de la requête.. C'est également plutôt lourd mais je n'ai pas trouvé mieux en terme de compromis performance / complexité...

Envoyé par CinePhil

C'est peut-être aussi pour ça que ce modèle est peu utilisé ?

Je ne sais pas si il est peu utilisé... Je pense surtout qu'il est utilisé dans des cas bien précis, et notamment lorsqu'il n'y a pas trop de données, car sur le papier l'approche est fort séduisante.

Le gros probleme (dans mon cas par exemple), c'est lorsqu'il y a beaucoup d'entités, beaucoup de propriétés, et que l'on a besoin d'explorer un peu tout ca à l'aide de filtres. Sinon c'est tout a fait gérable...

Plus d'info pour les curieux, en plus de l'article de SQLpro:
http://en.wikipedia.org/wiki/Entity-...te-value_model

[aieeeuuuuu]

Bonjour

Envoyé par CinePhil

Alors il faut faire autant de jointures qu'il y a de caractéristiques à tester, ce qui peut devenir très lourd !
C'est peut-être aussi pour ça que ce modèle est peu utilisé ?

Envoyé par vinzzzz

En effet, perso je fais des requêtes successives dans mon code client en récupérant les IDs et en les combinant suivant les conditions de la requête.. C'est également plutôt lourd mais je n'ai pas trouvé mieux en terme de compromis performance / complexité...

Et en cherchant les prospects qui satisfont exactement les 4 critères de recherche ?

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
SELECT *
FROM   TR_PROSPECT_PRP PRP
INNER JOIN (
	SELECT CRE_LIGNE
	FROM T_DONNEE_DON DON
	WHERE DON.TBL_ID = 44
	AND(
		(DON.CAR_ID = 123 AND  DON.CRE_VALEUR = 'valeur 1')
		OR  
		(DON.CAR_ID = 475 AND  DON.CRE_VALEUR = 'valeur 2')
		OR 
		(DON.CAR_ID = 74 AND  DON.CRE_VALEUR = 'valeur 3')
		OR
		(DON.CAR_ID = 7 AND  DON.CRE_VALEUR = 'valeur 4')
	)
	GROUP BY CRE_LIGNE
	HAVING COUNT(*) = 4
	) DON_OK ON PRP.PRP_ID = DON_OK.CRE_LIGNE

J'ai pas testé, mais ça me semble une bonne piste (à améliorer peut etre pour les perfs ?)

[CinePhil]

Oui je pense que ta requête fonctionne et répondrait au besoin (pas testé non plus).
Et au passage, je crois qu'au lieu de la cascade OR, on peut utiliser IN sur un couple de colonnes mais je ne suis pas sûr de la syntaxe (la flemme de vérifier ) :

Code :

1
2
3
4
5
6
7
8
9
10
SELECT *
FROM   TR_PROSPECT_PRP PRP
INNER JOIN (
    SELECT CRE_LIGNE
    FROM T_DONNEE_DON DON
    WHERE DON.TBL_ID = 44
    AND (DON.CAR_ID, DON.CRE_VALEUR) IN ((123, 'valeur 1'), (475, 'valeur 2'), (74, 'valeur 3'), (7, 'valeur 4'))
    GROUP BY CRE_LIGNE
    HAVING COUNT(*) = 4
) DON_OK ON PRP.PRP_ID = DON_OK.CRE_LIGNE

[Waldar]

Envoyé par CinePhil

Et au passage, je crois qu'au lieu de la cascade OR, on peut utiliser IN sur un couple de colonnes mais je ne suis pas sûr de la syntaxe :

Code :

(DON.CAR_ID, DON.CRE_VALEUR) IN ((123, 'valeur 1'), (475, 'valeur 2'), (74, 'valeur 3'), (7, 'valeur 4'))

Oui c'est bien ça, mais très peu de SGBD supportent cette syntaxe.
À ma connaissance, il n'y a qu'Oracle et PostgreSQL.