Get all posts that have a specific tag and keep all other tags on results with SQL

Question

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 1

I'm not sure about about the Codeigniter interaction, but here is a single query without subqueries that gets the job done:

SELECT
  "Animal".name AS "Animal_name",
  "Tag2".name AS "Tag_name"
FROM "Animal"
INNER JOIN "AnimalTag"
  ON "Animal"."id" = "AnimalTag"."animalId"
INNER JOIN "Tag"
  ON "AnimalTag"."tagId" = "Tag".id
  AND "Tag".name = 'flying'
INNER JOIN "AnimalTag" AS "AnimalTag2"
  ON "AnimalTag2"."animalId" = "Animal".id
INNER JOIN "Tag" AS "Tag2"
  ON "Tag2".id = "AnimalTag2"."tagId"
ORDER BY "Animal".id ASC, "Tag2".id ASC

The return could be something like:

  { Animal_name: 'hawk', Tag_name: 'flying' },                                                                                                                                                                                                                                  
  { Animal_name: 'hawk', Tag_name: 'vertebrate' },                                                                                                                                                                                                                              
  { Animal_name: 'bee', Tag_name: 'flying' }

note how hawk also has the tag vertebrate as desired, even though we searched for flying animals only.

The trick is to add further joins after the AND "Tag".name = 'flying' condition which will then add all other tags to the rows that were previously pruned out.

Here's a minimal runnable example with assertions.

Answer 2

After many hours based on this post, How to Write Many-To-Many Search Queries in MySQL and Hibernate I've found a way to do it in one go. It's working for me so far.

Please note that I'm using this table names: noticias as blog_posts, tags_noticias as blog_tags, tasg as tag

SELECT a.id, a.titulo, GROUP_CONCAT(tags.descripcion) as tags
FROM   noticias a
   INNER JOIN (SELECT   at.id_noticia
               FROM     tags_noticias at
                        INNER JOIN noticias a
                          ON a.id = at.id_noticia
                        INNER JOIN tags t
                          ON t.id = at.id_tag
               WHERE    t.descripcion IN ("tag1","tag2")
               GROUP BY at.id_noticia
               HAVING   Count(at.id_noticia) = 2) aa
     ON a.id = aa.id_noticia 
JOIN tags_noticias ON tags_noticias.id_noticia = a.id 
JOIN tags ON tags.id = tags_noticias.id_tag
GROUP BY tags_noticias.id_noticia

Answer 3

I did it in one go, continuing on @Bulat's code.

SELECT *, 
GROUP_CONCAT(DISTINCT bt.tag_id) as tags_id, 
GROUP_CONCAT(DISTINCT t.tag) as tags
FROM blog_posts bp
INNER JOIN blog_tags bt
ON bt.post_id = bp.id
ON t.id = bt.tag_id
GROUP BY bt.post_id
ORDER BY bp.created_date DESC

Then I return tags and tags_id as an array with a foreach loop

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

foreach($results as $result) {
  $result->tags_comma = $result->tags;
  strpos($result->tags, ',') ? $result->tags = explode(',', $result->tags) : $result->tags = array($result->tags);
  $result->tags_comma = str_replace(',', ', ', $result->tags_comma);
}

foreach($results as $result) {
  $result->tags_id_comma = $result->tags_id;
  strpos($result->tags_id, ',') ? $result->tags_id = explode(',', $result->tags_id) : $result->tags_id = array($result->tags_id);
  $result->tags_id_comma = str_replace(',', ', ', $result->tags_id_comma);
}

Answer 4

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
)