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

2022-01-18 00:00:00 join many-to-many tags mysql

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

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.

我的表结构是一个对象表、一个标签表和一个 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' 匹配的结果行​​被 GROUPing 隐藏".

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.

相关文章