0

I'm writing a very basic forum script in PHP as a learning exercise. Say I have a table that's looking like this (reduced to 3 columns for clarity): 

ID    PARENT    TITLE
1     null      Welcome to Post-Tools User Forum
2     1         Thank you!
3     2         Amen
4     null      EDL Loading Problems
5     4         EDL Loading Problems
6     4         EDL Loading Problems
7     null      Christmas Work Schedule
8     7         Christmas Work Schedule
9     1         Another Thank You!
10    3         I agree!
11    10        Idiots
12    1         Excellent tool.
13    3         Yup
14    2         I second that!
15    4         EDL Loading Problems

Say I want to SELECT the last 5 messages. But in order to have a complete thread for each of those new messages, I also need to iterate all the way UP the tree to find the highest parent for each of those 5 messages, and then crawl back down to get ALL children of those parents.

I can select the last 5 messages like so:

SELECT * FROM postsTable
ORDER BY id DESC
LIMIT 5

Which returns:

ID    PARENT    TITLE
15    4         EDL Loading Problems
14    2         I second that!
13    3         Yup
12    1         Excellent tool.
11    10        Idiots

Then I thought I could somehow JOIN that result with a second selection of all parents returned by the first query (ie, messages 4, 2, 3, 1 and 10).

But I cannot for the life of me make a join OR a union work once I've used the ORDER BY and/or LIMIT commands.

Furthermore, once I have the parents, I still need to do further queries to make sure I've found the highest order parent and any additional children and grandchildren of those parents. What I thought was going to be a moderately difficult test to learn mySQL has gotten much more complicated than expected.

Does anybody have any suggestions on how one would limit the initial query to the last 'X' messages, but then return all parents, siblings and cousins of those messages?

I think this is different than the previously answered hierarchal recursive query question, because we have no idea where in the tree we are, so we have to go both UP and DOWN the tree to complete the query. In addition, we potentially have MULTIPLE parents / siblings to account for.

ConleeC
  • 337
  • 6
  • 13
  • 1
    Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) – Madhur Bhaiya Nov 03 '18 at 07:04
  • But at some level, all messages are related, so how much of the tree do you actually want returned for a given node. A desired result set (not to mention proper DDLs) would be useful – Strawberry Nov 03 '18 at 08:11
  • I might be tempted to switch to a nested set model – Strawberry Nov 03 '18 at 08:12
  • Well in this simple sample, it would actually be almost the entire tree. But as more posts are made to the forum, the desired result would be the "X" most recent, with full hierarchies, ie all parents, siblings, and cousins. In other words, each message's full thread. – ConleeC Nov 03 '18 at 17:47

0 Answers0