Calculate depth in parent-child model in MySQL

Question

How to calculate node depth in parent-child model in MySQL?

I will need depth to, by the way, create an indent in my list (encoded using PHP).

Answer 1

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

Answer 2

It depends on the actual implementation of your hierarchy in the database. If you are using a nested set model ( http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/) .

. , , node. node , node ( ):

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;

, node N, , id/parent_id, .
, node, node, , node - , .

Answer 3

, . PARENT_ID.

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 ;