CODEWITHSUNDEEP
mysqlsql
daryl
daryl

Reputation: 15217

MySQL query posts by tag

I'm trying to search for all posts for a specific tag name, whilst still being able to join all tags for the returned posts.

posts

id
...

tags

id
name
slug

posts_tags

id
post_id
tag_id

I'll do a query such as this:

SELECT * FROM posts p 
INNER JOIN posts_tags pt ON pt.post_id = p.id
INNER JOIN tags t ON pt.tag_id = t.d
WHERE t.slug = 'foo'

This will return me all posts with the tag foo, but will no longer join the other tags associated with the posts. How can I write it in such a way so I can still get all tags on the posts?

For example, say I have a post which has 3 tags associated with it: cat, dog and chimp. I want to do a query for posts which have the tag dog. How can I construct a query which will fetch me the posts with the tag dog, ensuring that the cat and chimp tags are also retrieved in the result?

Upvotes: 0

Views: 563

Answers (3)

ScaisEdge
ScaisEdge

Reputation: 133400

if you need all the tags slug related to post that are related to foo then you could use 

select distinct tags.slug 
from tags 
inner join (
    SELECT post_id  from posts_tags pt 
    INNER JOIN tags t ON pt.tag_id = t.d
    WHERE t.slug = 'foo' 
    ) t on t.id = post_tags.post_id
inner join tags on tags.id = post_tags.tag_id

or if you need the related post too 

select post.*, tags.slug 
from tags 
inner join (
    SELECT post_id  from posts_tags pt 
    INNER JOIN tags t ON pt.tag_id = t.d
    WHERE t.slug = 'foo' 
    ) t on t.id = post_tags.post_id
inner join tags on tags.id = post_tags.tag_id 
inner join post on post.id = post_tag.post_id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

If you want all the posts that have 'foo' as a tag, then you need more complicated logic. For your purposes, I think it is probably sufficient to get the tags as a delimited list:

SELECT p.*, GROUP_CONCAT(t.slug) as tags
FROM posts p INNER JOIN
     posts_tags pt
     ON pt.post_id = p.id INNER JOIN
     tags t
     ON pt.tag_id = t.d
GROUP BY p.id
HAVING SUM(t.slug = 'foo') > 0;

Upvotes: 0

Polynomial Proton
Polynomial Proton

Reputation: 5135

If you want all the tags for all the posts which include foo as well as all other tags, then i think you can do a left join.

SELECT * FROM posts p 
INNER JOIN posts_tags pt ON pt.post_id = p.id
LEFT JOIN tags t 
ON pt.tag_id = t.d

Above will give you all the posts and the relevant tags for the posts. You can order by slug OR you can add a clause with left join to filter by tag you need like below:

    SELECT * FROM posts p 
    INNER JOIN posts_tags pt ON pt.post_id = p.id
    LEFT JOIN tags t 
    ON pt.tag_id = t.d AND t.slug IN ('foo') --add other tags if needed

Upvotes: 1

Related Questions