PostgreSQL: Combining these two queries

Question

My first query returns the first 10 comments, whose parent_id's are null

SELECT comment_id FROM comments WHERE thread_id = $1
AND parent_id is NULL 
ORDER BY upvoted DESC FETCH FIRST 10 ROW ONLY

How would I use each comment id, to perform a recursive query?

My current method is by storing the returned comment_id's in an array

[1, 2, 3, 4, 10, 14, 15, 18, 19, 20]

and then using a for loop to perform a recursive query on each id

var query = 

 `With RECURSIVE cte AS
 (
   SELECT * FROM comments WHERE comment_id = $1
   UNION
   SELECT t.*
   From comments t
   JOIN cte rt ON rt.comment_id = t.parent_id
 )
 SELECT * FROM cte`;


for(int i = 0; i < array.size(); i++){

   client.query(query, array[i])
   . . .
}

So I am wondering how I could do this in a single query instead of storing the ids in an array and then recursive querying each individual id?

user330315 · Accepted Answer · 2019-06-12 00:47:38Z

You can use your first statement as the non-recursive part of your recursive query. But due to the order by that you need, you need to put that query between parentheses:

With RECURSIVE cte AS
(
   (
    SELECT * 
    FROM comments 
    WHERE thread_id = $1
      AND parent_id is NULL 
    ORDER BY upvoted DESC 
    FETCH FIRST 10 ROW ONLY
   )
   UNION
   SELECT t.*
   From comments t
     JOIN cte rt ON rt.comment_id = t.parent_id
 )
 SELECT * FROM cte`

Alternatively you can do that in a separate CTE:

With RECURSIVE root_nodes AS
(
    SELECT * 
    FROM comments 
    WHERE thread_id = $1
      AND parent_id is NULL 
    ORDER BY upvoted DESC 
    FETCH FIRST 10 ROW ONLY
), cte as (
   select * 
   from root_nodes

   UNION

   SELECT t.*
   From comments t
     JOIN cte rt ON rt.comment_id = t.parent_id
)
SELECT * 
FROM cte;

Note the recursive keyword belongs to the WITH even when the first CTE is not the recursive one.

Collectives™ on Stack Overflow

PostgreSQL: Combining these two queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related