Latest snippets tagged mysql join tags sqlhttp://snippets.prendreuncafe.com/snippets/tagged/mysql+join+tags+sql/order_by/date2008-05-15T22:06:43ZSymfonynoreply@symfony-project.comGestion des tags en SQL2008-05-15T22:06:43Z93Un schema classique de gestion de tags :
[code=sql]
CREATE TABLE Project (
project_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
url TEXT NOT NULL,
PRIMARY KEY (project_id)
) ENGINE=MyISAM;
CREATE TABLE Tags (
tag_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
tag_text VARCHAR(50) NOT NULL,
PRIMARY KEY (tag_id),
INDEX (tag_text)
) ENGINE=MyISAM;
CREATE TABLE Tag2Project (
tag INT UNSIGNED NOT NULL,
project INT UNSIGNED NOT NULL,
PRIMARY KEY (tag, project),
INDEX rv_primary (project, tag)
) ENGINE=MyISAM;
[/code]
Pour récupérer tous les projets taggué `php` **ou** `mysql` :
[code=sql]
SELECT p.name
FROM Project p
INNER JOIN Tag2Project t2p
ON p.project_id = t2p.project
INNER JOIN Tag t
ON t2p.tag = t.tag_id
WHERE t.tag_text IN ('mysql', 'php');
[/code]
Pour récupérer tous les projets taggué `php` **et** `mysql` :
[code=sql]
SELECT p.name
FROM Project p
CROSS JOIN Tag t1
CROSS JOIN Tag t2
INNER JOIN Tag2Project t2p
ON p.project_id = t2p.project
AND t2p.tag = t1.tag_id
INNER JOIN Tag2Project t2p2
ON t2p.project = t2p2.project
AND t2p2.tag = t2.tag_id
WHERE t1.tag_text = "php"
AND t2.tag_text = "mysql";
[/code]
Tiré et adapté de la présentation [Join-fu de Jay Pipes](http://jpipes.com/presentations/joinfu/joinfu.pdf)