mysql - 根据具有同义行的标签表选择项目

Question

我有三个表:articles、tags 和articles_tags(junction)。

inside tags 我有 3 列(相关):id、名称和别名。alias 列包含早期同义标记的 ID(如果存在)(否则 NULL)。 没有循环或链:所有同义标签都包含相同的别名。例如:

标签

id    |    name    |    alias
------------------------------------
------------------------------------
8     |   pencil   |    NULL
------------------------------------
------------------------------------
3072  |   pencils  |      8
------------------------------------
------------------------------------
3073  | blue pencil|      8
------------------------------------

(标签必须具有此功能，因为它们是用户输入的。)

现在，假设我要搜索包含标签 pencils 或 cats 的所有文章。我希望它们包含所有其他同义标签(铅笔、蓝色铅笔、tomcat、小猫 等)。

我提出的查询如下:

SELECT * FROM `articles` WHERE id IN
    (SELECT article_id FROM `articles_tags` WHERE id IN
         (SELECT id FROM `tags` WHERE COALESCE(alias, id) IN
            (SELECT id FROM `tags` WHERE name IN
                ("pencils", "cats")
        )
    )
)

我相信它是有效的，但我认为可能有比使用 4 个带有 IN 的子查询更好的方法(性能是关键，因为这将是执行的常见搜索)。

我非常感谢您提供更好解决方案的指导。

谢谢。

奖励问题

如果我需要查找不包含搜索标签以外的其他标签的文章怎么办？

也就是说，选择仅包含“猫”和“狗”标签的所有文章将返回仅包含 (“猫”)、(“狗”) 或 (“猫”、“狗”) 标签的文章)。

编辑:表结构:

id    |    name    |    alias
------------------------------------
...
------------------------------------
8     |   pencil   |    NULL
------------------------------------
...
------------------------------------
3072  |   pencils  |      8 (pencil)
------------------------------------
------------------------------------
3073  | blue pencil|      8 (pencil)
------------------------------------
------------------------------------
6088  |    cats    |     NULL
------------------------------------
------------------------------------
7098  |    dogs    |     NULL
------------------------------------
------------------------------------
7099  |  kittens   |     6088 (cats)
------------------------------------
------------------------------------
7102  |  chiwawa   |     7098 (dogs)
------------------------------------

文章

id    |        title       |    content
----------------------------------------
...
----------------------------------------
1     |   I love writing   |    ...
----------------------------------------
...
----------------------------------------
42    |Tips for pet owners |    ...
----------------------------------------
----------------------------------------
108   |  Drawing my dog    |    ...
----------------------------------------

文章标签

id    |      article_id      |    tag_id
------------------------------------
...
------------------------------------
19    |   1(I love writing)  |    3072 (pencils)
------------------------------------
------------------------------------
21    |   1(I love writing)  |    3010 (poetry)
------------------------------------
------------------------------------
22    |   1(I love writing)  |    123  (books)
------------------------------------
------------------------------------
34    | 42(Tips for pet ...) |    6088 (cats)
------------------------------------
------------------------------------
35    | 42(Tips for pet ...) |    7098 (dogs)
------------------------------------
...
------------------------------------
78    | 108(Drawing my dog) |     7098 (dogs)
------------------------------------
------------------------------------
78    | 108(Drawing my dog) |     8    (pencil)
------------------------------------

所需输出:

搜索 chiwawa、cats 应该会产生“给宠物主人的提示”、“画我的狗”。搜索 pencil、dogs 应该会得到“I lovewriting”、“Drawing my dogs”。

对于第二部分(奖励):搜索 chiwawa、kittens 应该仅产生“给宠物主人的提示”，而不“画我的狗”因为它还有一个 pencils 标签。

抱歉文字太多。

Answer 1

这里本质上有两个问题...第一个是关系划分 - 选择应用于父记录的标签，这本身就很有趣。这是两个相关链接:

https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/ (塞尔科)

这个问题的答案提供了大约十几种不同的查询方法(以及使用 PostgreSQL 的性能基准)。内容相当丰富:

How to filter SQL results in a has-many-through relation

对于“铅笔与铅笔”类型标签，我建议通过输入应用程序来处理它。创建一个将标签映射到其他固有标签的“过滤器”。因此，当有人输入“铅笔”标签时，它会自动 a) 将其过滤为“铅笔”，或 b) 输入铅笔和铅笔。这对于“蓝色铅笔”之类的东西特别有用。考虑到上述关系除法问题的相对复杂性，我认为如果“蓝铅笔”同时添加了“铅笔”和“蓝铅笔”(也许还有“蓝色”)，您会发现自己管理数据模型要容易得多标签存入数据库。

在某个时刻，逻辑应该成为应用程序域的一部分并且远离数据库。我相信这是一个很好的例子。否则，您可能会冒着尝试强制数据库引擎执行其设计目的之外的任务的风险，而这根本没有乐趣。