多個 to_json(array_agg)，單獨連接

我正在建構一個消息傳遞系統，並嘗試獲取消息列表及其回复；並加入消息執行緒的參與者。

所以我正在拍攝messages = [{id, subject, body, replies: [..], users: [..]}]

SELECT messages.*, 
      to_json(array_agg(users)) users, 
      to_json(array_agg(replies)) replies
FROM messages
LEFT JOIN (SELECT id, fullname, email, company FROM users) users 
  ON users.id = messages.user_id OR users.id = messages.to
LEFT JOIN (SELECT * FROM messages ORDER BY created_at) replies 
  ON replies.message_id = messages.id
WHERE messages.to = :to OR (messages.to IS NOT NULL AND messages.user_id = :to)
GROUP BY messages.id;

我嘗試了各種其他查詢，這讓我最接近。問題是對於每條消息，所有回复都重複一次。即，如果一個執行緒中有 3 個回复，我得到 6 個（每個 2 個）。有什麼想法可能是錯的嗎？

我正在使用 Postgres 9.5。

基本問題是這樣的：

• 兩個 SQL LEFT JOINS 產生不正確的結果

這是避免此問題並改進其他一些細節的一種方法*：*

SELECT m.*, u.users, r.replies
FROM   messages m
LEFT   JOIN LATERAL (
  SELECT json_agg(u) AS users
  FROM   (
     SELECT id, fullname, email, company
     FROM   users
     WHERE  id IN (m.user_id, m.to)
     -- no order by?
     ) u
  ) u ON TRUE
LEFT   JOIN LATERAL (
  SELECT json_agg(r) AS replies
  FROM   (
     SELECT *
     FROM   messages
     WHERE  message_id = m.id
     ORDER  BY created_at
     ) r
  ) r ON TRUE
WHERE  :to IN (m.user_id, m.to)
AND    m.to IS NOT NULL;

• 使用json_agg()而不是to_json(array_agg()). 更簡單，更快。

• users立即在連接中聚合子查詢LATERAL，從而避免主要問題。有關的：

  • 從別名創建較小的行並保留列名

• 這：

WHERE  m.to = :to OR
     (m.to IS NOT NULL AND m.user_id = :to)

可以更簡單地表達：

WHERE  :to IN (m.user_id, m.to)
AND    m.to IS NOT NULL

• 這：

SELECT json_agg(r) AS replies
FROM   (
  SELECT *
  FROM   messages
  WHERE  message_id = m.id
  ORDER  BY created_at
  ) r

可以縮短為：

SELECT json_agg(r ORDER BY created_at) AS replies
FROM   messages r
WHERE  message_id = m.id

但是子查詢中的單個ORDER BY通常比 per-aggregate 更快ORDER BY。

引用自：https://dba.stackexchange.com/questions/129263