[Fixed]-Narrowing down possible combinations in a many-to-many relationship

by

1👍

✅

There is no need for a join for every additional tag. Suppose the query contains tag1 and tag2, here is the sql for it:

select distinct tags.id, tags.name from tags inner join foobar_tags 
   on tags.id = foobar_tags.tagId 
   where fooId in 
      (select fooId from tags t inner join foobar_tags ft on t.id = ft.tagId 
          where 
             (select count(distinct name) from foobar_tags inner join tags 
               on tags.id = foobar_tags.tagId
               where fooId = ft.fooId and tags.name in('tag2','tag1')--tags query
             ) = 2 --number of tags in the query
    )
    AND
    name not in ('tag2','tag1')--tags query

we find all foos that have all tags by counting their associated tags which are belong to our query tags.This count should be equal to number of query tags. Then we return tags of the matched foos except the tags that belong to query tags.

You can generate this query for any number of tags and the number of joins will be unchanged.

Here is a fiddle for it.

👤Taher Rahgooy

Leave a comment