MySQL Group_concat, JSON et Tags

août 16, 2011   //   by Nicolas Borboën   //   Blog, Programmation  //  Pas de commentaire

Devant faire une liste de mot-clés – tags – pour une série de projet je me suis penché sur les différentes méthodes et possibilités de faire un « tag cloud ». Voici ma solution…

La structure de la base de donnée

Trois tables sont nécessaires: projets, tags, et projects2tags. J’ai choisi de faire une table relationnelle pour garantir l’unicité des mots-clés et faciliter ensuite les recherches sur tels ou tels mots-clés.

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

/*Table structure for table `projects` */
CREATE TABLE `projects` (
  `project` int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT 'PK',
  `title` varchar(50) NOT NULL COMMENT 'Project title',
  PRIMARY KEY (`project`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
 
/*Data for the table `projects` */
INSERT INTO `projects`(`project`,`title`) VALUES (1,'My first project'),(2,'My second project'),(3,'My third project'),(4,'My fourth project');
 
/*Table structure for table `projects2tags` */
CREATE TABLE `projects2tags` (
  `project_id` int(8) unsigned NOT NULL,
  `tag_id` int(8) unsigned NOT NULL,
  PRIMARY KEY (`project_id`,`tag_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
/*Data for the table `projects2tags` */
INSERT INTO `projects2tags`(`project_id`,`tag_id`) VALUES (1,1),(1,5),(2,6),(3,1),(3,2),(3,3),(3,4),(3,5),(4,7),(4,8),(4,9);
 
/*Table structure for table `tags` */
CREATE TABLE `tags` (
  `tag` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`tag`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
 
/*Data for the table `tags` */
INSERT INTO `tags`(`tag`,`name`) VALUES (1,'tag1'),(2,'tag2'),(3,'tag3'),(4,'tag4'),(5,'tag5'),(6,'tag6'),(7,'tag7'),(8,'tag8'),(9,'tag9');

Ces requêtes vous permettent de créer les 3 tables avec des données de tests.

Comment faire en sorte d’avoir des résultats facile a traiter, si possible sans post-traitement ?

La vue

Avec une vue on peut faciliter les futures requêtes, et déjà mettre en forme les résultats de manière pratique. La commande MySQL GROUP_CONCAT permet de de faire un « implode » des résultats pour fournir une liste. J’ai choisi de faire deux versions de cette liste, une au format JSON – destinée au traitement des données en PHP – et l’autre au format texte afin de faciliter la recherche des projets en fonction de leurs tags. Voici à quoi elle ressemble:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

/*View structure for view v_projects */
CREATE OR REPLACE VIEW `Tagging`.`v_projects` 
	AS (SELECT `p`.`project`, `p`.`title`, 
	CONCAT("{",(SELECT GROUP_CONCAT("\"",`t`.`name`,"\"",":",`t`.`tag` ORDER BY `t`.`name` ASC)
		FROM `projects` `p2`
			LEFT JOIN `projects2tags` `p2t` ON (`p2`.`project` = `p2t`.`project_id`)
			LEFT JOIN `tags` `t` ON (`t`.`tag` = `p2t`.`tag_id`)
		WHERE `p2`.`project` = `p`.`project`),"}") AS `json_tags`,
	CONCAT(",",(SELECT GROUP_CONCAT(`t`.`name` ORDER BY `t`.`name` ASC)
		FROM `projects` `p2`
			LEFT JOIN `projects2tags` `p2t` ON (`p2`.`project` = `p2t`.`project_id`)
			LEFT JOIN `tags` `t` ON (`t`.`tag` = `p2t`.`tag_id`)
		WHERE `p2`.`project` = `p`.`project`),",") AS `lookup_tags`
	FROM `projects` `p`);

Cette vue « v_projects » est utilisable comme un table habituelle mais ses données sont dynamiques et il est impossible modifier les datas la composant sans passer par les vraies tables.

En pratique

Une requête comme SELECT * from v_projects; sort les résultats suivant:

1
2
3
4

1	My first project	{"tag1":1,"tag5":5}	,tag1,tag5,
2	My second project	{"tag6":6}	,tag6,
3	My third project	{"tag1":1,"tag2":2,"tag3":3,"tag4":4,"tag5":5}	,tag1,tag2,tag3,tag4,tag5,
4	My fourth project	{"tag7":7,"tag8":8,"tag9":9}	,tag7,tag8,tag9,

Les résultats de la colonne json_tags sont facilement exploitables en PHP avec la commande JSON_DECODE() qui les transformera en tableau PHP.

Les résultats de la colonne lookup_tags sont destinés à la recherche en MySQL, soit avec une requête comme SELECT project, title FROM v_projects WHERE lookup_tags LIKE « %,tag1,% »;. Vous noterez que le champs a des virgules avant et après, ce qui facilite la recherche « LIKE » sur tout le champs en évitant les erreurs de mots raccourcis. La recherche sur tag1 ne retournera pas tag13, tag118, …

Littérature

A vous de jouer !

Les sources sont disponibles ici: SQL Tagging with GROUP_CONCAT and JSON. Et vos commentaires sur cette application sont attendus ci-dessous! Merci!

crédit photo: http://www.uni-koblenz.de/~staab/

Mots-clefs :, , , , ,

Votre commentaire

Image CAPTCHA
*