0

I want to do a natural talkback where:

message

reply to message #1

reply to message #1#1

reply to message #2

another message

I've tried using the The Adjacency List Model here http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

But it's would give me something like

message

reply to message #1

reply to message #1#1

message

reply to message #2

another message

Notice 'message' tree is separate and not one like a natural talkback. I've tried doing a GROUP BY 'message' but it doesn't work, eliminating one of the 'message' tree altogether. I'm thinking I might need to explore something else.

Here's my query

$query = "SELECT DISTINCT t1.id AS lev1, 
                            t2.id AS lev2, 
                            t3.id AS lev3, 
                            t4.id AS lev14, 
                            t5.id AS lev5   
                        FROM post AS t1  
                        LEFT JOIN post AS t2 ON t2.parent_id = t1.id
                        LEFT JOIN post AS t3 ON t3.parent_id = t2.id 
                        LEFT JOIN post AS t4 ON t4.parent_id = t3.id 
                        LEFT JOIN post AS t5 ON t5.parent_id = t4.id 
                        GROUP BY lev1";
Mistergreen
  • 1,052
  • 1
  • 8
  • 16
  • a self-joined table, implemented not with a single query pretending mysql is recursive, but with a stored proc – Drew Nov 10 '15 at 17:25
  • the ddl (create/drop) of this is a slow point, that can be mitigated with sessions and not creates and drops, but here is a [Visual Here](http://stackoverflow.com/a/31967073) – Drew Nov 10 '15 at 17:30

0 Answers0