Blog
Utilisation du GROUP_CONCAT en SQL

Partager

Présentation

Si GROUP_CONCAT ne vous est pas familier, imaginez une liste d'utilisateurs possédant chacun un ou plusieurs droits. Il est assez facile de récupérer une liste d'utilisateurs avec un rôle par utilisateur, de récupérer une liste de rôles pour un utilisateur, ou même de récupérer une liste de pairs utilisateurs/droits :

  `ID`  |  `name`   |  `role`

   01   |  "user1"  |  "role1"
   01   |  "user1"  |  "role2"
   02   |  "user2"  |  "role1"
   02   |  "user2"  |  "role3"
   02   |  "user2"  |  "role7"
   ...

Ce sont juste des jointures ou des WHERE à utiliser sur votre requête, c'est assez basique. Mais si maintenant vous souhaitez récupérer une liste d'utilisateurs (une ligne par utilisateur), tout en récupérant la liste de ses droits ? Il est aussi possible de faire une requête principale pour obtenir les utilisateurs, puis de faire une autre requête pour chaque résultat afin d'obtenir ses droits. Cependant, cela nécessite de faire plusieurs requêtes. Dans l'intérêt de déporter la charge sur les serveurs SQL, et de simplifier les traitements PHP, il est possible d'utiliser la fonction GROUP_CONCAT en SQL.

Fonctionnement

La méthode est donc de faire une jointure sur les droits pour chaque utilisateur, puis de grouper par utilisateur (par son ID par exemple). Il suffit ensuite d'ajouter un champ role ayant comme valeur GROUP_CONCAT(`role`.`name`). Cette commande aura pour effet de regrouper tous les noms des droits à la suite, séparés par des virgules :

  `ID`  |  `name`   |  `...`  |  `role`

   01   |  "user1"  |   ...   |  "read,write,admin"
   02   |  "user2"  |   ...   |  "read,write"
   03   |  "user3"  |   ...   |  "read,write"
   ...

Pour définir un autre séparateur :

GROUP_CONCAT(`role`.`name` SEPARATOR '\n')

Utilisation avancée

Il peut également arriver que vous souhaitiez récupérer plusieurs informations sur les sous-requêtes. Récupérer pour l'exemple ci-dessus l'ID, le nom, et l'explication de chaque droit pourrait être utile. Il est alors possible d'utiliser un CONCAT_WS, qui permet de concaténer plusieurs informations à la suite. Ainsi :

CONCAT_WS("+", `role`.`id`, `role`.`name`, `role`.`explain`)

Donnera un résultat du type :

"2+read+Read data"

Et on obtiendra donc :

GROUP_CONCAT(CONCAT_WS("+", `role`.`id`, `role`.`name`, `role`.`explain`) SEPARATOR ',')
  `ID`  |  `name`   |  `...`  |  `role`

   01   |  "user1"  |   ...   |  "2+read+Read data,3+write+Write data,1+admin+Admin roles"
   02   |  "user2"  |   ...   |  "2+read+Read data,3+write+Write data"
   03   |  "user3"  |   ...   |  "2+read+Read data,3+write+Write data"
   ...

Récupération des données

À la récupération des données, il suffira alors de découper la chaîne par les ",", puis découper chaque segment par les "+". Exemple de fonction qui permet de convertir ces résultats en tableau en PHP :

<?php

function unpack_group_concat($result, $columns, $nullable = false, $sep_rows = ",", $sep_columns = "+") {
	if (!$result)
		return array();
	$rows = explode($sep_rows, trim($result, $sep_rows));
	$objs = array();
	foreach ($rows as $i => $row) {
		$cells = explode($sep_columns, $row);
		$o = new stdClass;
		foreach ($columns as $i => $column)
		{
			if (!$nullable)
				$o->$column = $cells[$i] !== '' ? $cells[$i] : null;
			else if ($cells[$i] === '0')
				$o->$column = $cells[$i];
			else
				$o->$column = $cells[$i]? $cells[$i] : null;
		}
		$objs[] = $o;
	}
	return $objs;
}

// Exemple d'utilisation :
unpack_group_concat($res->roles, array("id", "name", "title"));

?>

Remarque

Le résultat des GROUP_CONCAT est limité à 1024 bits par défaut. Au-delà, la chaîne est tronquée. Il est cependant possible de changer cette limite via la requête SQL :

SET SESSION group_concat_max_len = 100000
comments powered by Disqus