Calculate depth in a parent-child model in MySQL

Question

How do I calculate a node's depth in a parent-child model under MySQL?

I'll need the depth to, among other things, create the indent in my list (coded with PHP).

I just saw your other question re: moving node in the nested set model and it looks like you've been given some very conflicting answers so I can understand your frustration :-) Moving the node in nested sets model is actually pretty straightforward (though depending on your table size it may be expensive). Let me know if you're still interested in that and I can add the answer with an explanation. — ChssPly76, Jul 28 '09 at 19:16
Haha that's right, I got pretty frustrated about that. I found a solution about a week ago I think should work, havn't implemented it yet though. But nested sets wouldn't work very well for my purposes since there probably will be many daily updates by many different users, by that means many rows in the table. For that reason I'll give this adjacency list model a shot, which safely could move a node. *thumbs up* — Ivar, Jul 28 '09 at 19:50
Good luck with your implementation. I've updated my answer to reflect adjacency list model. — ChssPly76, Jul 28 '09 at 20:22

ChssPly76 · Answer 1 · 2009-07-28T20:21:43.080

2

That depends on the actual implementation of your hierarchy in the database. If you are using nested sets model (http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/) you can retrieve the full parent-to-child path via a single select.

Update: Ok, since you're going with adjacency list model I suggest to store node level in the table. Not only will it give you the node depth in one query, but it will also allow you to retrieve the entire path to that node in one query (albeit that query would have to be dynamically generated):

SELECT n1.name AS lvl1, n2.name as lvl2, n3.name as lvl3, ..., nN.name as lvlN
  FROM nodes AS n1
  JOIN nodes AS n2 ON n2.parent_id = n1.id
  JOIN nodes AS n3 ON n3.parent_id = n2.id
  ...
  JOIN nodes AS nN ON nN.parent_id = n(N-1).id
WHERE nN.id = myChildNode;

Since you know that your node is on level N there's no need for left joins and, given appropriate indexes on id / parent_id this should be reasonably fast.
The downside to this approach is that you'll have to keep node level updated during node moves, but that should be reasonably straightforward and fast as you would only do it for the node itself and its children - not for the majority of the table as you would do with nested sets.

edited Jul 28 '09 at 20:21

answered Jul 28 '09 at 18:33

ChssPly76

99,456
24
206
195

Yeah, I've worked with nested sets and knows how to calculate the depth there, but now I need the depth from a parent-child model (obviously with just the columns id and parent), not nested set. – Ivar Jul 28 '09 at 18:37
By the way, it's the depth I'm interested of (an integer), not the path. – Ivar Jul 28 '09 at 18:42
1

You're talking about adjacency list model then. It's far from ideal, unfortunately - you will need N selects in order to fund out the depth of node on the Nth level. – ChssPly76 Jul 28 '09 at 18:43
Okay, so you'd suggest me to add another column to the table named depth? But I can't see you use it in your example. And in this case, would it be safe? I'd prefer a fast system that doesn't change multiple rows if possible. :) – Ivar Jul 28 '09 at 20:47
In my example, the depth is N and you're generating a single query with N joins. Without knowing depth in advance you will have to execute N queries to do the same thing. If you happen to know maximum possible depth of your entire hierarchy (e.g. it can't have more than X levels), another possible compromise is to rewrite the above query using X outer joins. It won't perform that well, but you won't have to keep node level for each node. – ChssPly76 Jul 28 '09 at 21:03

score 0 · Answer 2 · answered Feb 02 '16 at 08:38

If you want just to copy paste here is my example. I have table Projects with ID and PARENT_ID fileds.

DELIMITER $$
DROP FUNCTION IF EXISTS `getDepth` $$
CREATE FUNCTION `getDepth` (project_id INT) RETURNS int
BEGIN
    DECLARE depth INT;
    SET depth=1;

    WHILE project_id > 0 DO
        SELECT IFNULL(parent_id,-1) 
        INTO project_id 
        FROM ( SELECT parent_id FROM Projects WHERE id = project_id) t;

        IF project_id > 0 THEN
            SET depth = depth + 1;
        END IF;

    END WHILE;

    RETURN depth;

END $$
DELIMITER ;

score 0 · Accepted Answer · answered Nov 07 '10 at 14:04

0

This might be an old question, but I just want to let others know that I found a solution some months ago. I did recently write about it here: http://en.someotherdeveloper.com/articles/adjacency-list-model-with-depth-calculation/

answered Nov 07 '10 at 14:04

Ivar

4,344
6
38
53

Calculate depth in a parent-child model in MySQL

3 Answers3

Linked