mysql to get depth of record, count parent and ancestor records
- by Nate
Hey All,
Say I have a post table containing the fields post_id and parent_post_id. I want to return every record in the post table with a count of the "depth" of the post. By depth, I mean, how many parent and ancestor records exist.
Take this data for example...
post_id parent_post_id
------- --------------
1 null
2 1
3 1
4 2
5 4
The 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!