How can I create a recursive CTE query to get root parent_id SQL

Question

I have a table like this

Post

id: text pk not null
content: text not null
author_id: text fk references User(id) not null
parent_id: text fk references Post(id)

My question is, how can I get every post from a child post (by using its id) up to the root post (where parent_id is null) in Postgres?

Answer 1

I was able to do it with this query

WITH RECURSIVE parent_posts(id, parent_id, content, author_id) AS (
  SELECT id, parent_id, content, author_id 
  FROM "Post" c 
  WHERE id = 'b71fe331-12a0-4383-bb12-bff505db4016'
  UNION ALL
  SELECT p.id, p.parent_id, p.content, p.author_id
  FROM parent_posts po, "Post" p
  WHERE p.id = po.parent_id
)
select * from parent_posts