|
If you're using Postgres, you can use recursive queries using common table expressions.
Disqus implemented this sort of thing 10 years ago: https://pastebin.com/Fe2twMRr (I can't find the original talk for some reason) Here's the meat of the solution: CREATE TABLE comments (
id SERIAL PRIMARY KEY,
message VARCHAR,
author VARCHAR,
parent_id INTEGER REFERENCES comments(id)
);
INSERT INTO comments (message, author, parent_id)
VALUES ('This thread is really cool!', 'David', NULL),
('Ya David, we love it!', 'Jason', 1), ('I agree David!', 'Daniel', 1),
('gift Jason', 'Anton', 2),
('Very interesting post!', 'thedz', NULL),
('You sir, are wrong', 'Chris', 5),
('Agreed', 'G', 5), ('Fo sho, Yall', 'Mac', 5);
What we’ve done now, is setup a basic comment model. We’ve included the message, the author, and the parent comment (which is optional). Now let’s learn how to use a recursive query to easily re-order this date, showing us a fully threaded view, sorted in ascending order by id. WITH RECURSIVE cte (id, message, author, path, parent_id, depth) AS (
SELECT id,
message,
author,
array[id] AS path,
parent_id,
1 AS depth
FROM comments
WHERE parent_id IS NULL
UNION ALL
SELECT comments.id,
comments.message,
comments.author,
cte.path || comments.id,
comments.parent_id,
cte.depth + 1 AS depth
FROM comments
JOIN cte ON comments.parent_id = cte.id
)
SELECT id, message, author, path, depth FROM cte
ORDER BY path;
|