Procedimientos almacenado subniveles infinitos

He aquí el procedimiento almacenado:

DELIMITER $$
DROP PROCEDURE IF EXISTS get_childs_sub$$
CREATE PROCEDURE get_childs_sub (IN nid INT)
BEGIN
DECLARE n INT;
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT id FROM posts WHERE replyto = nid;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;
get_childs_fetch_loop: LOOP
 FETCH cur INTO n;
 IF done = 1 THEN
  LEAVE get_childs_fetch_loop;
 END IF;
 INSERT INTO __childs VALUES (n);
 CALL get_childs_sub(n);
END LOOP get_childs_fetch_loop;
CLOSE cur;
END $$

DROP PROCEDURE IF EXISTS get_childs$$
CREATE PROCEDURE get_childs (IN nid INT)
BEGIN
DECLARE n INT;

DROP TEMPORARY TABLE IF EXISTS __childs;
CREATE TEMPORARY TABLE __childs (id INT NOT NULL PRIMARY KEY);
SELECT COUNT(*) FROM posts WHERE replyto = nid INTO n;

IF n <> 0 THEN
 CALL get_childs_sub(nid);
END IF;

SELECT posts.id,
  posts.text,
  posts.replyto,
  JOIN __childs USING(id)
  WHERE posts.status = 'Activo' ORDER BY posts.date ASC;
END $$

Y para llamar al procedimiento desde el código:

CALL get_childs(ID_del_que_queremos)

De nuevo, esto por simple que parece trae consigo 2 nuevos problemas. El primero es que un procedimiento almacenado se puede llamar desde PHP solamente si se utiliza la extensión MySQLi, es decir que con el método común:

   $data = mysql_query("CALL get_childs(2403)");
?>

PHP devolverá un error acerca de que no es posible esta llamada dentro del contexto. De todos modos recomiendo utilizar siempre la extensión MySQLi ya que es mucho más rápida y brinda acceso a una gama más amplia de funciones para interactur con la base de datos. MySQLi tiene la i por improved que quiere decir mejorado en castellano, es decir, es la extensión mejorada de PHP para MySQL.

Por otro lado lo que el procedimiento almacenado hace es básicamente extraer todas las repuestas bajo un determinado ID e insertarlas en una tabla temporal (en mi caso llamada __childs) y luego simplemente hace un JOIN de esa tabla con la tabla real que contiene las entradas. Lo que obtendríamos como resultado sería esto:

ID          Respuesta a
1           NULL
2           1
3           NULL
4           2
5           2
6           1
7           3

Esto nos devuelve exactamente las respuestas (y sub-respuestas) que queremos para una determinada entrada, pero lo hace en el orden que las selecciona de las tablas con el JOIN y eso no es lo que queremos ya que lo que interesa es obtener una lista de respuestas y sub-respuestas a manera de jerarquía, es decir algo así:

ID          Respuesta a
1           NULL
  2           1
     4           2
     5           2
  6           1
3           NULL
  7           3

En donde NULL es una respuesta a la raíz y luego las demás son todas las sub-respuestas en infinitos niveles.

Como a todo problema en la vida hay una solución (menos a la muerte) a esto también se le encuentra fácil solución y es que una vez hayamos obtenido el resultado de llamar al procedimiento almacenado, lo siguiente es una pequeña función recursiva (que se llama a si misma) dentro del código que se encarga de “ordenar” el resultado, colocando a cada sub-respuesta debajo de la respuesta a la que corresponde. La función es algo así como esto:

 public function sortReplyChilds($parent, $data, $newdata, $level = 0)
  {
  foreach($data as $item) {
   if ($item['replyto'] != $parent) {
    continue;
   }

   $level++;
   $item['sub_level'] = $level;
          $newdata[] = $item;
          $this->sortReplyChilds($item['id'], $data, &$newdata, $level);
          $level--;
   }
  }

    $newdata = array();
    $this->sortReplyChilds(10, $data, &$newdata);

?>

Y para explicar un poco la función:

- $newdata es el array que va a contener el resultado ordenado. Se tiene que pasar por referencia de modo que se pueda utilizar (mostrar) luego. Si no se pasa por referencia PHP inicia un array nuevo con cada iteración, de modo que el resultado será incompleto.
- $data es el array que contiene el resultado del procedimiento almacenado (sin ordenar).
- $level es el nivel al que pertenece cada respuesta, esto no tiene más uso que luego utilizar dicho valor en el método de JavaScript para que sangre (los lleve hacia adentro) o no los sub-niveles.
- El primer parámetro que recibe la función es el ID de la raíz, desde ahí hacia abajo se ordenarán los resultados.

Y bueno, así es como se hace, así es como está hecho en pub.lica.me

Fuente: link