Recursión SQL

tengo las siguientes tablas tabla de grupos que contiene grupos orderados jerárquicamente y group_member que almacena a qué grupos pertenece un usuario.

groups --------- id parent_id name group_member --------- id group_id user_id ID PARENT_ID NAME --------------------------- 1 NULL Cerebra 2 1 CATS 3 2 CATS 2.0 4 1 Cerepedia 5 4 Cerepedia 2.0 6 1 CMS ID GROUP_ID USER_ID --------------------------- 1 1 3 2 1 4 3 1 5 4 2 7 5 2 6 6 4 6 7 5 12 8 4 9 9 1 10 

Quiero recuperar los grupos visibles para un usuario dado. Eso quiere decir grupos a los que pertenece un usuario e hijos de estos grupos. Por ejemplo, con los datos anteriores:

 USER VISIBLE_GROUPS 9 4, 5 3 1,2,4,5,6 12 5 

Obtengo estos valores usando recursion y varias consultas de bases de datos. Pero me gustaría saber si es posible hacerlo con una sola consulta SQL para mejorar el performance de mi aplicación. Estoy usando MySQL.

Dos cosas vienen a la mente:

1 – Puedes unir externamente la tabla a sí mismo repetidamente para upload tu tree, como en:

 SELECT * FROM MY_GROUPS MG1 ,MY_GROUPS MG2 ,MY_GROUPS MG3 ,MY_GROUPS MG4 ,MY_GROUPS MG5 ,MY_GROUP_MEMBERS MGM WHERE MG1.PARENT_ID = MG2.UNIQID (+) AND MG1.UNIQID = MGM.GROUP_ID (+) AND MG2.PARENT_ID = MG3.UNIQID (+) AND MG3.PARENT_ID = MG4.UNIQID (+) AND MG4.PARENT_ID = MG5.UNIQID (+) AND MGM.USER_ID = 9 

Eso te dará resultados como este:

 UNIQID PARENT_ID NAME UNIQID_1 PARENT_ID_1 NAME_1 UNIQID_2 PARENT_ID_2 NAME_2 UNIQID_3 PARENT_ID_3 NAME_3 UNIQID_4 PARENT_ID_4 NAME_4 UNIQID_5 GROUP_ID USER_ID 4 2 Cerepedia 2 1 CATS 1 null Cerebra null null null null null null 8 4 9 

El límite aquí es que debe agregar una nueva unión para cada "nivel" que desee upload por el tree. Si su tree tiene less de, digamos, 20 niveles, entonces probablemente pueda salirse con la suya creando una vista que muestre 20 niveles de cada usuario.

2 – El único otro enfoque que conozco es crear una function de database recursiva y llamarla desde el código. De todos modos, tendrá algunos gastos indirectos de búsqueda (es decir, su número de consultas seguirá siendo igual al número de niveles que está recorriendo en el tree), pero en general debería ser más rápido ya que todo se lleva a cabo dentro de la database.

No estoy seguro acerca de MySql, pero en Oracle, esa function sería similar a esta (tendrá que cambiar los nombres de tabla y campo, solo estoy copyndo algo que hice en el pasado):

 CREATE OR REPLACE FUNCTION GoUpLevel(WO_ID INTEGER, UPLEVEL INTEGER) RETURN INTEGER IS BEGIN DECLARE iResult INTEGER; iParent INTEGER; BEGIN IF UPLEVEL <= 0 THEN iResult := WO_ID; ELSE SELECT PARENT_ID INTO iParent FROM WOTREE WHERE ID = WO_ID; iResult := GoUpLevel(iParent,UPLEVEL-1); --recursive END; RETURN iResult; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END; END GoUpLevel; / 

Los libros de Joe Cleko "SQL for Smarties" y "Trees and Hierarchies in SQL for Smarties" describen methods que evitan la recursión por completo, mediante el uso de sets nesteds. Eso complica la actualización, pero hace que otras consultas (que normalmente necesitarían recursión) sean comparativamente sencillas. Hay algunos ejemplos en este artículo escrito por Joe en 1996.

Creo que vas a necesitar CURSORES para esto, este enlace puede ayudar

No creo que esto se pueda lograr sin usar recursion. Puede lograrlo con un solo procedimiento almacenado utilizando mySQL, pero la recursión no está permitida en los procedimientos almacenados de forma pnetworkingeterminada. Este artículo contiene información sobre cómo habilitar la recursión. No estoy seguro de cuánto impacto tendrá esto en el performance frente al enfoque de consulta múltiple. mySQL puede hacer algo de optimization de los procedimientos almacenados, pero de otro modo esperaría que el performance fuera similar.

No sabía si tenía una tabla de Usuarios, así que obtengo la list a través de los User_ID almacenados en la tabla Group_Member …

 SELECT GroupUsers.User_ID, ( SELECT STUFF((SELECT ',' + Cast(Group_ID As Varchar(10)) FROM Group_Member Member (nolock) WHERE Member.User_ID=GroupUsers.User_ID FOR XML PATH('')),1,1,'') ) As Groups FROM (SELECT User_ID FROM Group_Member GROUP BY User_ID) GroupUsers 

Eso vuelve:

 User_ID Groups 3 1 4 1 5 1 6 2,4 7 2 9 4 10 1 12 5 

Lo cual parece correcto según los datos en su tabla. Pero no coincide con su list de valores esperados (por ejemplo, el Usuario 9 está solo en un grupo en los datos de su tabla pero lo muestra en los resultados como pertenecientes a dos)

EDITAR: Dang. Recién noté que estás usando MySQL. Mi solución fue para SQL Server. Lo siento.

– Kevin Fairchild

Ya hubo una pregunta similar planteada.

Aquí está mi respuesta (un poco editada):

No estoy seguro de haber entendido correctamente tu pregunta, pero esto podría funcionar. Mi opinión sobre los treees en SQL .

Publicación vinculada describió el método de almacenamiento de tree en la database – PostgreSQL en ese caso – pero el método es lo suficientemente claro, por lo que se puede adoptar fácilmente para cualquier database.

Con este método, puede actualizar fácilmente todos los nodos que dependen del nodo K modificado, con aproximadamente N consultas SELECT simples, donde N es la distancia de K desde el nodo raíz.

¡Buena suerte!

No recuerdo en cuál de las siguientes preguntas encontré el enlace, pero este artículo en sitepoint.com (segunda página) muestra otra forma de almacenar treees jerárquicos en una tabla que facilita la búsqueda de todos los nodos secundarios, o la ruta al arriba, cosas así. Buena explicación con código de ejemplo.


PD. Newish to StackOverflow, ¿está bien la respuesta anterior, o debería haber sido realmente un comentario sobre la pregunta, ya que es solo un indicador de una solución diferente (que no responde exactamente la pregunta)?

No hay forma de hacerlo en el estándar SQL, pero generalmente puede encontrar extensiones específicas del proveedor, por ejemplo, CONNECT BY en Oracle.

ACTUALIZACIÓN: como lo señalan los comentarios, esto fue agregado en SQL 99.