I have the following simplified database table structure for a legacy tickets-like system.
messages
id INT
parent_id INT
content TEXT
answer TEXT
...
On a list, I show all the messages. When a message is clicked, I display its answer etc.
The problem is, now I need to make a list structure of all parents and children related to this message, as well as the position of this message in the tree. How could I retrieve those from database?
I'm using Laravel, but raw SQL would also help me find the direction.
╔════╦═══════════╦════════════════════════╦═════════════════╗ ║ id ║ parent_id ║ content ║ answer ║ ╠════╬═══════════╬════════════════════════╬═════════════════╣ ║ 1 ║ NULL ║ Hi, I have a problem ║ I can't help ║ ║ 2 ║ 1 ║ The problem persists ║ Ok, what is it? ║ ║ 3 ║ 2 ║ Nevermind, I got this ║ Oh, well. ║ ║ 4 ║ 3 ║ Problem is back ║ Which problem? ║ ║ 5 ║ 4 ║ The same problem again ║ ... ║ ╚════╩═══════════╩════════════════════════╩═════════════════╝
When showing message with id = 4, I should be able to display something like this list:
Message history:
- Hi, I have a problem
- The problem persists
- Nevermind, I got this
- Problem is back
- The same problem again
I could only think of a loop and several SQL query executions, for each parent and child, which looks like a code smell.
As stated by Daan, this question seems like a duplicate for How to create a MySQL hierarchical recursive query.
I decided to not delete it however, since Ravan just answered it with a Laravel approach that helped me solve the problem, so I'll just leave this here for future reference.
Since you are doing hierarchical operations, you should use a strategy to save and retrieve this data from your database.
One approach is to use Nested Set Model, that can make it easier. Laravel has a great package that deals with it, called etrepat/baum, that also explains how it works and I quote:
An easy way to visualize how a nested set works is to think of a parent entity surrounding all of its children, and its parent surrounding it, etc. So this tree:
root
|_ Child 1
|_ Child 1.1
|_ Child 1.2
|_ Child 2
|_ Child 2.1
|_ Child 2.2
Could be visualized like this:
___________________________________________________________________
| Root |
| ____________________________ ____________________________ |
| | Child 1 | | Child 2 | |
| | __________ _________ | | __________ _________ | |
| | | C 1.1 | | C 1.2 | | | | C 2.1 | | C 2.2 | | |
1 2 3_________4 5________6 7 8 9_________10 11_______12 13 14
| |___________________________| |___________________________| |
|___________________________________________________________________|
The numbers represent the left and right boundaries. The table then might look like this:
id | parent_id | lft | rgt | depth | data
1 | | 1 | 14 | 0 | root
2 | 1 | 2 | 7 | 1 | Child 1
3 | 2 | 3 | 4 | 2 | Child 1.1
4 | 2 | 5 | 6 | 2 | Child 1.2
5 | 1 | 8 | 13 | 1 | Child 2
6 | 5 | 9 | 10 | 2 | Child 2.1
7 | 5 | 11 | 12 | 2 | Child 2.2
To get all children of a parent node, you
SELECT * WHERE lft IS BETWEEN parent.lft AND parent.rgt
To get the number of children, it's
(right - left - 1)/2
To get a node and all its ancestors going back to the root, you
SELECT * WHERE node.lft IS BETWEEN lft AND rgt
As you can see, queries that would be recursive and prohibitively slow on ordinary trees are suddenly quite fast. Nifty, isn't it?