Foreign key registration query with multiple occurrences

Question

I am developing a system for storing documents for everyday use with PHP and mysql and for easy queries I added tags to these documentations and to ensure the integrity of the data I added another table called documentacao_tag. But I’m having trouble doing a query ,where the documentation contains exactly all related tags. Ex: check all documentation that has exactly the linux tag AND server tag.

I am looking for a solution to guarantee the integrity of the data, being able to change the modeling of the tables

Modeling of the database: SQL file for testing:

CREATE DATABASE  teste;

USE teste;

CREATE TABLE IF NOT EXISTS `documentacao` (
  `doc_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `doc_titulo` varchar(100) NOT NULL,
  `doc_texto` text NOT NULL,
  PRIMARY KEY (`doc_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=53 ;


INSERT INTO `documentacao` (`doc_id`, `doc_titulo`, `doc_texto`) VALUES
(1, 'Título 1', 'texto da notícia 1'),
(2, 'Título 2', 'texto da notícia 2 '),
(3, 'Título 3', 'texto da notícia 3');



CREATE TABLE IF NOT EXISTS `documentacao_tag` (
  `doc_id` int(11) unsigned NOT NULL,
  `tag_id` tinyint(4) unsigned NOT NULL,
  PRIMARY KEY (`doc_id`,`tag_id`),
  KEY `FK_documentacao_tag_tag` (`tag_id`),
  KEY `doc_id` (`doc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `documentacao_tag` (`doc_id`, `tag_id`) VALUES
(1, 1),
(3, 1),
(1, 2),
(2, 2),
(3, 2),
(3, 3);


CREATE TABLE IF NOT EXISTS `tag` (
  `tag_id` tinyint(4) unsigned NOT NULL AUTO_INCREMENT,
  `tag_desc` varchar(30) NOT NULL,
  PRIMARY KEY (`tag_id`),
  UNIQUE KEY `tag_desc` (`tag_desc`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=90 ;


INSERT INTO `tag` (`tag_id`, `tag_desc`) VALUES
(1, 'linux'),
(3, 'manual'),
(2, 'servidor');


ALTER TABLE `documentacao_tag`
  ADD CONSTRAINT `FK_documentacao_tag_documentacao` FOREIGN KEY (`doc_id`) REFERENCES `documentacao` (`doc_id`),
  ADD CONSTRAINT `FK_documentacao_tag_tag` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`tag_id`);

Output example by selecting documentation with linux tags (tag_id = 1) and server (tag_id = 3):

+----+--------------------+---------------------+
| 1 | Título da notícia 1 | texto da notícia  1 | 
+----+--------------------+---------------------+
| 3 | Título da notícia 3 | texto da notícia  3 | 
+----+--------------------+---------------------+

I got a solution, but I do not believe it is the most ideal, and it depends on the interaction with PHP to concatenate SQL

Example by selecting tag_id 1 and 2:

SELECT 
    documentacao.doc_id,
    documentacao.doc_titulo,
    documentacao.doc_texto
FROM 
    documentacao, 
    documentacao_tag, 
    tag
WHERE documentacao.doc_id = documentacao_tag.doc_id
AND documentacao_tag.tag_id = tag.tag_id
AND documentacao_tag.tag_id IN (1)
AND documentacao_tag.doc_id IN (

    SELECT 
        documentacao.doc_id
    FROM 
        documentacao, 
        documentacao_tag, 
        tag
    WHERE documentacao.doc_id = documentacao_tag.doc_id
    AND documentacao_tag.tag_id = tag.tag_id
    AND documentacao_tag.tag_id IN (2)
    ORDER BY documentacao.doc_id
)
ORDER BY documentacao.doc_id

Note: I have to concatenate dynamically with PHP the line below for each tag added to the filter

AND documentacao_tag.doc_id IN (

    SELECT 
        documentacao.doc_id
    FROM 
        documentacao, 
        documentacao_tag, 
        tag
    WHERE documentacao.doc_id = documentacao_tag.doc_id
    AND documentacao_tag.tag_id = tag.tag_id
    AND documentacao_tag.tag_id IN (id_da_tag_a_ser_filtrada)
    ORDER BY documentacao.doc_id
)

Answer 1

I’m not sure I quite understand your problem due to the complexity of query that you posted, but fetch posts where all the tags researched should appear do something similar to the following:

SELECT doc.doc_id, doc.doc_titulo, doc.doc_texto 
FROM documentacao AS doc
   INNER JOIN (
     SELECT count(tag_id) as qtd, doc_id 
     FROM documentacao_tag 
     WHERE tag_id IN (1, 2) 
     GROUP BY doc_id
   ) AS dt ON dt.doc_id = doc.doc_id AND dt.qtd = 2;

The search makes a Join with a sub-query that fetches all the results of the post (dt.doc_id = doc.doc) which contains all the tags of research (dt.qtd = 2 where 2 is the total of tags researched and tag_id IN (1, 2) are the ID’s of the searched tags).

Sqlfiddle