sql - SQL查询好友关系双方

Question

我的 Postgres 表是这样设置的:

CREATE TABLE "Friendships" (
    id integer DEFAULT PRIMARY KEY,
    "fromUserId" integer NOT NULL REFERENCES "Users"(id),
    "toUserId" integer NOT NULL REFERENCES "Users"(id)
);

当用户获取他们的好友时，我运行:SELECT * FROM FROM Friendships WHERE fromUserId=XXXX。

如何修改查询，以便根据 toUserId 是否也添加了该用户，将额外数据添加到结果(真/假)？

示例结果:

[
{ id: 444, fromUserId: 1, toUserId: 22, addedBack: false },
{ id: 445, fromUserId: 1, toUserId: 67, addedBack: true },
{ id: 446, fromUserId: 1, toUserId: 599, addedBack: true },
{ id: 447, fromUserId: 1, toUserId: 733, addedBack: false },
]

Answer 1

存在:

select f.*,
  exists (
    select 0 from "Friendships"
    where "toUserId" = f."fromUserId" and "fromUserId" = f."toUserId"
  ) addedBack
from "Friendships" f
where f."fromUserId" = 1

对于此示例数据:

INSERT INTO "Friendships"(id, "fromUserId", "toUserId") VALUES
(444, 1, 22), (445, 1, 67), (446, 1, 599), (447, 1, 733), 
(448, 67, 1), (449, 599, 1);

结果:

>  id | fromUserId | toUserId | addedback
> --: | ---------: | -------: | :--------
> 444 |          1 |       22 | f        
> 445 |          1 |       67 | t        
> 446 |          1 |      599 | t        
> 447 |          1 |      733 | f        

参见 demo .