Mysql to get record depth, count parent and ancestral records

Question

Say I have a column containing fields post_idand parent_post_id. I want to return each entry to the table column with the calculation of the "depth" of the message. In depth, I mean how many parent and ancestral records exist.

Take this data, for example ...

post_id   parent_post_id
-------   --------------
1         null
2         1
3         1
4         2
5         4

Data represents this hierarchy ...

1
|_ 2
|  |_ 4
|     |_ 5
|_ 3

The result of the query should be ...

post_id   depth
-------   -----
1         0
2         1
3         1
4         2
5         3

Thanks in advance!

Answer 1

, , , , . .

, , , , , .

ETA: , self:

. :

SELECT t1.node 
FROM mytable AS t1
JOIN mytable AS t2 ON t1.parent = t2.node
JOIN mytable AS t3 ON t2.parent = t3.node
WHERE t3.parent IS NULL;