sql - 喜欢你的用户 (EXISTS) 但你没有与之聊天 (NOT EXISTS)

Question

如果您是 "t1".persona_1_id = 2，则预期结果应返回 persona_id = 4。

like
---
id persona_1_id persona_2_id liked
1  2            1            FALSE
2  3            1            TRUE
3  4            2            TRUE  -- 4 likes 2
4  2            4            TRUE  -- 2 likes 4
                                   -- (2 and 4 like each other)

chat_persona
---
id chat_id persona_id  -- but same chat has not been created between 2 and 4
1  1       3
2  1       2
3  2       4
4  2       1
5  3       5
6  3       1

-- so persona_id = 4 is the answer

我试图返回彼此喜欢的用户，他们之间还没有聊天。

“互相喜欢”有效，但我对“聊天尚不存在”进行了额外过滤:

SELECT DISTINCT
    "t1".id, "t1".read_at as read_at, "t1".created_at as created_at,
    "persona".id as persona_id, "persona".profile_id as profile_id, "persona".name as persona_name, "chat_persona".chat_id as chat_id, "chat_persona".id  as chat_persona_id
                    FROM "like" as "t1"
                        JOIN "persona" ON "t1".persona_2_id = "persona".id
                        JOIN "chat_persona" on "t1".persona_2_id = "chat_persona".persona_id
                    WHERE
                        "t1".persona_1_id = 2
                        AND EXISTS (
                            SELECT 1 
                            FROM "like" as "t2"
                            WHERE 
                                "t1".persona_1_id = "t2".persona_2_id 
                                AND "t1".persona_2_id = "t2".persona_1_id
                                AND "t2".liked = true
                        )
                        AND "t1".liked = true
                        AND "chat_persona".id IS NULL -- throws out the correct rows if ANY person chatted with them already... make sense

代替 AND "chat_persona".id IS NULL，也尝试过:

AND NOT EXISTS (
                            SELECT 1 
                            FROM "chat_persona" as "t2"
                            WHERE 
                                "t1".persona_1_id = "t2".persona_id 
                                AND "t1".persona_2_id = "t2".persona_id
                        ) -- doesn't throw out any rows

---

最终答案:

SELECT DISTINCT
    "l1".id, "l1".read_at as read_at, "l1".created_at as created_at,
    "persona".id as persona_id, "persona".profile_id as profile_id, "persona".name as persona_name
                    FROM "like" l1
                    JOIN "persona" ON "l1".persona_2_id = "persona".id
                    WHERE
                        "l1".persona_1_id = 2
                        AND "l1".liked = true
                        AND EXISTS (
                            SELECT 1 
                            FROM "like" l2
                            WHERE 
                                "l1".persona_1_id = "l2".persona_2_id 
                                AND "l1".persona_2_id = "l2".persona_1_id
                                AND "l2".liked = true
                        )
                        AND NOT EXISTS (
                            SELECT 1
                            FROM "chat_persona" c
                            WHERE c.persona_id IN ("l1".persona_1_id, "l1".persona_2_id) 
                            GROUP BY c.chat_id
                            HAVING count(*) = 2
                        )

Answer 1

我在想不存在，用一个子查询来检查两者是否在同一个聊天中:

select l.persona_1_id, l.persona_2_id
from l
where not exists (select 1
                  from chats c
                  where c.persona_id in (l.persona_1_id, l.persona_2_id)
                  group by c.chat_id
                  having count(*) = 2  -- both are in chat
                 );