left join ambigu

[HadanMarv]

Je reprends actuellement une application en main. Un existant relativement conséquent. J'ai pas mal d'exemple de requête relativement complexe j'en prend une au hasard car je n'arrive pas à comprendre son fonctionnement.
Vous pourrez constater que les deux lefts joins avec sous requête ont une clause on sur un champ n'existant pas.
c'est surement logique mais là je l'ai pas.

Voici les ddl :

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
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
CREATE TABLE objets
(
  obj_id integer NOT NULL DEFAULT 0, -- Identifiant unique du objet
  obj_module character varying(15) NOT NULL DEFAULT '0'::character varying,
  obj_nom character varying(30) NOT NULL DEFAULT '0'::character varying,
  obj_type smallint,
  obj_actiondisp smallint,
  CONSTRAINT objets_pkey PRIMARY KEY (obj_id),
  CONSTRAINT obj_module_nom_u UNIQUE (obj_module, obj_nom)
)
 
CREATE TABLE objets_actions
(
  oba_id integer NOT NULL DEFAULT 0, -- Identifiant unique du table
  oba_seq character varying(1) NOT NULL DEFAULT '0'::character varying,
  oba_idobj integer NOT NULL DEFAULT 0, -- Id objet (Clef etrangere OBJETS.OBJ_ID)
  oba_droitsdisp smallint,
  oba_emplacement smallint,
  CONSTRAINT objets_actions_pkey PRIMARY KEY (oba_id),
  CONSTRAINT oba_obj FOREIGN KEY (oba_idobj)
      REFERENCES objets (obj_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT oba_seq_idobj_u UNIQUE (oba_seq, oba_idobj)
)
 
CREATE TABLE profils
(
  pro_id integer NOT NULL DEFAULT 0, -- Identifiant unique de la profil
  pro_nom character varying(40) NOT NULL DEFAULT NULL::character varying,
  pro_comment character varying(255) DEFAULT NULL::character varying,
  CONSTRAINT profils_pkey PRIMARY KEY (pro_id),
  CONSTRAINT pro_idact_nom_u UNIQUE (pro_nom)
)
 
CREATE TABLE utilisateurs
(
  usr_id integer NOT NULL DEFAULT 0, -- Identifiant unique de la utilisateur
  usr_idpro integer, -- Id profil (Clef etrangere PROFILS.PRO_ID)
  usr_nomcompte character varying(20) NOT NULL DEFAULT ''::character varying,
  usr_pass character varying(32) DEFAULT NULL::character varying,
  usr_nom character varying(40) DEFAULT NULL::character varying,
  usr_prenom character varying(40) DEFAULT NULL::character varying,
  usr_email character varying(60) DEFAULT NULL::character varying,
  CONSTRAINT utilisateurs_pkey PRIMARY KEY (usr_id),
  CONSTRAINT usr_pro FOREIGN KEY (usr_idpro)
      REFERENCES profils (pro_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT usr_nomcompte_u UNIQUE (usr_nomcompte)
)
 
CREATE TABLE droits
(
  dro_idpro integer NOT NULL DEFAULT 0, -- Id profil (Clef etrangere PROFILS.PRO_ID)
  dro_idoba integer NOT NULL DEFAULT 0, -- Id object action (Clef etrangere OBJETS_ACTIONS.OBA_ID)
  dro_visible smallint,
  dro_droits smallint,
  dro_popup smallint,
  CONSTRAINT droits_pkey PRIMARY KEY (dro_idpro, dro_idoba),
  CONSTRAINT dro_oba FOREIGN KEY (dro_idoba)
      REFERENCES objets_actions (oba_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT dro_pro FOREIGN KEY (dro_idpro)
      REFERENCES profils (pro_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

et la requê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
SELECT OBJ_ID, OBA_SEQ, OBJ_MODULE, OBJ_NOM, OBA_DROITSDISP, COALESCE(T1.LENGTH,0) AS LENGTH, COALESCE(DRO_VISIBLE,1) AS DRO_VISIBLE, 
	CASE WHEN OBJ_TYPE IN (2,3) THEN COALESCE(DRO_DROITS,2) ELSE COALESCE(DRO_DROITS,0) END AS DRO_DROITS, 
	CASE WHEN OBJ_TYPE IN (2,3) THEN COALESCE(DRO_POPUP,2) ELSE COALESCE(DRO_POPUP,0) END AS DRO_POPUP 
FROM OBJETS 
LEFT JOIN OBJETS_ACTIONS ON OBA_IDOBJ = OBJ_ID 
LEFT JOIN (
	SELECT 
		PRO_ID 
	FROM PROFILS 
	LEFT JOIN UTILISATEURS ON USR_IDPRO=PRO_ID 
	WHERE PRO_ISUPP = 0 AND USR_ISUPP=0 AND PRO_ID > 0 
	GROUP BY PRO_ID
) AS T ON OBA_DROITSDISP = 3 
LEFT JOIN (
	SELECT 
		COUNT(DISTINCT PRO_ID) AS LENGTH 
	FROM PROFILS 
	LEFT JOIN UTILISATEURS ON USR_IDPRO=PRO_ID 
	WHERE PRO_ISUPP = 0 AND USR_ISUPP=0 AND PRO_ID > 0 
) AS T1 ON OBA_DROITSDISP = 3 
LEFT JOIN DROITS ON DRO_IDOBA = OBA_ID AND DRO_IDPRO = T.PRO_ID 
WHERE OBJ_TYPE IN (2,3,4,6,7,9,11) AND OBJ_ACTIONDISP > 0 AND OBA_DROITSDISP > 0 
ORDER BY OBJ_ID ASC, OBA_SEQ ASC, T.PRO_ID ASC, LENGTH ASC

Merci d'avance de vos lumières.

[Yanika_bzh]

OBA_DROITSDISP existe dans votre table objets_actions

A moins que cela ne soit pas la colonne dont vous parliez

[HadanMarv]

oui tout à fait d'accord avec vous, cependant elle n'existe pas dans la subquery du left join, donc je comprends pas comment çà marche.
C'est comme si on joignait des tables avec 1=1 ?

[al1_24]

Ça ressemble à une jointure conditionnelle.
Si OBA_DROITSDISP vaut 3, on effectue un produit cartésien avec les tables dérivées T et T3 et on prend en compte les valeurs qu'elles retournent, sinon ces données ne sont pas prises en compte dans le résultat, mais on retourne bien les lignes résultant du reste de la requête, avec les colonnes provenant de T et T3 à NULL.

Un peu tordu, mais efficace.

[HadanMarv]

Désolé mais là je suis complètement largué.
comment la jointure pourrait se faire sachant qu'aucun champ de la sous-requête portant pourtant sur deux tables ne peut se rapprocher de la condition 'on' ?

[Yanika_bzh]

je rejoins l'analyse de al1_24

Le ON entraine un produit cartésien mais renvoie des valeurs non nulles que si la condition est atteinte

[HadanMarv]

ok maintenant je comprends mieux, désolé c'est la fin de journée.
Pour le coup effectivement c'est tordu.
Y aurait-il selon vous un moyen de faire plus propre ?

[al1_24]

Plus lisible certainement, en qualifiant toutes les colonnes avec l'alias de la table correspondante, et en ajoutant un commentaire sur ces "jointures" pour le prochain qui reprend la requête ne se casse pas la tête à comprendre.

Plus efficace, je ne pense pas.

[CinePhil]

Envoyé par al1_24

Plus lisible certainement, en qualifiant toutes les colonnes avec l'alias de la table correspondante

La voici :

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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
SELECT o.OBJ_ID, oa.OBA_SEQ, o.OBJ_MODULE, o.OBJ_NOM, oa.OBA_DROITSDISP, 
    COALESCE(T1.LENGTH,0) AS LENGTH, 
    COALESCE(d.DRO_VISIBLE,1) AS DRO_VISIBLE, 
    CASE 
        WHEN o.OBJ_TYPE IN (2,3) THEN COALESCE(d.DRO_DROITS,2) 
        ELSE COALESCE(d.DRO_DROITS,0) 
    END AS DRO_DROITS, 
    CASE 
        WHEN o.OBJ_TYPE IN (2,3) THEN COALESCE(d.DRO_POPUP,2) 
        ELSE COALESCE(d.DRO_POPUP,0) 
    END AS DRO_POPUP 
FROM OBJETS o 
LEFT JOIN OBJETS_ACTIONS oa ON oa.OBA_IDOBJ = o.OBJ_ID 
 
    LEFT JOIN 
    (
        SELECT p1.PRO_ID 
        FROM PROFILS p1
        LEFT JOIN UTILISATEURS u1 ON u1.USR_IDPRO = p1.PRO_ID 
        WHERE p1.PRO_ISUPP = 0 
            AND u1.USR_ISUPP = 0 
            AND p1.PRO_ID > 0 
        GROUP BY p1.PRO_ID
    ) AS T ON oa.OBA_DROITSDISP = 3 
 
    LEFT JOIN 
    (
        SELECT COUNT(DISTINCT p2.PRO_ID) AS LENGTH 
        FROM PROFILS p2
        LEFT JOIN UTILISATEURS u2 ON u2.USR_IDPRO = p2.PRO_ID 
        WHERE p2.PRO_ISUPP = 0 
            AND u2.USR_ISUPP = 0 
            AND p2.PRO_ID > 0 
    ) AS T1 ON oa.OBA_DROITSDISP = 3 
 
    LEFT JOIN DROITS d 
        ON d.DRO_IDOBA = oa.OBA_ID 
        AND d.DRO_IDPRO = T.PRO_ID 
WHERE o.OBJ_TYPE IN (2,3,4,6,7,9,11) 
    AND o.OBJ_ACTIONDISP > 0 
    AND oa.OBA_DROITSDISP > 0 
ORDER BY o.OBJ_ID ASC, o.OBA_SEQ ASC, T.PRO_ID ASC, T1.LENGTH ASC

Il y a quand même des choses bizarres dans cette requête !
1) Une jointure avec sa condition sur deux tables !

Code :

1
2
3
LEFT JOIN DROITS d 
        ON d.DRO_IDOBA = oa.OBA_ID 
        AND d.DRO_IDPRO = T.PRO_ID

2) Appeler LENGTH (longueur) le comptage des identifiants de profils me laisse dubitatif sur la sémantique du bidule !

Code :

SELECT COUNT(DISTINCT p2.PRO_ID) AS LENGTH

3) Un GROUP BY sans fonction de regroupement :

Code :

1
2
3
SELECT p1.PRO_ID
-- ...
GROUP BY p1.PRO_ID

=> Il vaut mieux un DISTINCT

4) Les jointures conditionnelles vont retourner, chaque fois que OBJETS_ACTIONS.OBA_DROITSDISP = 3, la liste des identifiants de profil et le nombre de ces profils répondant aux conditions des sous-requêtes.
Je ne suis pas loin de penser que cette partie est là pour de la présentation de données et alourdit inutilement la requête.

Si tu peux nous dire ce qu'est censée retourner cette requête, on pourra t'aider à l'améliorer.

[HadanMarv]

Merci pour ces nouveaux éléments.
Pour ma part je continu à avancer sur le problème car j'avoue que cette histoire de jointure conditionnelle me rend un peu dingue.

Pour la jointure avec la condition sur deux tables, c'est en fait parce que la table droits à pour clé primaire la combinaison des deux tables en condition de jointure.

Pour le length, j'ai l'impression que c'est pour masquer le cas où la jointure conditionnelle n'est pas rempli et donc éviter de ramener un hypothétique champ à blanc.

Pour le group by en lieu et place du distinct je suis complétement d'accord.

Sinon après regroupement d'informations en tout genre, ainsi qu'une analyse des informations des tables voici ce que je déduis de l'utilité de la requête.

1)Les écrans de l'application sont composés d'objets.
2)Chacun des objets à des actions (Lecture, Création, Modification, Suppression) stockés dans objets_actions.
3)Chacun des utilisateurs de l'application à un profil
4)Les droits font le rapprochement entre le profil et l'objets_actions

Donc la requête exécuté au démarrage de l'application doit servir à remonter la liste de tous les objets et actions liées ainsi que la liste de tous les droits par profils.
Qu'en pensez vous ?

Sinon j'ai découpé le traitement en trois requêtes une première récupéré tous les profils, une seconde tous les objets et objets_actions.
Je parcours les deux listes pour récupérer les droits par objets_actions et profils.

Merci d'avance de votre aide.

[CinePhil]

Envoyé par HadanMarv

M
Sinon après regroupement d'informations en tout genre, ainsi qu'une analyse des informations des tables voici ce que je déduis de l'utilité de la requête.

1)Les écrans de l'application sont composés d'objets.
2)Chacun des objets à des actions (Lecture, Création, Modification, Suppression) stockés dans objets_actions.
3)Chacun des utilisateurs de l'application à un profil
4)Les droits font le rapprochement entre le profil et l'objets_actions

Donc la requête exécuté au démarrage de l'application doit servir à remonter la liste de tous les objets et actions liées ainsi que la liste de tous les droits par profils.
Qu'en pensez vous ?

J'allais dire que s'il s'agit de ramener tous les objets, toutes les actions et tous les droits, quel que soit l'utilisateur, autant le faire à l'aide d'un fichier XML car ce sont des données statiques ! En plus, ça encombre la mémoire de la machine. Bref, un truc de développeur qui n'a pas conscience de la richesse de l'utilisation d'une BDD. Je ne suis pas loin de penser qu'il y a d'autres requêtes qui ramènent des informations inutilisée !

Mais quand je lis :

Sinon j'ai découpé le traitement en trois requêtes une première récupéré tous les profils, une seconde tous les objets et objets_actions.
Je parcours les deux listes pour récupérer les droits par objets_actions et profils.

Ça a tendance a confirmer mes soupçons ! Pourquoi ne pas faire la requête adéquate qui va chercher les droits de l'utilisateur connecté au lieu de ramener tout ?

[HadanMarv]

J'allais dire que s'il s'agit de ramener tous les objets, toutes les actions et tous les droits, quel que soit l'utilisateur, autant le faire à l'aide d'un fichier XML car ce sont des données statiques ! En plus, ça encombre la mémoire de la machine.

je le pense aussi

Bref, un truc de développeur qui n'a pas conscience de la richesse de l'utilisation d'une BDD. Je ne suis pas loin de penser qu'il y a d'autres requêtes qui ramènent des informations inutilisée !

Je ne comprends pas trop ce que vous entendez par là, s'agit-il de mon point de vue, ou bien de l'approche qui a été faite ?

Ça a tendance a confirmer mes soupçons ! Pourquoi ne pas faire la requête adéquate qui va chercher les droits de l'utilisateur connecté au lieu de ramener tout ?

Je ne comprends pas non plus l'intérêt de tout charger au démarrage de l'application, surtout que là nous ne voyons que la partie bdd, il faut voir côté java le boulot pour traiter cela, c'est assez conséquent là aussi et un peu fouillis.

[CinePhil]

Envoyé par HadanMarv

Je ne comprends pas trop ce que vous entendez par là, s'agit-il de mon point de vue, ou bien de l'approche qui a été faite ?

De l'approche qui a été faite, mais je trouvais que ta modification n'allait pas assez loin car, si j'ai bien compris, tu continues de tout charger mais en plusieurs requêtes simples au lieu d'une compliquée. Une requête, à mon avis pas très compliquée, permettrait de ne ramener que les droits de l'utilisateur au lieu de l'ensemble et se serait sûrement beaucoup pus simple à gérer par l'application.

[HadanMarv]

Ma modification était plus là pour m'aider à la compréhension de la requête.

En fait, je pense que l'écriture de la requête est là pour retourner forcément un résultat même si aucun droits n'est défini pour l'objet et son action (COALESCE).
En fait c'est plus un principe de droit par défaut.

J'avoue avoir encore du mal à comprendre le fondement.
mais au moins j'ai la "logique" de la requête.

Maintenant je rejoint le principe de dire qu'il serait préférable de stocker les informations liés à un utilisateur et non pas à tous les utilisateurs possibles.

Côté applicatif, il est vrai que le traitement de ces informations entraînent une charge conséquente côté serveur multiple hashmap imbriqué.