专家的 SQL 嵌套计数

Question

当前代码:

SELECT "conversation"."id", "conversation"."title", "conversation"."deleted", "conversation"."created_at", (
        SELECT  COUNT("currentUserConversations->messageReceivers"."id")
        FROM "Conversations" AS "conversation"
        INNER JOIN "UserConversation" AS "currentUserConversations" ON "conversation"."id" = "currentUserConversations"."conversation_id"
        INNER JOIN "MessageReceiver" AS "currentUserConversations->messageReceivers" ON "currentUserConversations"."id" = "currentUserConversations->messageReceivers"."user_conversation_id" AND "currentUserConversations->messageReceivers"."read" = false
        WHERE "currentUserConversations"."user_id" = 7
        ) AS "unreadM" 
FROM "Conversations" AS "conversation" 
INNER JOIN "UserConversation" AS "otherUserConversations" ON "conversation"."id" = "otherUserConversations"."conversation_id" 
INNER JOIN "UserConversation" AS "currentUserConversations" ON "conversation"."id" = "currentUserConversations"."conversation_id" 
INNER JOIN "MessageReceiver" AS "currentUserConversations->messageReceivers" ON "currentUserConversations"."id" = "currentUserConversations->messageReceivers"."user_conversation_id"
WHERE "currentUserConversations"."user_id" = 7 
AND "otherUserConversations"."user_id" = '4' 
GROUP BY "conversation"."id";

我的表:

• 对话
• 用户对话
• 消息接收者

关系:

• N UserConversation 每个 Conversations
• 每个 UserConversation1 个 MessageReceiver

我要实现的目标:

• MessageReceiver 有一个read 列
• MessageReceiver.read 可以是 true/false
• 对于给定的查询(嵌套查询之外的所有内容)，我想计算有多少 MessageReceivers 将 read 列设置为 false(并在主查询中返回它别名为 unreadM)

当前发生的事情:

嵌套查询计算所有 MessageReceivers，即使它们的 read 属性设置为 true...

使用 PostgreSQL 方言。

如果可能的话，我希望能有一种不改变“FROM”的方法。我还需要能够过滤外部查询(有条件地，不是在 sql 中，而是从 express 中，根据外部条件生成查询)只检索至少有 1 条未读消息的对话 ("currentUserConversations->messageReceivers "."read"= false)

我的大脑爆炸了，如果有人有解决方案和解释，我将不胜感激!

预期结果:

id   title                         deleted   created_at              unreadM
4   "automaticSeeded title shvrn"   false   "2018-11-26 13:24:33.766355+01" "0"
7   "automaticSeeded title viowy"   false   "2018-11-26 13:24:33.766355+01" "4"

返回结果:

id   title                         deleted   created_at              unreadM
4   "automaticSeeded title shvrn"   false   "2018-11-26 13:24:33.766355+01" "4"
7   "automaticSeeded title viowy"   false   "2018-11-26 13:24:33.766355+01" "4"

示例数据:

用户对话

id|conversation_id|user_id|deleted|admin
1|10|1|f|f
2|10|10|f|f
3|9|2|f|f
4|9|9|f|f
5|8|3|f|f
6|8|8|f|f
8|7|7|f|f
10|6|6|f|f
11|5|6|f|f
12|5|5|f|f
13|4|7|f|f
14|4|4|f|f
15|3|8|f|f
16|3|3|f|f
17|2|9|f|f
18|2|2|f|f
19|1|10|f|f
20|1|1|f|f
7|7|4|f|f
9|6|7|f|f

消息接收者:

id|user_conversation_id|message_id|read
1|1|1|t
2|1|2|t
3|1|3|t
4|1|4|t
5|2|5|t
6|2|6|t
7|2|7|t
8|2|8|t
9|3|9|t
10|3|10|t
11|3|11|t
12|3|12|t
17|5|17|t
18|5|18|t
19|5|19|t
20|5|20|t
21|6|21|t
22|6|22|t
23|6|23|t
24|6|24|t
29|8|29|f
30|8|30|f
31|8|31|f
32|8|32|f
33|9|33|t
34|9|34|t
35|9|35|t
36|9|36|t
37|10|37|t
38|10|38|t
39|10|39|t
40|10|40|t
41|11|41|t
42|11|42|t
43|11|43|t
44|11|44|t
45|12|45|f
46|12|46|f
47|12|47|f
48|12|48|f
49|13|49|t
50|13|50|t
51|13|51|t
52|13|52|t
53|14|53|t
54|14|54|t
55|14|55|t
56|14|56|t
57|15|57|t
58|15|58|t
59|15|59|t
60|15|60|t
61|16|61|f
62|16|62|f
63|16|63|f
64|16|64|f
65|17|65|t
66|17|66|t
67|17|67|t
68|17|68|t
69|18|69|t
70|18|70|t
71|18|71|t
72|18|72|t
73|19|73|t
74|19|74|t
75|19|75|t
76|19|76|t
77|20|77|f
78|20|78|f
79|20|79|f
80|20|80|f
25|7|25|t
26|7|26|t
14|4|14|t
13|4|13|t
16|4|16|t
15|4|15|t
27|7|27|t
28|7|28|t

对话:

id|title|deleted|created_at
1|automaticSeeded title ijmmg|f|2018-11-26 13:24:33.766355+01
2|automaticSeeded title xdjiy|f|2018-11-26 13:24:33.766355+01
3|automaticSeeded title bmvpv|f|2018-11-26 13:24:33.766355+01
4|automaticSeeded title shvrn|f|2018-11-26 13:24:33.766355+01
5|automaticSeeded title yjvai|f|2018-11-26 13:24:33.766355+01
6|automaticSeeded title ubzab|f|2018-11-26 13:24:33.766355+01
7|automaticSeeded title viowy|f|2018-11-26 13:24:33.766355+01
8|automaticSeeded title ecthq|f|2018-11-26 13:24:33.766355+01
9|automaticSeeded title tzotl|f|2018-11-26 13:24:33.766355+01
10|automaticSeeded title fakjf|f|2018-11-26 13:24:33.766355+01

Answer 1

尚不完全清楚您要实现的目标，但您的子查询是独立的。这意味着对于 unreadM 中的所有行，您将始终获得相同的结果。

不过，我相信你想要以下内容

SELECT c."id", c."title", c."deleted", c."created_at", 
    Count(
        CASE WHEN mr."read" = false THEN 1 END
    ) AS "unreadM" 
FROM "Conversations" AS c 
INNER JOIN "UserConversation" AS ouc ON c."id" = ouc."conversation_id" 
INNER JOIN "UserConversation" AS uc ON c."id" = uc."conversation_id" 
INNER JOIN "MessageReceiver" AS mr ON uc."id" = mr."user_conversation_id"
WHERE uc."user_id" = 7 
AND ouc."user_id" = 4 
GROUP BY c."id";

如果你会做 count(*) + where read = false 而不是 count(case when read = false ...) 那么你会失去对话而没有未读消息。

此外，我会将输出中您想要的所有对话列包含到 GROUP BY 列表中。只是为了满足其他 DBMS 要求。

...
GROUP BY c."id", c."title", c."deleted", c."created_at";