多个标签搜索查询 [英] Multiple tags search query

问题描述

我正在研究基于标签的搜索.我有三个表tag(id，name)，tagXmedia(id，tag_id，media_id)和media(id，...). tagXmedia是标签和媒体表之间的映射表.这是一对多的关系.

I working on a tag based search. I have three tables tag(id,name), tagXmedia(id,tag_id,media_id), and media(id,...). tagXmedia is the mapping table between the tag and media tables. This is a one to many relationship.

关于如何创建"AND"类型的搜索，我真的可以使用一些指导.例如，我需要能够在媒体表中搜索与"home"和"hawaii"标签相关联的条目.

I could really use a little direction on how to create an "AND" type of search. For instance I need to be able to search for an entry in the media table that is associated with both the "home" and "hawaii" tags.

我已经尝试过MySQL的存在，例如

I have experimented with MySQL exists such as

SELECT
    tam.media_id
FROM
    tagXmedia tam
    LEFT JOIN tag ON tag.id = tam.tag_id
WHERE
    EXISTS (SELECT * FROM tag  WHERE tag.name = "home")
AND EXISTS (SELECT * FROM tag WHERE tag.name = "hawaii")

任何帮助，我们将不胜感激.

Any help on this would really be appreciated.

推荐答案

以下内容应该可以工作.

The following should work.

SELECT media_id
FROM tagXmedia
WHERE tag_id IN (SELECT id FROM tag WHERE name IN ('home','hawaii'))
GROUP BY media_id
HAVING COUNT(tag_id) = 2;

如果您希望它不仅匹配两个标签，还可以轻松添加它们.只需记住在HAVING子句中更改 2 .

If you wish to have it match more than just two tags, you can easily add them. Just remember to change the 2 in the HAVING clause.

我假设tagXmedia中的所有行都是唯一的.如果没有，则必须在COUNT部分中添加DISTINCT.

I assumed all the rows in tagXmedia are unique. In case they aren't, you will have to add DISTINCT to the COUNT part.

这篇关于多个标签搜索查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！