SQL标记列表和标记过滤 [英] SQL tag list and tag filtering

问题描述

我有一个SQL数据库，在其中存储用户和与用户关联的标签(许多关系).我有一个带有users表，tags表和桥"表usertag的经典架构，该表将用户与标签链接起来:

I have a SQL database in which I store users and tags associated to users (many to many relationship). I have the classic schema with users table, tags table and the "bridge" table usertag which links users with tags:

users table:
    +---------+---------+
    | Id      |  Name   |
    +---------+---------+
    | 1       | Alice   |
    | 2       | Bob     |
    | 3       | Carl    |
    | 4       | David   |
    | 5       | Eve     |
    +---------+---------+

tags table:
    +---------+---------+
    | Id      | Name    |
    +---------+---------+
    | 10      | Red     |
    | 20      | Green   |
    | 30      | Blue    |
    +---------+---------+

usertag table:
    +---------+---------+
    | UserId  |  TagId  |
    +---------+---------+
    | 2       | 10      |
    | 2       | 20      |
    | 1       | 30      |
    | 4       | 20      |
    | 4       | 10      |
    | 4       | 30      |
    | 5       | 10      |
    +---------+---------+

现在，我使用GROUP_CONCAT()函数进行了查询，以逗号分隔的字段的形式检索所有用户及其标签:

Now, I made a query to retrieve all the users and their tags as a comma separated field, using the GROUP_CONCAT() function:

SELECT u.*, GROUP_CONCAT(ut.tagid) as tags FROM users as u LEFT JOIN usertag as ut ON u.id = ut.userid GROUP BY u.id

这给了我正确的输出:

output:
    +---------+---------+----------+
    | Id      |  Name   | Tags     |
    +---------+---------+----------+
    | 1       | Alice   | 30       |
    | 2       | Bob     | 10,20    |
    | 3       | Carl    | (null)   |
    | 4       | David   | 10,30,20 |
    | 5       | Eve     | 10       |
    +---------+---------+----------+

问题是，现在我想在此基础上实现标签过滤，即能够按标签(或多个标签)查询用户.过滤器应使用AND运算符进行工作.

The problem is that now I want to implement tag filtering on top of that, i.e. being able to query the users by tag (or multiple tags). The filter should work using the AND operator.

例如:获取标记为红色(10)和绿色(20)的用户:

For example: Get users with tag Red (10) AND Green (20):

output:
    +---------+---------+----------+
    | Id      |  Name   | Tags     |
    +---------+---------+----------+
    | 2       | Bob     | 10,20    |
    | 4       | David   | 10,30,20 |
    +---------+---------+----------+

另一个示例:使用标签Red(10)获取用户:

Another example: Get users with tag Red (10):

output:
    +---------+---------+----------+
    | Id      |  Name   | Tags     |
    +---------+---------+----------+
    | 2       | Bob     | 10,20    |
    | 4       | David   | 10,30,20 |
    | 5       | Eve     | 10       |
    +---------+---------+----------+

另一个示例:使用标签为Red(10)，Green(20)和Blue(30)的用户:

Another example: Get users with tag Red (10), Green (20) and Blue (30):

output:
    +---------+---------+----------+
    | Id      |  Name   | Tags     |
    +---------+---------+----------+
    | 4       | David   | 10,30,20 |
    +---------+---------+----------+

如何实现这种查询? 这个问题 SO上的内容非常相似，并且实际上可以运行，但是它不处理GROUP_CONCAT()字段，我想保持原样

How can I implement such query? This question on SO is very similar and it actually works but it doesn't deal with the GROUP_CONCAT() field which is something I'd like to keep as it is

此处是SQL提琴 http://sqlfiddle.com/#!9/291a5c/8

编辑

可能会想到此查询有效:

One may imagine that this query works:

检索所有标记为红色(10)和蓝色(20)的用户:

Retrieve all users with tag Red (10) and Blue (20):

 SELECT u.name, GROUP_CONCAT(ut.tagid)
    FROM users as u
    JOIN usertag as ut ON u.id = ut.userid
   WHERE ut.tagid IN (10,20)
GROUP BY u.id
  HAVING COUNT(DISTINCT ut.tagid) = 2

哪个给:

output:
    +---------+---------+----------+
    | Id      |  Name   | Tags     |
    +---------+---------+----------+
    | 2       | Bob     | 10,20    |
    | 4       | David   | 10,20    |
    +---------+---------+----------+

哪个用户名正确(Bob和David)，但是Tags字段缺少David列表中的标签30！

which username-wise is correct (Bob and David) but the Tags field is missing the tag 30 from David's list!

推荐答案

left join tags表，并将要搜索的id包含在join子句中，并检查having中的计数.

left join the tags table and include the id's being searched for in the join clause and check for counts in having.

SELECT u.id,u.name,GROUP_CONCAT(ut.tagid) as tags
FROM users u 
LEFT JOIN usertag as ut ON u.id = ut.userid 
LEFT JOIN tags t ON t.id=ut.tagid AND t.ID IN (10,20,30) --change this as needed
GROUP BY u.id,u.name
HAVING COUNT(ut.tagid) >= COUNT(t.id) AND COUNT(t.id) = 3 --change this number to the number of tags

如果值有限，则另一个选择是使用FIND_IN_SET.例如，

One more option is to use FIND_IN_SET if there are limited values. For example,

SELECT * FROM (
SELECT u.*, GROUP_CONCAT(ut.tagid) as tags 
FROM users as u 
LEFT JOIN usertag as ut ON u.id = ut.userid 
GROUP BY u.id
) T
WHERE FIND_IN_SET('10',tags) > 0 AND FIND_IN_SET('20',tags) > 0

这篇关于SQL标记列表和标记过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！