So i have an article, and "comments" on the article..
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
Quick mockup of what the tables look like
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)
Is there a way to query the Postgres DB to give me a result of something like
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)
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?
Btw, i'm using GoLang
for my backend and the Gorm
package to access my postgres db
EDIT: I'm using this query
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 results to :
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: [...]
},
{
.....
}
]
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