MySQL查询以多对多关系检索完全匹配的项目

问题描述

我有下表:

Media:
- mediaid
- mediatitle


MediaTag:
- mediaid
- mediatag


Tag:
- tagid
- tagname

以下标记:

Tag: (1) - Public
Tag: (2) - Premium
Tag: (3) - Restricted

以下带有标签的媒体:

Media: (1) - Public
Media: (2) - Premium & Restricted
Media: (3) - Premium
Media: (4) - Restricted
Media: (5) - No tags

假定用户有权查看仅Public (1)和Restricted (3)标签.如果媒体包含用户无权访问的标签的组合，则不允许他看到它.如果媒体没有标签，则根本不可见.因此，在这种情况下，用户只能看到Media 1和Media 4.

Assume that a user has permissions to see only Public (1) and Restricted (3) tags. If a media has a combination of tags that the user doesn't have explicit access to, he's not allowed to see it. If a media has no tag it's not visible at all. So the user in this case would be able to see only Media 1 and Media 4.

假设我具有标签ID为(1,3)的数组形式的用户权限，是否可以编写MySQL查询来检索媒体?

Is it possible to write a MySQL query to retrieve the media assuming I have the user permissions in the form of an array with tag IDs (1,3) ?

推荐答案

JOIN这三个表，并将包含用户有权访问它们的标签ID的数组传递给IN谓词， WHERE子句.

JOIN the three tables, and pass the array, that contain the tag Id's that the user has permissions to access them, to the IN predicate in the WHERE clause.

对于数组[1, 3]，您可以执行以下操作:

For instance for the array [1, 3], you can do this:

SELECT m.mediatitle, t.tagname
FROM media m
INNER JOIN MediaTag mt ON m.mediaid = mt.mediaid
INNER JOIN Tag t ON mt.mediatag = t.tagid
WHERE t.id IN (1, 3);

更新:例如，要仅获取同时具有两个标签ID(1、3)的mediatitle，请使用带有HAVING COUNT(tagname) = 2的GROUP BY mediatitle，如下所示:

Update: To get only the mediatitles that had both tag ids (1, 3) for example, use a GROUP BY mediatitle with HAVING COUNT(tagname) = 2 like so:

SELECT m.mediatitle
FROM media m
INNER JOIN mediaTag mt ON m.mediaid = mt.mediaid
INNER JOIN tag t ON mt.mediatag = t.tagid
WHERE t.tagid IN (1, 3)
GROUP BY m.mediatitle
HAVING COUNT(t.tagname) = 2;

这篇关于MySQL查询以多对多关系检索完全匹配的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！