sql - 喜欢你的用户(EXISTS)你没有聊天(NOT EXISTS)
问题描述
如果你是"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
)
解决方案
我在想not exists
,用一个子查询检查两者是否相同chat
:
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
);
推荐阅读
- asp.net-core - 应用程序/json 410 - 200 - 应用程序/json;+charset=utf-8 5732.4554ms ASP.NET CORE API
- javascript - 如何避免我的动画图像剪裁粘性标题?
- javascript - docs.forEach 不是函数
- java - com.crystaldecisions.sdk.occa.report.template 找不到
- javascript - 使用.then nodejs
- java - 如何在java中的反射中获取枚举对象
- mysql - 获取包含某个值的最新行以及在某个时间跨度内共享该值的所有行
- javascript - 什么是 gapi.client.init() 以及它与使用 HTTP 请求有何不同?
- html - (CSS) 想办法用 translateX 获取父元素的坐标
- ruby-on-rails - Rails 6:更改时自动重新加载本地 gem