时间戳

首页 > 解决方案 > 选择带有搜索标签的所有帖子（和相关标签）

mysql - 选择带有搜索标签的所有帖子（和相关标签）

问题描述

我想选择标有一个或多个搜索词的帖子（及其标签）。

这是我的表：

邮政

| ID | community | created             | updated             | title | content |
|----|-----------|---------------------|---------------------|-------|---------|
| 99 | 17        | 2019-08-14 14:20:38 | 2019-08-14 14:20:38 | Lorem | Ipsum   |
| 80 | 16        | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | Lorem | Ipsum   |
| 79 | 16        | 2019-07-27 23:09:47 | 2019-07-27 23:09:47 | Lorem | Ipsum   |

社区

| ID | title          |
|----|----------------|
| 16 | The Scary Door |
| 17 | Other          |

社区标签类别

| Community | Category |
|-----------|----------|
| 16        | 5        |
| 16        | 18       |
| 16        | 19       |
| 16        | 20       |
| 17        | 6        |

标签类别

| category    | ID |
|-------------|----|
| character   | 5  |
| focus       | 18 |
| warning     | 19 |
| time-period | 20 |
| NULL        | 6  |

标签分类

| category | tag |
|----------|-----|
| 5        | 146 |
| 6        | 131 |
| 6        | 147 |
| 19       | 147 |
| 20       | 148 |

标签

| name          | id  |
|---------------|-----|
| spider        | 146 |
| arachnophobia | 147 |
| Victorian era | 148 |
| NULL          | 131 |

帖子标签

| post | tag |
|------|-----|
| 99   | 147 |
| 80   | 146 |
| 80   | 147 |
| 80   | 148 |
| 79   | 131 |

我已经在使用此查询来获取有关所有帖子及其相关标签和类别的信息：

SELECT p.id, c.id as 'commid', c.title AS 'community', p.title, p.content, p.author, p.created, p.updated, ct.category, t.name AS 'tag'
FROM Post p, Community c, CommunityTagCategories cc, TagCategory ct, TagCategorised tc, Tag t, PostTags pt
WHERE p.community=c.id AND c.id=cc.community AND cc.category=ct.id AND ct.id=tc.category AND tc.tag=t.id AND t.id=pt.tag AND pt.post=p.id
ORDER BY p.created DESC;

如果我搜索像“arachnophobia”这样的标签，我希望结果可以像这样缩小：（如果我想搜索“arachnophobia”和“蜘蛛”，它应该只显示 80 后的信息）

| ID | commid | community      | title | content | author | created             | updated             | category    | tag           |
|----|--------|----------------|-------|---------|--------|---------------------|---------------------|-------------|---------------|
| 99 | 17     | Other          | Lorem | Lorem   | 7      | 2019-08-14 14:20:38 | 2019-08-14 14:20:38 | NULL        | arachnophobia |
| 80 | 16     | The Scary Door | Lorem | Lorem   | 7      | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | character   | spider        |
| 80 | 16     | The Scary Door | Lorem | Lorem   | 7      | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | warning     | arachnophobia |
| 80 | 16     | The Scary Door | Lorem | Lorem   | 7      | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | time-period | Victorian era |

从搜索类似的问题看来，我可能需要使用嵌套的 SELECT 和 INNER JOINS。我试着这样做：

SELECT p2.id, c.id as 'commid', c.title AS 'community', p2.author, p2.created, p2.updated, p2.title, p2.content, tc.category, t.name AS 'tag' 
FROM 
    (SELECT p.id
    FROM Post p, Tag t, PostTags pt
    WHERE p.id=pt.post AND pt.tag=t.id AND t.name ="arachnophobia"
    ) search
INNER JOIN Post p2
ON (search.id = p2.id)
INNER JOIN Community c
ON p2.community=c.id
INNER JOIN CommunityTagCategories cc
ON c.id=cc.community
INNER JOIN TagCategory tc
ON cc.category=tc.id
INNER JOIN TagCategorised ct
ON tc.id=ct.category
INNER JOIN Tag t
ON ct.tag=t.id
INNER JOIN PostTags pt
ON t.id=pt.tag
INNER JOIN Post p3
ON pt.post=p3.id
ORDER BY p2.created DESC;

它只返回帖子 99 和 80 的结果，但它为我提供了相关社区中存在的每个标签和类别的结果，即使这些帖子与这些标签和类别无关。所以这里它为帖子 80 返回一个额外的行，带有来自帖子 79 的标签，它们都在同一个社区中。

| ID | commid | community      | title | content | author | created             | updated             | category    | tag           |
|----|--------|----------------|-------|---------|--------|---------------------|---------------------|-------------|---------------|
| 99 | 17     | Other          | Lorem | Lorem   | 7      | 2019-08-14 14:20:38 | 2019-08-14 14:20:38 | NULL        | arachnophobia |
| 80 | 16     | The Scary Door | Lorem | Lorem   | 7      | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | character   | spider        |
| 80 | 16     | The Scary Door | Lorem | Lorem   | 7      | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | warning     | arachnophobia |
| 80 | 16     | The Scary Door | Lorem | Lorem   | 7      | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | time-period | Victorian era |
| 80 | 16     | The Scary Door | Lorem | Lorem   | 7      | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | character   | NULL          |

几天前，我在这里问了一个关于显示所有帖子数据的类似问题，用户 nbk 提供了一个同样有效的不同查询，但它对我来说有点太复杂了，不知道如何改变自己。

标签： mysql

解决方案

为避免重复行，您可以使用 DISTINCT：

SELECT DISTINCT p2.id
  , c.id as commid
  , c.title AS community
  , p2.author
  , p2.created
  , p2.updated
  , p2.title
  , p2.content
  , tc.category
  , t.name AS tag 
FROM  (
   SELECT p.id
   FROM Post p
   INNER JOIN PostTags pt ON p.id=pt.post
   INNER JOIN Tag t ON  pt.tag=t.id AND t.name ="arachnophobia"
) search
INNER JOIN Post p2 ON search.id = p2.id
INNER JOIN Community c ON p2.community=c.id
INNER JOIN CommunityTagCategories cc ON c.id=cc.community
INNER JOIN TagCategory tc ON cc.category=tc.id
INNER JOIN TagCategorised ct ON tc.id=ct.category
INNER JOIN Tag t ON ct.tag=t.id
INNER JOIN PostTags pt ON t.id=pt.tag
INNER JOIN Post p3 ON pt.post=p3.id
ORDER BY p2.created DESC;

只是一些建议。

您不应该在列名周围使用单引号（如果用于文字文本）在需要时使用反引号。

不应使用基于表名逗号分隔的旧隐式连接语法和 where 条件（您在子查询中有）。

避免在连接条件周围出现不必要的 ()。

并查看您的关系：您应该避免加入您已经通过搜索获得的标签的最后一部分。

SELECT DISTINCT p2.id
  , c.id as commid
  , c.title AS community
  , p2.author
  , p2.created
  , p2.updated
  , p2.title
  , p2.content
  , tc.category
  , t.name AS tag 
FROM  (
    SELECT p.id
    FROM Post p
    INNER JOIN Tag t ON p.id=pt.post AND t.name ="arachnophobia"
    INNER JOIN PostTags pt ON pt.tag=t.id 
) search
INNER JOIN Post p2 ON search.id = p2.id
INNER JOIN Community c ON p2.community=c.id
INNER JOIN CommunityTagCategories cc ON c.id=cc.community
INNER JOIN TagCategory tc ON cc.category=tc.id
INNER JOIN TagCategorised ct ON tc.id=ct.category
ORDER BY p2.created DESC;

推荐阅读