SELECT node.category_id,node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
            FROM categories AS node,categories AS parent,categories AS sub_parent,
            (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM categories AS node,
                categories AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                AND node.category_id = :category_id
                GROUP BY node.name
                ORDER BY node.lft
             )AS sub_tree
             WHERE node.lft BETWEEN parent.lft AND parent.rgt
             AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
             AND sub_parent.name = sub_tree.name
             GROUP BY node.name
             ORDER BY node.lft;