0

I' trying to retrieve nested comments, I'm able to get all replies recursively for single commend by below code.

SELECT id, name, parent_id
FROM comments
WHERE id = 2
UNION
SELECT id, name, parent_id
FROM (SELECT * FROM comments ORDER BY id) sort,
     (SELECT @pv := 2) init
WHERE find_in_set(parent_id, @pv)
AND length(@pv := concat(@pv, ',', id))

Above query works but it only returns specific id. I want to retrieve all comments like saying where post_id=1 but couldn't figure it out how we do it. I would be appreciate if anyone help.

EDIT: I want to get all comments belongs to a post and their replies recursively.

comments table has id,name,parent_id etc.. fields..

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Teoman Tıngır
  • 2,766
  • 2
  • 21
  • 41
  • Sample data and desired results, please. – Gordon Linoff Nov 14 '19 at 18:18
  • @GordonLinoff I want to get all comments belongs to a post and their replies recursively. – Teoman Tıngır Nov 14 '19 at 18:25
  • Are you using MySQL 8.0? It supports proper recursive queries, not the hackish solution you show. See docs and examples here: https://dev.mysql.com/doc/refman/8.0/en/with.html – Bill Karwin Nov 14 '19 at 18:31
  • Which `mySQL` version are you on @TeomanTıngır? – artemis Nov 14 '19 at 18:35
  • Hey @TeomanTıngır, does parent_id for reply comments only ? if that, then use simple get all the comments for a post `SELECT id, name, parent_id FROM comments WHERE id = 2` – James Dullat Nov 14 '19 at 18:44
  • @BillKarwin I know v8 has pretty cool methods for recursive queries but my script running on a shrared hosting which use 5.7 – Teoman Tıngır Nov 14 '19 at 21:35
  • You might like this answer of mine: https://stackoverflow.com/a/192462/20860 I show a solution for storing tree-like data in MySQL 5.x that doesn't require nasty queries with variables and stuff. I also did a presentation: https://www.slideshare.net/billkarwin/models-for-hierarchical-data and I cover it in my book: https://pragprog.com/book/bksqla/sql-antipatterns – Bill Karwin Nov 14 '19 at 22:10

0 Answers0