Foreign key registry query with multiple occurrences

Question

Foreign key registry query with multiple occurrences

Navigation

#1 by (1 votes)

0

I'm developing a system for storing documentation for day-to-day use with PHP and mysql, and to make it easier to query, I have added tags to these documentation and to ensure the integrity of the data I added another table called documentacao_tag. However, I'm having trouble performing a query, where the documentation contains exactly all related tags. Ex: Query all documents that have exactly the tag AND server tag.

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

Database modeling : 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');

Example output by selecting the 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 have a solution, but I do not think it is the most ideal, depending 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 dynamically concatenate 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
)

mysql sql

asked by anonymous 01.10.2015 / 21:13

1 answer

How to make a direct connection to the table? Button action after clicking - jQuery

score 1 · Accepted Answer

I do not know if I understood your problem very well because of the complexity of the query you posted, but to search for posts where / em> should appear do something like this:

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 does a join with a subquery that fetches all the results of the post ( dt.doc_id = doc.doc ) containing all tags > of the search ( dt.qtd = 2 where 2 is the total tags searched and tag_id IN (1, 2) are the ID's of the searched tags).

Foreign key registry query with multiple occurrences

1 answer

SQLFiddle