如何列出按常用标签数量排序的相关博文？

I want to display a list of related blog posts and I want the list to be ordered by the number of common tags they have to the current post. Each post can have multiple tags associated to it. Here is my table structure:

我想显示相关博客帖子的列表,我希望列表按照当前帖子的常用标签数量排序。每个帖子都可以有多个与之关联的标签。这是我的表结构:

[Posts] <-- [posts-to-tags-joining-table] --> [Tags]

[帖子] < - [posts-to-tags-joining-table] - > [Tags]

I'm using PHP and MySQL - can I do this in one query?

我正在使用PHP和MySQL - 我可以在一个查询中执行此操作吗?

2 个解决方案

#1

What about...:

SELECT COUNT(*) AS numcommon, posts.pid, posts.post FROM posts
               INNER JOIN p2t ON p2t.pid = posts.pid
               WHERE p2t.tid IN
               (SELECT p2t.tid FROM p2t
               INNER JOIN posts ON p2t.pid = posts.pid
               WHERE posts.pid = 1)
               AND posts.pid != 1
               GROUP BY posts.pid
               ORDER BY numcommon

assuming pid as the primary key in the posts table, tid as the primary key in the tags table, both foreign keys in the p2t (post to tag) table?

假设pid作为posts表中的主键,tid作为tags表中的主键,p2t(post to tag)表中的两个外键?

#2

Sure you can do it in one query:

当然,您可以在一个查询中执行此操作:

SELECT postid, count(tagid) as common_tag_count
FROM posts_to_tags
WHERE tagid IN (SELECT tagid FROM posts_to_tags WHERE postid = 2)
GROUP BY postid ORDER BY common_tag_count DESC;

#1