sql - 在 postgres 中，如何执行仅返回两个多对多关系重叠的结果的查询？

Question

抱歉，标题令人困惑。情况真的没有那么复杂，但我很难用语言表达清楚!想象一个包含用户、角色和资源的模型。一个用户可以属于多个角色，一个资源也可以属于多个角色。如果我查询特定资源/userId(或者更重要的是，对资源的通用查询)，我只希望在用户与该资源处于所有相同角色时返回该资源。如果有任何匹配项，我就能得到结果，但我无法找出 sql 来完全按照我的意愿进行操作。

例如，一个包含几个用户的用户表:

     Table "public.users"
  Column  |  Type  | Modifiers 
----------+--------+-----------
 id       | bigint | 
 username | text   | 

postgres=# select * from users;
 id | username 
----+----------
  1 | foo
  2 | bar
(2 rows)

还有几个角色:

postgres=# \d roles;
      Table "public.roles"
  Column   |  Type  | Modifiers 
-----------+--------+-----------
 id        | bigint | 
 role_name | text   | 

postgres=# select * from roles;
 id | role_name 
----+-----------
  1 | roleOne
  2 | roleTwo
(2 rows)

用户一是两个角色:

postgres=# select * from user_role_join where user_id = 1;
 user_id | role_id 
---------+---------
       1 |       1
       1 |       2
(2 rows)

虽然用户二只是第一个角色:

postgres=# select * from user_role_join where user_id = 2;
 user_id | role_id 
---------+---------
       2 |       1
(1 row)

最后，我有一个分配给两个角色的资源..

postgres=# \d resource_role_join
Table "public.resource_role_join"
   Column    |  Type  | Modifiers 
-------------+--------+-----------
 resource_id | bigint | 
 role_id     | bigint | 

postgres=# 

postgres=# SELECT resource.name, resource.id, resource_role_join.role_id FROM resource INNER JOIN resource_role_join ON resource.id = resource_role_join.resource_id;
  name   | id | role_id 
---------+----+---------
 special |  1 |       1
 special |  1 |       2
(2 rows)

postgres=# 

这个查询当然会返回我的资源:

SELECT DISTINCT resource.id, resource.name
FROM resource
INNER JOIN resource_role_join ON resource.id = resource_role_join.resource_id
INNER JOIN user_role_join ON resource_role_join.role_id = user_role_join.role_id
WHERE user_role_join.user_id = 2 AND resource_id = 1;

但我正在尝试弄清楚如何构造我的查询，以便当我使用不在资源所属的所有角色中的 user_id 时，该资源将不会返回查询结果:

SELECT DISTINCT resource.id, resource.name
FROM resource
INNER JOIN resource_role_join ON resource.id = resource_role_join.resource_id
INNER JOIN user_role_join ON resource_role_join.role_id = user_role_join.role_id
WHERE user_role_join.user_id = 2 AND resource_id = 1;

我尝试将用户表本身添加到连接中:

SELECT DISTINCT resource.id, resource.name
FROM resource
INNER JOIN resource_role_join ON resource.id = resource_role_join.resource_id
INNER JOIN user_role_join ON resource_role_join.role_id = user_role_join.role_id
INNER JOIN users ON users.id = user_role_join.user_id
WHERE users.id = 2 AND resource_id = 1;

我开始怀疑我需要某种子查询。任何帮助是极大的赞赏!我知道我也可以在结果返回后检查角色成员资格，但我希望能够通过查询来限制它何时用户一次查询许多资源。

Answer 1

使用 not exists 也可以涵盖资源没有角色(=它是公共(public)的)的特殊情况。然后我们 left outer join 一切，看看我们是否获得了用户没有的资源角色。

SELECT DISTINCT
   r.id,
   r.name
FROM
   resource r
WHERE
   NOT EXISTS(
      SELECT
         *
      FROM
         resource_role_join rrj
         LEFT OUTER JOIN
            user_role_join urj
         ON
            urj.role_id = rrj.role_id AND
            urj.user_id = :USER_ID
      WHERE
         r.id = rrj.resource_id AND
         urj.role_id IS NULL
   )
;