/* ************************************************************************** */
/*INFORMATION*/
/* ************************************************************************** */
# Author: Nicolas Borboën
# Author email: nbo@donax.ch
# Author website: http://donax.ch
# Post link: http://blog.donax.ch/2011/08/16/mysql-group_concat-et-tags/
# 
# MySQl Group_Concat: http://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_group-concat
# Imploding MySQL vs PHP: http://stackoverflow.com/questions/5957389/imploding-in-php-vs-imploding-in-mysql-which-uses-less-cpu
# MySQL TagSchema: http://forge.mysql.com/wiki/TagSchema
# Performance: http://www.pui.ch/phred/archives/2005/06/tagsystems-performance-tests.html
# Méthodes: http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html
# Fulltext: http://www.pui.ch/phred/archives/2005/05/tags-with-mysql-fulltext.html
# Général/Design: http://www.smashingmagazine.com/2007/11/07/tag-clouds-gallery-examples-and-good-practices/ 
# PHP Json Encode: http://php.net/manual/en/function.json-encode.php

/* ************************************************************************** */
/*Structure and Data creation*/
/* ************************************************************************** */
/*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');

/*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`);

/* ************************************************************************** */
/*SELECT*/
/* ************************************************************************** */
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` ;

SELECT * FROM v_projects;
SELECT project, title FROM v_projects WHERE lookup_tags LIKE "%,tag1,%";