SQL Querying for Threaded Messages

Question

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

Answer 1

You can use a temporary ID to order the messages. If the message is a Parent then the temporary ID will be equal to the ID, else the temporary ID will be equal to the ParentMessageID. Then you just need to order by the temporary ID

SELECT Messages.*, 
CASE WHEN ParentMessageId IS NULL THEN Id ELSE ParentMessageId END AS tempId 
FROM Messages
ORDER BY tempId

Edit

If you want the first 10 records you could get the Ids first and then run the query

SELECT Messages.*, 
CASE WHEN ParentMessageId IS NULL THEN Id ELSE ParentMessageId END AS tempId 
FROM Messages
WHERE Messages.tempId IN (SELECT Messages.Id 
                      FROM Messages
                      WHERE ParentMessageId IS NULL
                      LIMIT 10
                      ORDER BY Messages.Id )
ORDER BY tempId

This way you only get the messages and respective childs from the first 10 messages.

Answer 2

Try this. You can replace the range_ using some variable kept on your front-end for pagination.

select child.MessageText from
(select @i:=@i+1 as range_, id, MessageText from messages, (select @i:=0) k where ParentMessageId is null order by id asc) parent 
left outer join messages child on (parent.id = child.ParentMessageId or parent.id = child.id)
where parent.range_ between 1 and 3;