Jan Koupil Jan Koupil - 11 months ago 44
MySQL Question

MySQL search for a record having AND NOT having specific tags

In a large project (collection of solved problems in …) I have a data organized like this (a typical M:N relation):

records | record_id, other_info
tag_map | map_id, record_id, tag_id
tags | tag_id, tag_text, other info

I would like to add filtering feature. In the filter the user can choose for each tag whether its presence is required, ignored or forbidden.

e.g. I need to find all records that

  • have tag with tag_id = 1

  • AND have tag with tag_id = 4

  • AND do not have tag with tag_id = 3

  • and any other tags are not important.

Answer Source

If the (record_id,tag_id) tuple is unique in the tag_map table, we could use a combination of join and anti-join operations.

For example:

 SELECT r.record_id
   FROM records r

   JOIN tag_map t1 
     ON t1.record_id = r.record_id
    AND t1.tag_id    = 1  

   JOIN tag_map t4
     ON t4.record_id = r.record_id
    AND t4.tag_id    = 4

   JOIN tag_map t3
     ON t3.record_id = r.record_id
    AND t3.tag_id    = 3

  WHERE t3.record_id IS NULL

Again, this is based on the existence of a uniqueness constraint on (record_id,tag_id) in tag_map table. Otherwise, this has the potential to return "duplicate" rows.

There are other query patterns that can return equivalent results. For example, we could use a NOT EXISTS (correlated subquery) in place of the anti-join.