PostgreSQL 9.6 中的社交網路評論和文章模型

我正在嘗試找出從 MongoDB 遷移到 PostgreSQL 的資料結構。在我posts的 Postgres 表中，我有一個JSONB[]數組，用於保存文章的評論。

問題是數組中的每個元素只包含id評論作者，而不是作者的實際使用者資訊。在該數組的每個元素上執行連接以替換作者id的數據的最佳方法是什麼，或者如果不是，那麼更好的方法是什麼？

例如，我的文章表行之一可能如下所示：

id: 5
author: 1
body: "Hello word"
comments: [{author:0, body: "It's me"}]

評論列是一個JSONB數組。

我的使用者表可能如下所示：

id: 0
username: Dux

我確實需要執行緒評論。

首先是作者表，這很容易。

CREATE TABLE author (
   authorid   serial  PRIMARY KEY,
   username   text    UNIQUE
);

現在我們使用一個自引用的 post 表來創建層次結構。

CREATE TABLE posts (
   id           serial  PRIMARY KEY,
   parent_post  int     REFERENCES posts,
   authorid     int     NOT NULL REFERENCES author,
   body         text    NOT NULL
);

測試數據…

INSERT INTO author (authorid,username) VALUES
 (0, 'ecarroll'),
 (1, 'jbob');
INSERT INTO posts (id,parent_post,authorid,body) VALUES
 (0, null, 0, 'HALLO WORLD'),
 (1 ,0,    1, 'HALLO EVAN' );

遞歸查詢使其工作。

WITH RECURSIVE t(chain,author_un,text,id) AS (
  SELECT ARRAY[id], username, body, id
  FROM posts
  INNER JOIN author USING (authorid)
  WHERE parent_post IS NULL
  UNION ALL
    SELECT t.chain||p.id, username, p.body, p.id
    FROM t
    INNER JOIN posts AS p
      ON (t.id = p.parent_post)
    INNER JOIN author AS a
      USING (authorid)
)
SELECT *
FROM t;

chain | author_un |    text     | id 
-------+-----------+-------------+----
{0}   | ecarroll  | HALLO WORLD |  0
{0,1} | jbob      | HALLO EVAN  |  1
(2 rows)

---

有關此方法的更多資訊，請參閱我的文章here

• 如何為支持回复的私人消息系統建構表？

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