SQL Querying for Threaded Messages
- by Harper
My site has a messaging feature where one user may message another. The messages support threading - a parent message may have any number of children but only one level deep.
The messages table looks like this:
Messages
- Id (PK, Auto-increment int)
- UserId (FK, Users.Id)
- FromUserId (FK, Users.Id)
- ParentMessageId (FK to Messages.Id)
- MessageText (varchar 200)
I'd like to show messages on a page with each 'parent' message followed by a collapsed view of the children messages.
Can I use the GROUP BY clause or similar construct to retrieve parent messages and children messages all in one query? Right now I am retrieving parent messages only, then looping through them and performing another query for each to get all related children messages.
I'd like to get messages like this:
Parent1
Child1
Child2
Child3
Parent2
Child1
Parent3
Child1
Child2