Search code examples
sqljoingroup-bymariadb

Toxi-style tag query broken after adding join operation

I'm trying to select items from a toxi-style database. The database has the following structure:

Table "SONGS":

index title ...
'a001' 'title1' ...
'a002' 'title2' ...

Table "TAGMAP":

index item_index tag_index
1 'a001' 't001'
2 'a001' 't003'
3 'a001' 't004'
4 'a002' 't003'
5 'a002' 't005'

Table "TAGS":

tag_index name
't001' 'foo'
't002' 'bar'
't003' 'foobar'

I want to have a query, that will select items with ALL desired tags present. Using this article, I've built the following query, that works:

SELECT s.title, s.description
FROM (songs s, tagmap tm)
WHERE (tm.tag_index IN (7,3))
AND s.index = tm.item_index
GROUP BY s.item_index 
HAVING COUNT(s.item_index) = 2 
ORDER BY s.date DESC

The IN (7,3) array is dynamically populated in practice, with COUNT(...) = <x> being the length of the desired tags array. This query works.

However, now I also need the names of all associated tags returned with every item. Previously (a query of all items, without respecting any tags), this worked by using joins. But now, when introducing the same JOIN I used previously to this query, it falls apart. The result isn't the same, it's less items and they are even completely different!

If tag2 and tag2 are the tags I'm interested in, the expected output would be:

Title Description ... tags
title1 something ... tag1, tag2, tag3 ...
title2 otherthing ... tag2, tag3, tag5 ...
... ... ... ...

The following record would not be included because one of the required tags (in the specific case, "tag3") is missing:

( 'title5' , 'somewhere' , ... , 'tag1, tag2, tag5 ...' )

Here's my attempt at this task:

SELECT s.title, s.description, GROUP_CONCAT(t2.name SEPARATOR ', ') as tags
FROM (songs s, tagmap tm)
LEFT JOIN tagmap tm2 ON s.index = tm2.item_index 
LEFT JOIN tags t2 ON tm2.tag_index = t2.tag_index
WHERE (tm.tag_index IN (7,3))
AND s.index = tm.item_index
GROUP BY s.item_index 
HAVING COUNT(s.item_index)=2 
ORDER BY s.date DESC

I fail to see where this is broken so any help will be highly appreciated!

Solution

  • Remove the filtering and use conditional aggregation to check if your tags are present among all the tags.

    SELECT s.title, s.description, GROUP_CONCAT(t.tag_name) AS tags
FROM      songs s
LEFT JOIN tagmap tm
       ON s.index = tm.item_index
LEFT JOIN tags t 
       ON tm.tag_index = t.tag_index
GROUP BY s.title, s.description
HAVING COUNT(CASE WHEN t.tag_index IN (2,3) THEN 1 END) = 2

    Note that this solution assumes you don't have duplicated tags. If you can have a title associated multiple times to a single tag, you should split the conditions in the HAVING clause as follows:

    HAVING MAX(tag_index=2) = 1 
   AND MAX(tag_index=3) = 1

    This ensures that there's at least one true value (the respective tag exists).