I have the following tables:
cards
uploads
tags
-
tag_upload
: Relation between a tag and an upload. -
card_tag
: Relation between a tag and a card.
Card 1 has a relation to the tags "event", "rmc", and "patient".
Upload 1 has a relation to the tags "event" and "patient".
Upload 2 has a relation to the tags "event", "rmc", and "patient".
Query 1:
I've selected the tags "event" and "patient".
It should return Upload 1, Upload 2, and Card 1.
Query 2:
I've selected the tags "event", "rmc", and "patient".
It should return Upload 1 and Card 1.
The Problem:
I'm currently using the following but when I do this Query 2 returns the same as Query 1:
What would the query look like that would select a card if it had the tags in it? Could I see an example or logic flow you might use?
This is my first version trying to get what I want down. Will be turned into testable code right after with PHPSpec.
Solution
Thank you eggyal!
SELECT *
FROM(
SELECT cards.id, cards.name, cards.type,
cards.updated_at, cards.created_at, cards.image
FROM cards
JOIN card_tag ON card_tag.card_id = cards.id
JOIN tags ON card_tag.tag_id = tags.id
WHERE NOT tags.deleted
AND cards.type NOT IN ('','libraries')
AND cards.account_user_id = 1
AND tags.name IN ('rmc', 'test')
GROUP BY cards.id
HAVING COUNT(DISTINCT tags.id) = 1
UNION ALL
SELECT uploads.id, uploads.name, uploads.type,
uploads.updated_at, uploads.created_at, uploads.image
FROM uploads
JOIN tag_upload ON tag_upload.upload_id = uploads.id
JOIN tags ON tag_upload.tag_id = tags.id
WHERE NOT tags.deleted
AND uploads.type NOT IN ('','libraries')
AND uploads.account_user_id = 1
AND tags.name IN ('rmc','test')
GROUP BY uploads.id
HAVING COUNT(DISTINCT tags.id) = 2
) AS qry
ORDER BY `updated_at` DESC
LIMIT 0, 35