Postgresql
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