多个“标签"的Mysql join查询(多对多关系)匹配所有标签?

问题描述

我正在尝试查询匹配所有给定标签集的对象.

I am trying to query for Objects that match ALL of a given set of Tags.

基本上我希望用户能够添加越来越多的标签来过滤或缩小"他们的搜索结果，就像 newegg.com 那样.

Basically I want users to be able to add on more and more Tags to filter or "narrow down" their search results, kind of like newegg.com does.

我的表结构是一个Objects表，一个Tags表，和一个MANY:MANY关系表ObjectsTags.所以我有一个像这样的 JOIN 查询:

My table structure is a table of Objects, a table of Tags, and a MANY:MANY relation table ObjectsTags. So I have a JOIN query like so:

SELECT * FROM Objects
LEFT OUTER JOIN ObjectsTags ON (Objects.id=ObjectsTags.object_id)
LEFT OUTER JOIN Tags ON (Tags.id=ObjectsTags.tag_id)

我尝试使用 IN 子句/条件，如下所示:

I tried using an IN clause/condition, like this:

SELECT * FROM Objects
LEFT OUTER JOIN ObjectsTags ON (Objects.id=ObjectsTags.object_id)
LEFT OUTER JOIN Tags ON (Tags.id=ObjectsTags.tag_id)
WHERE Tags.name IN ('tag1','tag2')
GROUP BY Objects.id

但我了解到这模拟了一系列 OR，因此您向查询添加的标签越多，您获得的结果就越多，而不是像我希望的那样缩小结果集.

But I learned that this simulates a series of ORs, so the more tags you add to the query the MORE results you get, instead of the result set narrowing down like I was hoping.

我也尝试过做多个 LIKE WHERE 条件，然后将它们合并在一起:

I also tried doing multiple LIKE WHERE conditions, ANDed together:

SELECT * FROM Objects
LEFT OUTER JOIN ObjectsTags ON (Objects.id=ObjectsTags.object_id)
LEFT OUTER JOIN Tags ON (Tags.id=ObjectsTags.tag_id)
WHERE Tags.name LIKE 'tag1' 
AND Tags.name LIKE 'tag2'
GROUP BY Objects.id

但这不会返回任何结果，因为当结果组合在一起时，OUTER JOINed Tags.name 列只包含tag1"，而不包含tag2".'tag2' 匹配的结果行被分组隐藏".

But this returns no results, since when the results are grouped together the OUTER JOINed Tags.name column just contains 'tag1', and not also 'tag2'. The result row where 'tag2' matched is "hidden" by the GROUPing.

如何匹配所有标签以获得我所追求的缩小"或深入"效果?谢谢.

How can I match ALL of the tags to get the "narrow down" or "drill down" effect that I am after? Thanks.

推荐答案

使用:

  SELECT * 
    FROM OBJECTS o
    JOIN OBJECTSTAGS ot ON ot.object_id = o.id
    JOIN TAGS t ON t.id = ot.tag_id
   WHERE t.name IN ('tag1','tag2')
GROUP BY o.id
  HAVING COUNT(DISTINCT t.name) = 2

您遗漏了 HAVING 子句.

You were missing the HAVING clause.

如果您只需要同时存在两个标签的行，则无需 LEFT JOIN.

There's no need to LEFT JOIN if you want only rows where both tags exist.

这篇关于多个“标签"的Mysql join查询(多对多关系)匹配所有标签?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！