mysql to get depth of record, count parent and ancestor records

Question

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!

Answer 1

If you're making a lot of queries like this, you may find that a nested set model is more appropriate than the adjacency list you're asking about. There's a good discussion of both models here.

In any event, to do what you're asking with an adjacency list you're looking at either recursion in the application layer, or storing the level as a 3rd column.

ETA: if your level count isn't terribly high, you can do it with self joins:

e.g. nodes with 2 ancestors:

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;