Get messages in a given trail

Question

The following is a simplified table structure showing messages belonging to a single mail trail. i.e. the trail started with messageID 46 (where parentMessageID = 0). Message 47 is a reply to message 46. Message 89 is a reply to message 47.

tblMessages

messageID     parentMessageID
-----------------------------
46            0
47            46
89            47

The table would obviously have thousands of message records.

How would you query the table to get all messages in a particular trail, e.g. the trail shown above?

So basically, this is a conversation, starting with `parentMessageID == 0`? — BenM, Jul 24 '13 at 10:16
Wouldn't it be better to store a conversation identifier? The problem you're going to have is that you have no indication of the depth level, and so trying to loop over is almost impossible. It would be more logical to set the `parentMessageID` to the very root (i.e. where its `parentMessageID == 0`), and then order them by date. — BenM, Jul 24 '13 at 10:18
In this case you have to use a recursive query. Just google 'recursive query in MySQL'. [Here is one example how to do it](http://stackoverflow.com/questions/10646833/using-mysql-query-to-traverse-rows-to-make-a-recursive-tree) — valex, Jul 24 '13 at 10:32
Yeah, I thought that would present problems. Make that an answer if you like. — user460114, Jul 24 '13 at 10:32
There's no need to prefix questions with tags, the tag system takes care of that. Please read http://meta.stackexchange.com/q/19190/147072 for more information. — Patrick, Jul 24 '13 at 10:37

score 1 · Accepted Answer · answered Jul 24 '13 at 10:41

Wouldn't it be better to store a conversation identifier?

The problem you're going to have is that you have no indication of the depth level, and so trying to loop over is almost impossible. It would be more logical to set the parentMessageID to the very root (i.e. where its parentMessageID == 0), and then order them by date.

Get messages in a given trail

1 Answers1