Seeking way to returning all tags matched to media files

byMR

January 3, 2025

I asked an earlier question but kind of messed it up, so I’m reformulating it again here.

I have 3 related tables:

media
media_tags (bridge table)
tags

Here’s the code I have so far:

SELECT m.media_id, m.name, array_agg(distinct t.tag) filter (WHERE t.date_deleted IS NULL) AS tags 
FROM media m 
LEFT JOIN media_tags mt USING (media_id) 
LEFT JOIN tags t USING (tag_id)
WHERE m.date_deleted IS NULL AND t.tag = ANY(array['dog','cat'])
GROUP BY m.media_id, m.name

This is getting very close to what I want, but it’s not quite correct. Imagine there are these media records:

Media 1 with these tags: dog, bird, sheep, horse
Media 2 with these tags: dog, cat, cow
Media 3 with these tags: cat, horse, rabbit
Media 4 with these tags: cow, horse, sheep

My query above does return Media 1, 2 & 3 but the tags shown only consist of dog & cat. What I want is for tags to always contain ALL of its tags if there is any match with any of the values in the array. So, if [‘dog’,’cat’] are in the query, then Media 1, 2, & 3 should be returned with all of their respective tags, but Media 4 should not because there’s no match.

I sense that I’m pretty close to a solution but just can’t figure it out.

Update:
@Tim Biegeleisen solved it! Since comments don’t format code correctly, I’m going to repost a slightly modified version of his code here, which does precisely what I need:

WITH cte AS (
    SELECT m.media_id
    FROM media m
    INNER JOIN media_tags mt ON mt.media_id = m.media_id
    INNER JOIN tags t ON t.tag_id = mt.tag_id
    WHERE t.tag = any(array['lowrider', 'fire hydrants', 'airshow'])
    GROUP BY m.media_id
)
SELECT m.media_id, m.name, ARRAY_AGG(DISTINCT t.tag) FILTER (WHERE t.date_deleted IS NULL) AS tags
FROM media m
INNER JOIN media_tags mt ON mt.media_id = m.media_id
INNER JOIN tags t ON t.tag_id = mt.tag_id
WHERE m.media_id IN (SELECT media_id FROM cte)
GROUP BY m.media_id, m.name

>Solution :

The ANSI standard way of doing this (sans the use of Postgres arrays) would be to first find all matching tags, then use that result to restrict your current query.

WITH cte AS (
    SELECT m.media_id
    FROM media m
    INNER JOIN media_tags mt ON mt.media_id = m.media_id
    INNER JOIN tags t ON t.tag_id = mt.tag_id
    WHERE t.tag IN ('dog', 'cat')
    GROUP BY m.media_id
    HAVING COUNT(DISTINCT t.tag) = 2
)

SELECT m.media_id, m.name, ARRAY_AGG(DISTINCT t.tag) FILTER (WHERE t.date_deleted IS NULL) AS tags
FROM media m
INNER JOIN media_tags mt ON mt.media_id = m.media_id
INNER JOIN tags t ON t.tag_id = mt.tag_id
WHERE m.media IN (SELECT media_id FROM cte)
GROUP BY m.media_id, m.name;