LEFT OUTER JOIN en DQL

L'utilisation de jointure dans les requête est très fréquente, afin de remonter des informations depuis divers objets. Malheureusement une "simple" jointure, en ajoutant les objets dans la clause FROM, nécessite qu'il y ait au moins une correspondance pour remonter les informations.

Cet article présente l'utilisation de la fonction LEFT OUTER JOIN, bien connu du SQL et disponible depuis la version 6.6 du content server, qui va permettre de manipuler les données de façon assez souple. A noter que ce type de jointure est massivement utilisé dans les requêtes générées avec xCP, notamment pour la récupération des variables de process.

Votre avis

Nobody voted on this yet  You need to enable JavaScript to vote

Tweet

Enoncé

Les deux souhaits suivants seront traités.

• Récupérer la liste des utilisateurs et le nom du premier document leurs appartenant;
• Récupérer la liste des utilisateurs et le nombre de documents leurs appartenant.

Liste des documents

Afin de récupérer l'ensemble des documents d'un utilisateur, le premier réflexe serait d'écrire la requête suivante.

SELECT dm_user.r_object_id, dm_user.user_name, dm_document.object_name
FROM dm_user, dm_document
WHERE dm_user.user_name = dm_document.owner_name
AND dm_user.r_is_group = false
ORDER by dm_user.user_name, dm_document.object_name

Cependant, cette requête présente deux inconvénients.

• Un utilisateur doit avoir au moins un document dont il est le propriétaire;
• La requête remonte l'ensemble des documents appartenant aux utilisateurs.

Afin de remédier au premier problème, l'utilisateur de LEFT OUTER JOIN va permettre de remonter tous les utilisateurs même si aucun document ne leur appartient. La requête devient donc celle-ci.

SELECT dm_user.r_object_id, dm_user.user_name, doc.object_name
FROM dm_user
LEFT OUTER JOIN (
	SELECT dm_document.r_object_id, dm_document.owner_name, dm_document.object_name
	FROM dm_document
) doc
ON doc.owner_name = dm_user.user_name
WHERE dm_user.r_is_group = false
ORDER BY dm_user.user_name, doc.object_name

Les points d'attention pour cette utilisation sont les suivants.

• Utilisation d'un alias pour la sous requête du LEFT OUTER JOIN, ici doc;
• L'alias doit être utilisé pour préfixer les attributs dans la clause SELECT principale;
• L'attribut utilisé dans le ON doit figuré dans la clause SELECT de la sous requête;
• Les attributs remontés dans la clause SELECT principale, doivent être remontés dans la sous requête.

Cependant, cette requête remonte l'ensemble des documents appartenant aux utilisateurs. L'objectif est de ne remonter que le premier document, si il existe. Pour cette démonstration, la notion de premier sera basé sur la date de création. L'astuce va consister à utiliser un regroupement dans la sous requête qui devient celle-ci, ainsi la clause LEFT OUTER JOIN ne remontera qu'une seule ligne en corrélation avec la requête principale.

SELECT dm_user.r_object_id, dm_user.user_name, doc.object_name
FROM dm_user
LEFT OUTER JOIN (
	SELECT min(dm_document.r_creation_date), min(dm_document.object_name) as object_name, dm_document.owner_name
	FROM dm_document
	GROUP BY dm_document.owner_name
) doc
ON doc.owner_name = dm_user.user_name
WHERE dm_user.r_is_group = false
ORDER BY dm_user.user_name, doc.object_name

Dans cette nouvelle écriture, la fonction min a été introduite sur l'attribut r_creation_date. Cependant, elle doit aussi être appliquée sur l'attribut object_name, sinon il faudrait l'ajouter dans la clause GROUP BY. Cette dernière permet de regrouper les résultats de la sous requête en fonction de l'attribut owner_name. Ainsi, elle remonte une seule ligne par utilisateur avec la valeur minimal de r_creation_date et de object_name.

Ces résultats sont ensuite reliés à la requête principale et ainsi nous avons pour chacun des utilisateurs, le nom du premier document créé lui appartenant, et vide si aucun document.

Décompte des documents

Bien souvent, il est demandé d'avoir un listing du nombre de documents par utilisateur. Le problème concerne ceux qui n'ont aucun document, et il faut jouer d'astuce pour les avoir dans les résultats de la requête, par exemple en utilisant une UNION de ce type.

SELECT count(*), owner_name
FROM dm_document
GROUP BY owner_name
UNION
SELECT 0, user_name
FROM dm_user
WHERE NOT EXISTS (
	SELECT 1
	FROM dm_document
	WHERE dm_document.owner_name = dm_user.user_name
) AND
dm_user.r_is_group = false
ORDER BY 1, 2

En effet, cette astuce permet de remonter 0 pour les cas où les utilisateurs ne possèdent aucun document.

Mais il est possible de le faire sans cette utilisation de deux requêtes, en appliquant les mêmes principes que dans le paragraphe précédent. Un décompte est réalisé dans une sous requête en fonction de l'attribut owner_name, et est remonté dans la requête principale. Ceci qui donne l'écriture suivante.

SELECT dm_user.r_object_id, dm_user.user_name, doc.nb
FROM dm_user
LEFT OUTER JOIN (
	SELECT count(dm_document.r_creation_date) as nb, dm_document.owner_name
	FROM dm_document
	GROUP BY dm_document.owner_name
) doc
ON doc.owner_name = dm_user.user_name
ORDER BY doc.nb, dm_user.user_name

A noter que le tri dans ce cas n'est absolument pas crédible. Lors d'un test pour cet article, les résultats obtenus ont été du style suivant.

11a447c280000531  User1                     1
11a447c280000144  dmc_wdk_preferences_owner 9
11a447c280000143  dmc_wdk_presets_owner     43
11a447c28000052c  User2                     10
11a447c280000102  dmadmin                   65
11a447c280000530  User3                     0