CapBul's questions

Tenho uma tabela de comentários com id, parent_id, content, created_at e outros campos. Quero poder selecionar primeiro os comentários raiz (ou seja, comentários em que o parent_id é NULL) e limitá-los aos 5 mais recentes. Em seguida, para cada comentário raiz selecionado, quero limitar o número de comentários filhos de segundo nível a 5. E assim por diante para todos os níveis mais profundos. O problema que estou enfrentando é como limitar os comentários filhos em todos os níveis da recursão sem varrer a tabela inteira. Até agora, só consegui fazer isso para os comentários de nível superior usando uma solução alternativa. Como posso adicionar a essa consulta recursiva para que ela respeite a regra de 5 filhos por pai durante a recursão sem varrer a tabela inteira e filtrar depois?

O esquema da tabela e os dados de origem têm a seguinte aparência:

-- create
CREATE TABLE comments (
    id         INT NOT NULL PRIMARY KEY,
    user_id    BIGINT NOT NULL,
    post_id    BIGINT NOT NULL,
    parent_id  INT DEFAULT NULL,
    content    VARCHAR(10000) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_comments_parent FOREIGN KEY (parent_id) 
      REFERENCES comments(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Root Comments (Level 1)
INSERT INTO comments (id, user_id, post_id, parent_id, content, created_at) VALUES
(1, 101, 1, NULL, 'Root Comment 1', '2025-04-10 10:00:00'),
(2, 102, 1, NULL, 'Root Comment 2', '2025-04-10 10:05:00'),
(3, 103, 1, NULL, 'Root Comment 3', '2025-04-10 10:10:00'),
(4, 104, 1, NULL, 'Root Comment 4', '2025-04-10 10:15:00'),
(5, 105, 1, NULL, 'Root Comment 5', '2025-04-10 10:20:00'),
(18, 105, 1, NULL, 'Root Comment 6', '2025-04-11 10:20:00');

-- Second-level Comments (Level 2) for Root Comments 1, 2, and 3
INSERT INTO comments (id, user_id, post_id, parent_id, content, created_at) VALUES
(6, 106, 1, 1, 'Second-level Comment 1 (Child of Root 1)', '2025-04-10 10:25:00'),
(7, 107, 1, 1, 'Second-level Comment 2 (Child of Root 1)', '2025-04-10 10:30:00'),
(8, 108, 1, 2, 'Second-level Comment 3 (Child of Root 2)', '2025-04-10 10:35:00'),
(9, 109, 1, 3, 'Second-level Comment 4 (Child of Root 3)', '2025-04-10 10:40:00'),
(10, 110, 1, 3, 'Second-level Comment 5 (Child of Root 3)', '2025-04-10 10:45:00');

-- Third-level Comments (Level 3) for second-level comments
INSERT INTO comments (id, user_id, post_id, parent_id, content, created_at) VALUES
(11, 111, 1, 6, 'Third-level Comment 1 (Child of Second-level 1)', '2025-04-10 10:50:00'),
(12, 112, 1, 6, 'Third-level Comment 2 (Child of Second-level 1)', '2025-04-10 10:55:00'),
(13, 113, 1, 7, 'Third-level Comment 3 (Child of Second-level 2)', '2025-04-10 11:00:00'),
(14, 114, 1, 8, 'Third-level Comment 4 (Child of Second-level 3)', '2025-04-10 11:05:00'),
(15, 115, 1, 9, 'Third-level Comment 5 (Child of Second-level 4)', '2025-04-10 11:10:00');

-- Fourth-level Comments (Level 4) for third-level comments
INSERT INTO comments (id, user_id, post_id, parent_id, content, created_at) VALUES
(16, 116, 1, 11, 'Fourth-level Comment 1 (Child of Third-level 1)', '2025-04-10 11:15:00'),
(17, 117, 1, 12, 'Fourth-level Comment 2 (Child of Third-level 2)', '2025-04-10 11:20:00');

E a consulta que escrevi, que busca apenas os 5 comentários raiz mais recentes e todos os seus filhos:

WITH RECURSIVE comment_tree AS (
    -- Base case: Selecting the root comments
    SELECT id, user_id, parent_id, content, created_at, 1 AS level
    FROM comments
    WHERE post_id = 1 AND parent_id IS NULL
    AND id IN (SELECT * FROM (
        SELECT id FROM comments 
        WHERE post_id = 1 AND parent_id IS NULL
        ORDER BY created_at DESC
        LIMIT 5)temp_tab)
    
    UNION ALL

    -- Recursive case: Selecting child comments
    SELECT c.id, c.user_id, c.parent_id, c.content, c.created_at, ct.level + 1 AS level
    FROM comments c
    JOIN comment_tree ct ON c.parent_id = ct.id
    -- Limiting the recursion to some level
    WHERE ct.level < 4
)

SELECT * FROM comment_tree
ORDER BY created_at;

Tabela do resultado desejado (colunas irrelevantes foram omitidas, cada parent_id tem no máximo 5 filhos):