On 2019/05/07 7:57 PM, Thomas Zimmermann wrote:
Hi!
Sometimes it is desirable to limit the size of the queue¹ in a
recursive CTE//...
CREATE TABLE comment (
comment_id INTEGER PRIMARY KEY,
parent_comment_id INTEGER REFERENCES comment (comment_id),
created_at INTEGER NOT NULL -- timestamp, bigger means newer
);
CREATE INDEX comment_hierarchy ON comment (parent_comment_id,
created_at DESC);
WITH RECURSIVE //...
I would be very interested in a general solution that still allows for
the adjacency list design,
but I'm open to denormalization. :)
It's tricky, but there is a solution now that Window-functions have
joined the fray.
Essentially what we need is to first extract from the table a list of
all the sub comments by parent comment, but while the query order is not
helpful, we can partition by the parent-comment-ID's and number the rows
(which we CAN apply a sort-order to thanks to the Window function
methodology), so the first CTE does just that.
The next step is to list the origin rows (which have parent_id = NULL)
and then amend to them every sub-comment belonging to them, but only
where the created row-order ID is less than 100 (or whatever value you
pick).
Lastly, we only use full sorting order in the very final query (outside
the CTE's) according to main comment dates and sub-comment id's, which
would make things fastest. This solution will work universally for all
similar types of tree-queries.
While this compiles/runs, I have not been able to test this with data
because you gave no data and I was too lazy to make up data, but I'm
pretty sure it would work. If it doesn't, please send some data and
expected outcome, then we can fix it.
WITH RECURSIVE
comments_by_parent(subid, comment_id, parent_comment_id,
created_at) AS (
SELECT ROW_NUMBER() OVER (PARTITION BY c.parent_comment_id
ORDER BY c.created_at DESC) AS subid,
c.comment_id, c.parent_comment_id, c.created_at
FROM comment AS c
WHERE c.parent_comment_id IS NOT NULL
), sorted_comment(comment_id, created_at, sub_comment_id,
sub_created_at, depth, idx) AS (
SELECT comment_id, created_at, NULL, NULL, 0, 0
FROM (SELECT comment_id, created_at
FROM comment
WHERE parent_comment_id IS NULL
ORDER BY created_at DESC
LIMIT 100
)
UNION ALL
SELECT sc.comment_id, sc.created_at, cp.comment_id,
cp.created_at, sc.depth + 1, cp.subid
FROM sorted_comment AS sc
JOIN comments_by_parent AS cp ON cp.parent_comment_id =
sc.comment_id
WHERE cp.subid < 100
)
SELECT comment_id, created_at, sub_comment_id, sub_created_at, depth, idx
FROM sorted_comment
ORDER BY created_at, comment_id, idx
;
Good luck!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
