php - SQL 选择具有相似标签的帖子

Question

在我的 MariaDB 中，我有一个名为

的表

“帖子”与

id, title, username_id, text, image_url, url

一个叫做“标签”的

id, tag

还有一个叫做 post_tags 的

id、post_id、tag_id。

我想要完成的事情是从“帖子”表中获取与页面上当前显示的帖子具有最多共同标签的 3 个帖子。

我被困在这里不知道从哪里开始。

编辑

Posts

id | username_id |   title   |    text     |       image_url      |           url

 1        1         example    example_text  localhost/image.jpg     localhost/first-post
 2        1         example1   example_text  localhost/image1.jpg    localhost/second-post
 3        1         example2   example_text  localhost/image2.jpg    localhost/third-post
 4        1         example4   example_text  localhost/image4.jpg    localhost/fourth-post
...      ...          ...          ...                ...                     ...
...      ...          ...          ...                ...                     ...


Tags

id | tag

 1   herbs
 2   flower
 3   rose

Post_tags

id | post_id | tag_id

 1      1        1
 2      1        2
 3      1        3
 4      2        1
 5      3        1
 6      3        2
 7      4        1
 8      4        2
 9      4        3        

我想返回一个包含 posts.title 和 posts.image_url 的数组，选择具有最多 post_tags.tag_id 的帖子与当前的相同。

如你所见，如果我们取第 n 个帖子。 1 作为选定的帖子，帖子 n。 4与其共有的标签最多，post n.3排在第二，post n.2排在第三。

example4 | localhost/image4.jpg
example3 | localhost/image3.jpg
example2 | localhost/image2.jpg

我希望我说得更清楚了。谢谢。

Answer 1

SELECT p.id, p.title, p.image_url, COUNT(*) as how_many_shared_tags
FROM posts p
JOIN post_tags pt ON pt.post_id = p.id
                 AND pt.tag_id IN(SELECT tag_id FROM post_tags WHERE post_id = 1)
WHERE p.id != 1
GROUP BY p.id, p.title, p.image_url
order by COUNT(*) DESC
LIMIT 3

应要求，查询说明:

1. 为了找到与“父”帖子共享最多标签的前 3 个帖子，我们首先需要获取“父”帖子的标签列表 => SELECT tag_id FROM post_tags WHERE post_id = 1
2. 然后通过添加条件 tag_id IN(LIST_OF_tag_id_FROM_SUB_SELECT_SHOWN_ABOVE) 在包含帖子 ID 和标签的表中进行搜索，找到至少具有这些标签之一的帖子。
3. 现在我们知道哪些帖子至少与“parent”共享一个标签，因此我们可以计算它们实际共有多少个标签并按它排序 => 按 COUNT(*) DESC 排序
4. 因为“parent”帖子也“共享”这些标签，我们不希望他出现在我们的结果中，我们给出了排除“parent”ID 的附加条件 => WHERE p.id != 1
5. 最后我们将结果集限制为 3 行，因为我们只需要前 3 行。LIMIT 3
6. 不需要选择计数，它只是指出它计数的内容 COUNT(*) as how_many_common_tags