Skip to content

Instantly share code, notes, and snippets.

@ejrh

ejrh/horrible.sql

Created October 1, 2018 02:12
Download ZIP

Revisions

  1. ejrh created this gist Oct 1, 2018.
    24 changes: 24 additions & 0 deletions horrible.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,24 @@

    WITH RECURSIVE t AS
    (
    SELECT id, '{}'::int[] AS parents, 0 AS level
    FROM z_msg WHERE in_reply_to IS NULL
    UNION ALL
    SELECT c.id, parents || c.in_reply_to, level+1
    FROM t JOIN z_msg AS s c ON t.id = c.in_reply_to
    ),
    t2 AS
    (
    SELECT c.in_reply_to, json_agg(jsonb_build_object('User', name, 'Comment', c.comment, 'Replies', '{}'::json[]))::jsonb AS js
    FROM t JOIN z_msg AS c ON t.id = c.id
    JOIN z_user ON z_user.id = c.user_id
    WHERE level > 0 AND NOT c.id = ANY(parents)
    GROUP BY c.in_reply_to
    UNION ALL
    SELECT c.in_reply_to, jsonb_build_object('Name', name, 'Comment', c.comment) || jsonb_build_object('Replies', js) AS js
    FROM t2 JOIN z_msg AS c ON c.id = t2.in_reply_to
    JOIN z_user ON z_user.id = c.user_id
    )
    SELECT jsonb_agg(js)
    FROM t2
    WHERE in_reply_to IS NULL;