postgresql - 用于返回电子邮件对话列表的复杂查询

Question

我有一个复杂的查询在 sql fiddle 中不起作用.

在我工作的应用程序中，我们将用户 Gmail 与我们的数据库同步。我们将电子邮件存储在电子邮件表中，我们还有一个回复表，我们在其中存储引用 header ，其中列出了电子邮件的所有父回复。

例如，如果我有这样一封电子邮件:

id  | subject     | message_id
---------------------------------------------------------------------------------------------
1   | howzitgoin  | 53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail

replys表中不会有记录:

现在，如果我们像这样导入此电子邮件的回复:

    id  | subject     | message_id  
---------------------------------------------------------------------------------------------
    2   | RE: howzitgoin  | CAEBV8YTu_A6LtP_uGuQ-QSVj3zojWUiwcjGZpsPPEz1Pj3_i1A@mail.gmail.com

我们将在回复表中存储以下内容:

    email_id | message_id
------------------------------------------------------------------------------------------
       2     | 53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail

如果我们收到回复:

id  | subject     | message_id
---------------------------------------------------------------------------------------------
3   | RE: howzitgoin  | 53856b88a2a09_23fa9605badd01601b@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail

我们将在回复表中存储以下内容:

email_id | message_id
---------------------------------------------------------------------------------------------
   3     | 53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail
   3     | CAEBV8YTu_A6LtP_uGuQ-QSVj3zojWUiwcjGZpsPPEz1Pj3_i1A@mail.gmail.com

经过一番摸索，我想到了这个问题:

    WITH "ranked_replies" AS 
    (
        SELECT "r"."email_id", "r"."message_id", "rnk" 
        FROM (SELECT *, rank() OVER (PARTITION BY "message_id" ORDER BY "email_id" DESC) AS "rnk" FROM "replies") AS "r" 
        INNER JOIN "emails" 
        ON ("emails"."message_id" = "r"."message_id") 
    ), 
    "count_of_replies" AS 
    (
        SELECT "email_id", count(*) AS "count", count(*) AS "thread_count" 
        FROM "ranked_replies" 
        GROUP BY "email_id"
    ) 
    SELECT DISTINCT "emails".*, "thread_count" 
    FROM "emails" 
    LEFT JOIN "count_of_replies" 
    ON ("emails"."id" = "count_of_replies"."email_id") 
    WHERE 
    (
        ("folder" = 'INBOX') 
        AND 
        (
            ("emails"."message_id" NOT IN (SELECT "message_id" FROM "ranked_replies" WHERE ("rnk" != 1))) 

            OR ("emails"."message_id" IS NULL)
        ) 
        AND ("emails"."id" NOT IN (SELECT "email_id" FROM "ranked_replies" WHERE ("rnk" != 1)))
    ) 
    ORDER BY "created_at" DESC LIMIT 50 OFFSET 0

问题是它没有返回主题为“不返回”的电子邮件线程。

之所以会这样，是因为where子句的这一部分:

("emails"."message_id" NOT IN (SELECT "message_id" FROM "ranked_replies" WHERE ("rnk" != 1))

这排除了主题为“不返回”的根电子邮件，因为它在排名为 1 和 2 的 ranked_replies 中有 2 行。

我想要一个查询:

1. 显示没有回复的电子邮件(例如，不在线程中)
2. 显示每个线程的顶端，如果每个线程存在多个末端节点我只想要一个。
3. 仅显示当前文件夹(收件箱)中的电子邮件。

引用 SQLFiddle 示例:它应该返回电子邮件:#5(线程 1 的最高排名)、#8(线程 2 的最高排名)、#9(不在线程中)和#10(非最高排名) ，但收件箱中只有一个线程)

我在使用 #10 时遇到了问题。

Answer 1

“未返回”的邮件没有返回，因为顶部的有回复，而底部的不在“收件箱”中。

作为旁节点:由于您将其作为“应用程序”，我想您可以通过将所有(至少直到特定数量)邮件的元数据传输到客户端并在那里进行排序/过滤来增强它。这很可能比让数据库根据用户群等进行所有思考更好地扩展(并且在用户体验方面更快)。回到你的问题:

我很不确定你为什么要为此使用 rank()，所以我放弃了它。如果你因为我忽略的其他要求而想继续使用它，你可以这样做:在“回复”的子选择上使用排名，它只处理回复在当前文件夹中的行。

也许您想在我的解决方案中跳过“threadid”的选择，并通过首先插入这个 id 来解决这个问题。或者您自己为每个线程创建一个唯一的 ID。

由于我不知道 gmail 显示邮件的方式，我假设您需要以下内容:

1. 显示没有回复的电子邮件(例如，不在线程中)
2. 显示每个线程的顶端，如果每个线程存在多个末端节点，我假设您只需要一个。
3. 只关心当前文件夹中的电子邮件。

我创建了 this SQL Fiddle那样做。在那里我还更改了数据库模型以引用由其主键 (id) 而不是其 message_id 回复的消息。因为这是一个数字序列，所以它可以用来寻址线程树，我也这样做了。

这是解决方案:

WITH "thread" AS 
( -- select the uppermost id per thread
    SELECT r."email_id", min("reply_to_id") AS "threadid"
    FROM "replies" r
    INNER JOIN "emails" e ON r.email_id = e.id 
    -- create tree only for current folder
    AND e.folder = 'INBOX'
    GROUP BY r."email_id"
),
"lastmail" AS
( -- select the highest email per thread
  SELECT t."threadid", max(r."email_id") AS "lastmail"
  FROM replies r
  INNER JOIN thread t ON t.threadid = r.reply_to_id
  GROUP BY t."threadid"
),
"count_of_replies" AS
(SELECT r."email_id", count(r.*) AS "thread_count"
 FROM replies r
  INNER JOIN thread t ON t.threadid = r.reply_to_id
  GROUP BY r."email_id")
SELECT DISTINCT "emails".*, "thread_count"
FROM "emails" 
LEFT JOIN "count_of_replies" 
ON ("emails"."id" = "count_of_replies"."email_id") 
WHERE 
(
   -- only from current folder
    ("folder" = 'INBOX') AND
    (
      -- the ones that are in no thread
      ("emails"."id" NOT IN (SELECT "email_id" FROM "thread"
                             UNION ALL
                             SELECT "threadid" from "thread"))
      OR
      -- the ones that are top in their thread
      ("emails"."id" IN (SELECT "lastmail" FROM "lastmail"))
    )
) 
ORDER BY "created_at" DESC LIMIT 50 OFFSET 0