Finding depth in a list model

MYSQL has no CTEs nor recursion functions. You can solve it with a "classic loop":

DELIMITER $$
DROP FUNCTION IF EXISTS figure_up_depth $$

--encapsulated as a function:
CREATE FUNCTION figure_up_depth(idToSearch INT) RETURNS INT BEGIN

 --var to count steps from node to root:
 DECLARE depth INT;
 SET depth = 1;

 --while we are not on root node:
 WHILE ( idToSearch  is not  null) DO

   SET idToSearch = ( select parent
     from category
     where category_id = idToSearch
                  );

   SET depth = depth + 1;

 END WHILE;

 RETURN depth;

END;
$$
DELIMITER ;

      

        
        
        
      

    

Check:

mysql> select figure_up_depth(7);
+--------------------+
| figure_up_depth(7) |
+--------------------+
|                  4 |
+--------------------+
1 row in set (0.00 sec)

      

        
        
        
      

    

EDITED July 8, 2015

I understand that the OP is asking for the max depth , not the node depth. Easy:

mysql> select max( figure_up_depth(category_id) ) as max_depth
       from category;

      

        
        
        
      

    

If performance matters , the correct solution is:

• Place everything categories.parent
  
  in a temporary table table1
  
  .
• Join table1 to categories with categories.id = table1.id
  
  , place the result in the combined field categories.parent
  
  in a temporary table table2
  
  .
• Drop table1
  
  and rename table2
  
  as table1
  
  .
• Go to step 2 if it table1
  
  has strings.

The number of iteration cycles will be the depth of the tree:

DELIMITER $$
DROP PROCEDURE IF EXISTS letsgo;
CREATE PROCEDURE letsgo() BEGIN
 DECLARE R int;
 DECLARE D int;
 SET D=0;
 DROP TEMPORARY TABLE IF EXISTS children;
 CREATE TEMPORARY TABLE children AS (SELECT category_id as id 
                                       FROM category 
                                      WHERE parent is NULL );
 DROP TEMPORARY TABLE IF EXISTS children_prev;
 CREATE TEMPORARY TABLE children_prev AS (SELECT * FROM children );
 SET R = ( SELECT count(*) FROM children ); 
 WHILE ( R > 0 ) DO
   DROP TEMPORARY TABLE IF EXISTS children_aux;
   CREATE TEMPORARY TABLE children_aux AS (
     SELECT category_id as id 
       FROM category R  
       INNER JOIN  children_prev C on C.id = R.parent
   );
   SET R = ( SELECT count(*) FROM children_aux );  
   INSERT INTO children 
      SELECT * FROM children_aux;
   TRUNCATE TABLE children_prev;
   INSERT INTO children_prev
      SELECT * FROM children_aux;   
   SET D=D+1;
 END WHILE;
 SELECT D;
END;
$$
DELIMITER ;

      

        
        
        
      

    

Testing:

mysql> call letsgo();
+------+
| D    |
+------+
|    4 |
+------+
1 row in set (0.14 sec)

      

        
        
        
      

    

Note: sorry for the dirty solution, this is mysql: no CTE, no recursion, no selection in function recursion, not DO-While, ...