php - 具有多对多关系的Mysql group_concat

Question

我有三张 table

+-------------+  +-----------------------------+  +-----------+
| WORKSPACES  |  | USERS_WORKSPACES            |  | USERS     |
+-----+-------+  +----+---------+--------------+  +----+------+
| id  | title |  | id | user_id | workspace_id |  | id | name |
+-----+-------+  +----+---------+--------------+  +----+------+
|  1  |  W1   |  | 1  |   1     |     1        |  |  1 |  U1  |
|  2  |  W2   |  | 2  |   1     |     2        |  |  2 |  U2  |
|  3  |  W3   |  | 3  |   2     |     2        |  |  3 |  U3  |
+-----+-------+  +----+---------+--------------+  +----+------+

我正在尝试查询数据库以列出给定 user_id 的唯一工作区。对于返回的每个工作区，我想要一组唯一的用户 ID。像这样:

对于 user_id = 2

+--------------+-----------------+-------+
| workspace_id | workspace_title | users |
+----------------------------------------+
| 1            | W1              | 1     |
+----------------------------------------+
| 2            | W2              | 1,2   |
+----------------------------------------+

对于 user_id = 2

+--------------+-----------------+-------+
| workspace_id | workspace_title | users |
+----------------------------------------+
| 2            | W2              | 1,2   |
+----------------------------------------+

通过这个查询，我可以列出所有工作区的唯一用户 ID。如何将列表限制为仅与指定 user_id 关联的工作区？

SELECT w.id,
       w.title,
       group_concat(b.user_id) as users
FROM   workspaces w, 
       users_workspaces b,
       users u
WHERE  w.id = b.workspace_id 
  AND  b.user_id = u.id
GROUP BY w.id

Answer 1

您的查询在功能上看起来是正确的。但是，您不需要加入users 表，因为您可以从users_workspaces 中获取user_id。并且，您应该使用正确的连接语法:

select w.id, w.title, group_concat(uw.user_id) as users
from workspaces w join 
     users_workspaces uw
     on w.id = uw.workspace_id 
group by w.id;

如果您想将其限制在与特定用户 ID 关联的工作区，则使用 having 子句:

select w.id, w.title, group_concat(uw.user_id) as users
from workspaces w join 
     users_workspaces uw
     on w.id = uw.workspace_id 
group by w.id
having sum(uw.user_id = @USERID) > 0;