Michael Michael - 7 months ago 28
SQL Question

Get all posts for specific tag with SQL

I have 3 different tables for a blog I am working with.

1st table: blog_posts, it has an ID, Title, Content, user_id (who created post), created_date and slug.

2nd table: blog_tags, it has an ID, post_id and tag_id

3rd table: tags, it has an ID and tag

I am using table 3 to save all tags once, so that there are no duplicates. Then I am using table 2 to connect a tag to a post (table 1).

The problem I am having is getting all the posts from a specific tag and also returning all the other tags.

My code right now only returns the tag that I want to find posts in, but I still want to write out the rest of the tags, only the posts showing HAS to include that specific tag...

I am usually sharp with SQL, but this time my head is totally still... Please help me :)
I am using PHP and CodeIgniter if that matters.

Thanks in advance.

Mike

Edit

I am printing the results out as json, which gives me following:

{
"data": [
{
"id": "28",
"title": "blabla",
"content": "<p>hello<\/p>",
"user_id": "1",
"created_date": "2014-08-18 23:57:22",
"slug": "blabla-2014-08-18-235722"
},
{
"id": "34",
"title": "test2",
"content": "<p>test2<\/p>",
"user_id": "1",
"created_date": "2014-08-23 21:41:00",
"slug": "test2-2014-08-23-214100"
}
],
"success": true
}


With the help from the answer below. My SQL and code now says:

$sql = "SELECT * FROM blog_posts bp
WHERE EXISTS(SELECT * FROM blog_tags bt INNER join
tags t ON t.id = bt.tag_id
WHERE bp.id = bt.post_id
AND t.id = ".$this->db->escape($tag_id).")";

$results = $this->db->query($sql)->result();

return $results;


What I want to get is the following:

{
"data": [
{
"id": "28",
"title": "blabla",
"content": "<p>hello<\/p>",
"user_id": "1",
"created_date": "2014-08-18 23:57:22",
"slug": "blabla-2014-08-18-235722",
"tags": [
{
"id": 1
"tag": "test",
},
{
"id": 2
"tag": "test2",
}
]
},
{
"id": "34",
"title": "test2",
"content": "<p>test2<\/p>",
"user_id": "1",
"created_date": "2014-08-23 21:41:00",
"slug": "test2-2014-08-23-214100"
"tags": [
{
"id": 3
"tag": "testa",
},
{
"id": 1
"tag": "test",
}
]
}
],
"success": true
}

Answer

I assume you are happy to send two requests to the database.

First, get all the posts for a given tag:

SELECT * FROM blog_posts bp 
WHERE EXISTS (SELECT * FROM blog_tags bt INNER JOIN
               tags t ON t.id = bt.tag_id
              WHERE bp.id = bt.post_id
               AND t.tag = @SearchTag)

Second, you want to tags, I guess, linked to the one you are looking for via posts:

SELECT * FROM tags t
WHERE EXISTS ( -- Here we link two tags via blog_tags
               SELECT * FROM blog_tags bt1 INNER JOIN
               blog_tags bt2 ON bt1.post_id = bt2.post_id
                     AND bt1.tag_id != bt2.tag_id INNER JOIN
               tags t ON t.id = bt1.tag_id
               WHERE t.tag = @SearchTag
                  AND t.id = bt2.tag_id
)