0

I got a self referencing table that's only 1 deep: comments and replies. A reply is just a comment with a parent id:

Comments (simplified):
    - comment_id
    - parentCommentId

Users have to scroll through the comments and replies and typically 10 new rows are being fetched each time, I'm trying out an recursive query for this:

WITH RECURSIVE included_childs(comment_id, parent_comment_id) AS 
    (SELECT comment_id, parent_comment_id
    FROM comments 
  UNION 
    SELECT c.comment_id, c.parent_comment_id
    FROM included_childs ch, comments c
    WHERE c.comment_id = ch.parent_comment_id
  )
SELECT *
FROM included_childs
limit 10

obviously because of limit 10 not all the childs are being included this way and conversations will be cut off. What I actually want is a limit on the parents and have all childs included, regardless of how many total rows.

update

This is the actual query, now with limit in the first branch:

WITH RECURSIVE included_childs(comment_id, from_user_id, fk_topic_id, comment_text, parent_comment_id, created) AS 
    ((SELECT comment_id, from_user_id, fk_topic_id, comment_text, parent_comment_id, created 
    FROM vw_comments WHERE fk_topic_id = 2
    and parent_comment_id is null
    limit 1)
  union all 
    SELECT c.comment_id, c.from_user_id, c.fk_topic_id, c.comment_text, c.parent_comment_id, c.created
    FROM included_childs ch, vw_comments c
    WHERE c.comment_id = ch.parent_comment_id
  )
SELECT *
FROM included_childs

still, this doesn't give me the expected results, I get 1 comment as a result with no replies.

update 2

silly mistake on the where clause:

WHERE c.comment_id = ch.parent_comment_id

should've been

WHERE ch.comment_id = c.parent_comment_id

it's working now.

Elger Mensonides
  • 6,930
  • 6
  • 46
  • 69

1 Answers1

1

I think the first branch in the UNION in the recursive CTE should be something like:

SELECT comment_id, parent_comment_id
FROM comments
WHERE parent_comment_id IS NULL
LIMIT 10

Then you'd get all replies for these 10 "root" comments. I'd expect some sort of ORDER BY in there, unless you don't care for the order.

UNION ALL would be fester than UNION, and there cannot be cycles, right?

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • When I limit in the first branch to 1 (with parentcommentid is null) I still get only 1 row? I simplified the query for the question, that's why I omitted the where clause. I'll update my answer. – Elger Mensonides Feb 17 '19 at 20:27
  • If that comment has no responses, yes. But maybe I misunderstood your question. – Laurenz Albe Feb 17 '19 at 20:33
  • nevermind, I see my mistake now (switched branch in the where clause, needed to be ch.comment_id = c.parent_comment_id) – Elger Mensonides Feb 17 '19 at 20:36