mysql多对多关系查询.如何获取已过滤帖子的所有标签?

本文介绍了mysql多对多关系查询.如何获取已过滤帖子的所有标签?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在Stackoverflow中经历了很多有关此问题的问题，但我认为这是不同的.

I have gone through tons of questions about this issue here in Stackoverflow but I think this is different.

我想做的是让用户能够按标签过滤帖子，以便仅看到剩下要过滤的标签.换句话说，如果用户选择标签"tag1"，它将显示具有该标签的帖子，并显示其他帖子，这些帖子发布了共享但隐藏了过滤后没有可见帖子的标签.

What I'm trying to do is give user ability to filter posts by tags so that one sees only the tags that are left for filtering. In other words if user selects tag "tag1", it show posts with that tag and also show other tags that post shares but hide tags that no visible post have after filtering.

我有表 posts ， posts_tags 和 tags . Posts_tags具有post_id和tag_id.我已经设法使post_id与特定标签集一起可用:

I have tables posts, posts_tags and tags. Posts_tags have post_id and tag_id. I have managed to get post_ids available with specific tagset:

SELECT pt.post_id
FROM posts_tags pt
    INNER JOIN tags t
        ON pt.tag_id = t.id
WHERE t.name IN ('tag1', 'tag2', 'tag3')
GROUP BY pt.post_id
HAVING COUNT(DISTINCT t.id) = 3;

比方说，此查询给出了post_ids 1、2、3:

Let's say this query gives post_ids 1, 2, 3:

post 1 has tag1, tag2, tag3 and tag4
post 2 has tag1, tag2, tag3 and tag5
post 3 has tag1, tag2, tag3 and tag6

现在，我的问题是如何扩展查询以仅向用户返回tag4，tag5和tag6 ，因为这些标签仍可用于进一步过滤帖子.如何实现这一目标?

Now my problem is how to expand the query to return only tag4, tag5 and tag6 to user, because these tags are still available to filter posts further. How to achieve this?

关注性能也会很好.我有130000个帖子，6500个标签，桥表有240000行.

Paying attention to performance would be also nice. I have 130000 posts, 6500 tags and bridge-table has 240000 rows.

使用场景:

用户查找具有自动完成功能的标签，并选择多个标签.
用户根据提交的标签检索帖子.
用户搜索更多标签，然后:

User seaches tags with autocomplete and selects multiple tags.
User retrieves posts based on submitted tags.
User searches more tags and at that point:

我不想给出完整的列表，而只是给出完整的列表

I don't want to give full list but only the ones

a.尚未选择.

b.用于步骤2中检索到的帖子.

b. Are used in posts that were retrieved at step 2.

基于Mosty Mostacho的答案的最终查询:

SELECT DISTINCT pt2.tag_id, t2.name FROM    
(SELECT pt1.post_id
    FROM posts_tags pt1
    INNER JOIN tags t1
        ON pt1.tag_id = t1.id
    WHERE t1.name in ('tag1','tag2','tag3')
    GROUP BY pt1.post_id
    HAVING COUNT(DISTINCT t1.id) = 3) MatchingPosts
INNER JOIN posts_tags pt2 ON (MatchingPosts.post_id = pt2.post_id)
INNER JOIN tags t2 ON (pt2.tag_id = t2.id)
WHERE t2.name NOT IN ('tag1','tag2','tag3');

推荐答案

这是我在凌晨4:30想到的最好的方法:

Well, this is the best I can think of at 4:30 AM:

SELECT distinct tag_id FROM
    (SELECT pt1.post_id FROM pt1
    INNER JOIN tags t1 ON (pt1.tag_id = t1.id)
    WHERE t1.id IN (1, 2)
    GROUP BY pt1.post_id
    HAVING COUNT(DISTINCT t1.id) = 2) MatchingPosts
INNER JOIN pt2 ON (MatchingPosts.post_id = pt2.post_id)
WHERE (pt2.tag_id NOT IN (1, 2))

(1，2)是您要寻找的标签，计数当然必须与您要用来过滤的标签数量相匹配.

The (1, 2) are the tags you're looking for and the count, of course, will have to match the amount of tags you're using to filter.

这是一个示例(请注意，我略微更改了数据)

Here is an example (Notice I slightly changed the data)

这篇关于mysql多对多关系查询.如何获取已过滤帖子的所有标签?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

Tags