CODEWITHSUNDEEP
mysql
zavg
zavg

Reputation: 11081

Many-to-many tags conditional select

I have three MySQL tables - documents, document tags and many-to-many relationship table (with ids of documents and ids of tags).

Document
+------+
|  id  |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+

DocumentToTag
+------+------+
|idDoc |idTag |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    2 |    1 |
|    2 |    3 |
|    4 |    4 |
+------+------+

Tag
+------+-------+
|  id  | value |
+------+-------+
|    1 |     2 |
|    2 |     4 |
|    3 |     8 |
|    4 |    42 |
+------+-------+

It is necessary to fetch Documents with, for example, 2 (or more) tags with certain value. We use following JOIN query:

SELECT DISTINCTROW
Document.id

FROM Document
LEFT JOIN DocumentToTag AS dt1 ON dt1.idDoc = Document.id  
LEFT JOIN Tag AS tag1 ON dt1.idTag = tag1.id
LEFT JOIN DocumentToTag AS dt2 ON dt2.idDoc = Document.id
LEFT JOIN Tag AS tag2 ON dt2.idTag = tag2.id

WHERE tag1.value = 'someTagValue'
AND   tag2.value = 'someOtherTagValue'

In this case we need to do as many JOINs as many tags we need to add in condition. So they query should be dynamically created by some script. Is there more elegant way to deal with it?

Upvotes: 2

Views: 93

Answers (2)

echo_Me
echo_Me

Reputation: 37243

you may look for this

   SELECT DISTINCT
  Document.id
  FROM Document
 LEFT JOIN DocumentToTag AS dt1 ON dt1.idDoc = Document.id  
 LEFT JOIN Tag AS tag1 ON dt1.idTag = tag1.id

 WHERE tag1.value in ( 'someTagValue' ,'someOtherTagValue')

DEMO HERE

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33391

Try this:

SELECT
Document.id
FROM Document
    JOIN DocumentToTag AS dt1
        ON dt1.idDoc = Document.id  
    JOIN Tag AS t
        ON dt1.idTag = t.id
WHERE t.value IN ('tag1', 'tag2', 'tag3') -- you can dynamicaly generate this list
GROUP BY Document.id
HAVING COUNT(DISTINCT t.value) = 3 -- you can pass this as parameter

Upvotes: 4

Related Questions