SQL engorrosa

[huspy]

Buenas!

Estoy trabajando en un programa en Java e intentando hacer una select un poco compleja (por lo menos para mí jeje) a ver si me podéis echar una mano.



Tengo 2 tablas, una de directorios y otra de documentos. Tanto los documentos como los directorios "cuelgan" de otros directorios, es decir los directorios hacen referencia a sí mismos y los documentos también tiene una clave foránea a los directorios. Además en cada directorio se guarda el nivel al que pertenece, es decir, para el directorio raíz (que es único) el nivel es el 1, para los que cuelguen de él tendrán el nivel 2 y así sucesivamente...

En la aplicación debo mostrar un árbol que contengan los directorios y en la parte derecha una tabla con un listado de los documentos de cada directorio seleccionado en cada momento en el árbol.

Para ello lo que estoy intentando hacer con la select es obtener los documentos y además los directorios a los que pertenecen, pero recursivamente, me explico gráficamente:

Código SQL:

DIR_DIRECTORIO
dir_pk    dir_dir_pk        dir_nombre            dir_nivel
1            NULL                directorio_raíz        1
2            1                    directorio1                2
3            1                    directorio2                2
4            2                    directorio3                3
5            4                    directorio4                4
 
 
DOC_DOCUMENTO
doc_pk        dir_pk        doc_nombre
1                2                documento1
2                4                documento2
3                4                documento3
4                5                documento4
5                3                documento5
 

Entonces teniendo estas tablas con dichos datos tendría que sacar todos los directorios y sus carpetas hasta llegar al nivel superior (aunque solo sepa el padre y no el abuelo en la jerarquía). Me explio? :)

Actualmente la select que he formado es la siguiente, pero me parece una guarrería... porque tengo que ir formando mediante un bucle en java lo que indico en rojo dependiendo por cada nivel que exista en la jerarquía de directorios:

Código SQL:

SELECT dir.dir_pk, dir.dir_dir_pk, dir.dir_nivel, dir.dir_nombre,
dir1.dir_pk, dir1.dir_dir_pk, dir1.dir_nivel, dir1.dir_nombre,
dir2.dir_pk, dir2.dir_dir_pk, dir2.dir_nivel, dir2.dir_nombre,
dir3.dir_pk, dir3.dir_dir_pk, dir3.dir_nivel, dir3.dir_nombre,
doc.doc_pk, doc.doc_nombre
 
FROM dir_directorio dir 
LEFT JOIN dir_directorio dir1 ON dir.dir_dir_pk = dir1.dir_pk
LEFT JOIN dir_directorio dir2 ON dir1.dir_dir_pk = dir2.dir_pk
LEFT JOIN dir_directorio dir3 ON dir2.dir_dir_pk = dir3.dir_pk
LEFT JOIN doc_documento doc ON dir.dir_pk = doc.dir_pk;
 

En realidad esto me prodría servir, pero como he dicho me parece bastante deo y no se de verdad si hay alguna manera mejor y más limpia de hacerlo

Esto lo que me devolvería sería:

Código SQL:

dir_pk1        dir_dir_pk1        dir_nivel1        dir_nombre1            dir_pk2        dir_dir_pk2        dir_nivel2            dir_nombre2        dir_pk3        ir_dir_pk3        dir_nivel3            dir_nombre3        doc_pk        doc_nombre
1                NULL                    1                    directorio_raiz        NULL            NULL                    NULL                    NULL                    NULL            NULL                NULL                    NULL                    1                documento1
2                1                        2                    directorio1                1                NULL                    1                        directorio_raiz    NULL            NULL                NULL                    NULL                    2                documento2
2                1                        2                    directorio1                1                NULL                    1                        directorio_raiz    NULL            NULL                NULL                    NULL                    3                documento3
4                2                        3                    directorio3                2                1                        2                        directorio1            1                NULL                1                        directorio_raiz    4                documento4
1                NULL                    1                    directorio_raiz        NULL            NULL                    NULL                    NULL                    NULL            NULL                NULL                    NULL                    5                documento5
 

Bueno espero me puedan ayudar un poco en esto.

Muchas Gracias por todo y Saludos!!

[quimfv]

Para construir esa query necesitas consultar primero el nivel del directorio donde esta el documento

SELECT dir.nivel FROM documento doc
INNER JOIN directorios dir on doc.idDir=dir.diDir WHERE doc.idDoc=X

y luego hacer un bucle para construirla y finalmente ejecutarla.

Se me ocurre que quizas sea mas rapido hacer lo siguiente

SELECT * FROM directorios

obtienes todos los directorios y guardas el resultado en un array

Luego

SELECT * FROM documento doc
INNER JOIN directorios dir on doc.idDir=dir.diDir WHERE doc.idDoc=X

(para un doc o sin el WHERE para todos)

con lo que obtienes los datos del documento y de su directorio, con ellos montas el bucle sobre el array de directorios para obtener el arbol.

Estas son consultas muy simples para el servidor de bbdd y ademas la primera la ejecutas una sola vez (mientras no haya cambios en el arbol de directorios). El bucle sobre el array será mas o menos dificil de programar pero una vez hecho seguramente será mas eficiente que una query con n niveles de recursividad.

No se es una idea....