CODEWITHSUNDEEP
mysqlsql
Rafff
Rafff

Reputation: 1518

How to get posts having ALL or ANY provided tags?

I have three tables:

posts (id, title, ...)
    1, Post title

tags (id, name, slug)
    1, Tag one, tag-1
    2, Other tag, tag-2
    3, Third tag, tag-3

posts_tags (id, post_id, tag_id)
    1, 1, 1
    2, 1, 2
    3, 1, 3

I'm trying to build a query to get all posts having all or any of provided tags.

This is what I have now

SELECT DISTINCT p.* FROM posts p
LEFT JOIN post_tags pt ON pt.post_id=p.id
LEFT JOIN tags t ON t.id=pt.tag_id
-- WHERE --
ORDER BY $orderBy $order
LIMIT $limit

To get posts that are tagged by any of the provided tags I use

WHERE (t.slug='tag-1' OR t.slug='tag-2')

And it seems to be working, but the problem is when I want to get posts tagged by all provided tags

WHERE (t.slug='tag-1' AND t.slug='tag-2')

Even there is a post tagged with both tag-1 and tag-2 I'm getting an empty result.

I don't get any errors just an empty result. Any ideas why? Thanks

Upvotes: 1

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

For all tags, you can do:

SELECT p.*
FROM posts p JOIN
     post_tags pt
     ON pt.post_id = p.id JOIN
     tags t
     ON t.id=pt.tag_id
WHERE t.slug IN ('tag-1', 'tag-2')
GROUP BY p.id
HAVING COUNT(*) = 2  -- "2" is the number of tags
ORDER BY $orderBy $order;

Note that this query uses GROUP BY p.id and SELECT p.*. This is actually allowed by the ANSI standard, as long as id is declared as unique (or equivalently a primary key).

Upvotes: 1

Related Questions