使用SQL获取特定标签的所有帖子 [英] Get all posts for specific tag with SQL

问题描述

我正在使用的博客有3个不同的表。

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

第一个表：blog_posts，它具有ID，标题，内容，user_id（谁创建了帖子） ），created_date和slug。

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

第二个表：blog_tags，它具有ID，post_id和tag_id

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

3rd table：标签，它具有ID和标签

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

我正在使用表3将所有标签保存一次，因此不会重复。然后我使用表2将标签连接到帖子（表1）。

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.

我的代码现在仅返回我要在其中查找帖子的标签，但我仍然想写出其余标签，仅显示HAS的帖子包括该特定标签...

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...

我通常对SQL敏锐，但是这次我的头脑仍然完全...请帮助我:)
如果需要的话，我正在使用PHP和CodeIgniter。

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.

预先感谢。

Mike

编辑

我将结果打印为json，因此我可以：

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
}

在以下答案的帮助下。我的SQL和代码现在说：

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
} 

推荐答案

我认为您很高兴向数据库发送两个请求。

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
)

这篇关于使用SQL获取特定标签的所有帖子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！