Postgres无限自我加入 [英] Postgres infinite self join
问题描述
所以我有一篇文章，并对文章进行评论".
So i have an article, and "comments" on the article..
评论允许人们回复..您可以回复回复..依此类推，这意味着最深的树的根是N
the comment allows people to reply.. and you could reply to the reply.. so on and so forth, meaning the deepest tree root would be N
快速模拟表的外观
Comments(id, news_id, user_id, body, likes)
Replies(id, parent_id) --> id here is = Comments.id
User(id, username, password)
News(id, title, body, image)
有没有一种方法可以查询Postgres数据库，使我得到类似
Is there a way to query the Postgres DB to give me a result of something like
因此 Replies 表中任何具有null parent_id为空的内容都是主要"注释(aka不是答复).如果可能的话，如果 children 字段会在其内部填充(即答复的答复)
So anything inside the Replies table that has null parent_id is a "main" comment (aka isn't a reply).. I would love if possible if the children fields gets populated within itself (i.e. a reply of a reply)
使用Postgres甚至可能吗?还是我应该获取所有 Replys 和 Comments 一起加入它们，然后遍历每个试图找到合适的目的地?
Is this even possible with Postgres? Or Am i supposed to be fetching all Replies joining them with Comments and then iterating through each one trying to find it's proper desitination?
顺便说一句，我在后端使用 GoLang 和 Gorm 包访问我的postgres数据库
Btw, i'm using GoLang for my backend and the Gorm package to access my postgres db
我正在使用此查询
with recursive commentss as (
select r.id, r.parent, array[r.id] as all_parents,
c.body, u.username
from replies r
inner join comments c
on c.id = r.id
join users u
on u.id = c.user_refer
where (parent <> '') IS NOT TRUE
union all
select r.id, r.parent, c.all_parents || r.id,
co.body, u.username
from replies r
join comments co
on co.id = r.id
join users u
on u.id = co.user_refer
join commentss c
on r.parent = c.id
and r.id <> ALL (c.all_parents)
)
select * from commentss order by all_parents;
结果为:
距离更近..但是我需要的是返回一个看起来像
Which is a step closer.. however what i need is to have a JSON object returned looking like
comments: [
{
comment_id: ...,
username: ...,
comment_body: ....,
comment_likes: ....,
children: [...]
},
{
.....
}
]
comments 对象中的第一项应该是不是答复的注释，并且 children 字段应填充有已答复的注释. children 中的注释也应填充其 children 以回复该答复
Where the first items inside the comments object would be the comments that are NOT a reply, and the children field should be populated with the replied comments.. and the comments inside the children should also have their children populated to replies to that reply
推荐答案
希望这是您的预期结果.(我在这里做了类似的操作: https://stackoverflow.com/a/52076212/3984221 )
Hoping that this is your expected result. (I did something similar here: https://stackoverflow.com/a/52076212/3984221)
表评论 :
Table comments:
id body user_id likes
-- ------------ ------- -----
a foo 1 1
b foofoo 1 232
c foofoofoo 1 23232
d fooFOO 1 53
e cookies 1 864
f bar 1 44
g barbar 1 54
h barBAR 1 222
i more cookies 1 1
表回复
Table replies
id parent_id
-- ---------
a (null)
b a
c b
d a
e (null)
f (null)
g f
h f
i (null)
结果:
{
"comments": [{
"children": [],
"username": "Mike Tyson",
"comment_id": "i",
"comment_body": "more cookies",
"comment_likes": 1
},
{
"children": [{
"children": [],
"username": "Mike Tyson",
"comment_id": "b",
"comment_body": "foofoo",
"comment_likes": 232
},
{
"children": [{
"children": [],
"username": "Mike Tyson",
"comment_id": "c",
"comment_body": "foofoofoo",
"comment_likes": 23232
}],
"username": "Mike Tyson",
"comment_id": "d",
"comment_body": "fooFOO",
"comment_likes": 53
}],
"username": "Mike Tyson",
"comment_id": "a",
"comment_body": "foo",
"comment_likes": 1
},
{
"children": [],
"username": "Mike Tyson",
"comment_id": "e",
"comment_body": "cookies",
"comment_likes": 864
},
{
"children": [{
"children": [],
"username": "Mike Tyson",
"comment_id": "g",
"comment_body": "barbar",
"comment_likes": 54
},
{
"children": [],
"username": "Mike Tyson",
"comment_id": "h",
"comment_body": "barBAR",
"comment_likes": 222
}],
"username": "Mike Tyson",
"comment_id": "f",
"comment_body": "bar",
"comment_likes": 44
}]
}
查询:
递归 :
Recursion:
WITH RECURSIVE parent_tree AS (
SELECT
id,
NULL::text[] as parent_id,
array_append('{comments}'::text[], (row_number() OVER ())::text) as path,
rc.children
FROM replies r
LEFT JOIN LATERAL (SELECT parent_id, ARRAY_AGG(id) as children FROM replies WHERE parent_id = r.id GROUP BY parent_id) rc ON rc.parent_id = r.id
WHERE r.parent_id IS NULL
UNION
SELECT
r.id,
array_append(pt.parent_id, r.parent_id),
array_append(array_append(pt.path, 'children'), (row_number() OVER (PARTITION BY pt.parent_id))::text),
rc.children
FROM parent_tree pt
JOIN replies r ON r.id = ANY(pt.children)
LEFT JOIN LATERAL (SELECT parent_id, ARRAY_AGG(id) as children FROM replies WHERE parent_id = r.id GROUP BY parent_id) rc ON rc.parent_id = r.id
), json_objects AS (
SELECT c.id, jsonb_build_object('children', '[]'::jsonb, 'comment_id', c.id, 'username', u.name, 'comment_body', c.body, 'comment_likes', c.likes) as jsondata
FROM comments c
LEFT JOIN users u ON u.id = c.user_id
)
SELECT
parent_id,
path,
jsondata
FROM parent_tree pt
LEFT JOIN json_objects jo ON pt.id = jo.id
ORDER BY parent_id NULLS FIRST
唯一的递归部分位于CTE parent_tree 之内.在这里，我正在寻找父母并建立一条道路.此路径是以后在正确位置插入json数据所必需的.
The only recursion part is within CTE parent_tree. Here I am searching for the parents and build a path. This path is needed for inserting the json data later at the right position.
第二个CTE( json_objects )为每个注释构建一个json对象，并带有一个空的child数组，以后可以在其中插入孩子.
The second CTE (json_objects) builds a json object for each comment with an empty children array where the children can be inserted later.
LATERAL 联接在答复表中搜索当前ID的子代，并给出一个包含其ID的数组.
The LATERAL join searches the replies table for children of the current id and gives an array with their ids.
最后的 ORDER BY 子句很重要.这样可以确保所有较高的节点都位于较低的节点(它们的子节点)之前.否则，由于无法在适当的时候不存在必要的父项，因此全局json对象的输入稍后可能会失败.
The ORDER BY clause at the end is important. With this it is ensured that all upper nodes come before the lower nodes (their children). Otherwise the input into the global json object could fail later because a necessary parent could not exist at the right moment.
构建最终的JSON对象 :
Building the final JSON object:
CREATE OR REPLACE FUNCTION json_tree() RETURNS jsonb AS $$
DECLARE
_json_output jsonb;
_temprow record;
BEGIN
SELECT
jsonb_build_object('comments', '[]'::jsonb)
INTO _json_output;
FOR _temprow IN
-- <query above>
LOOP
SELECT jsonb_insert(_json_output, _temprow.path, _temprow.jsondata) INTO _json_output;
END LOOP;
RETURN _json_output;
END;
$$ LANGUAGE plpgsql;
不可能在递归中构建json对象，因为在查询中， jsondata 对象不是全局变量.因此，如果我在一个递归分支中将 b 作为子级添加到 a 中，则它将不存在于另一个分支中，在该分支中我将 c 添加为孩子.
It is not possible to build the json object within the recursion because within the query the jsondata object is not a global variable. So if I would add b as child into a in one recursion branch, it wouldn't exist in another branch where I would add c as child.
因此有必要生成一个全局变量.这可以在一个函数中完成.使用计算出的路径和子对象，将最终的json一起构建起来非常简单:遍历结果集并将json对象添加到全局对象的路径中.
So it is necessary to generate a global variable. This could be done in a function. With the calculated path and child objects it is really simple to build the final json together: looping through the result set and add the json object into the path of the global object.
这篇关于Postgres无限自我加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
