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.

例如:获取标签为 Red (10) AND Green (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 |
    +---------+---------+----------+

另一个例子:获取标签为红色(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       |
    +---------+---------+----------+

另一个例子:获取标签为红色(10)、绿色(20)和蓝色(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

Here the SQL fiddle 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 表并在 join 子句中包含正在搜索的 id 并检查拥有.

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屋！