Subconsultas profundamente anidadas para recorrer treees en MySQL

Tengo una tabla en mi database donde almaceno una estructura de tree usando el model de set nested híbrido (MPTT) (el que tiene valores lft y rght ) y el model de list de adyacencia (que almacena parent_id en cada nodo).

 my_table (id, parent_id, lft, rght, alias) 

Esta pregunta no se relaciona con ninguno de los aspectos MPTT del tree, pero pensé que lo dejaría en caso de que alguien tuviera una buena idea acerca de cómo aprovechar eso.

Quiero convertir una ruta de alias a un nodo específico. Por ejemplo: "users.admins.nickf" encontraría el nodo con alias "nickf" que es hijo de uno con alias "admins" que es hijo de "usuarios" que está en la raíz. Hay un índice único en (parent_id, alias) .

Comencé escribiendo la function para que dividiera la ruta de acceso a sus partes y luego consultara la database uno por uno:

 SELECT `id` FROM `my_table` WHERE `parent_id` IS NULL AND `alias` = 'users';-- 1 SELECT `id` FROM `my_table` WHERE `parent_id` = 1 AND `alias` = 'admins'; -- 8 SELECT `id` FROM `my_table` WHERE `parent_id` = 8 AND `alias` = 'nickf'; -- 37 

Pero luego me di count de que podía hacerlo con una sola consulta, usando una cantidad variable de anidación:

 SELECT `id` FROM `my_table` WHERE `parent_id` = ( SELECT `id` FROM `my_table` WHERE `parent_id` = ( SELECT `id` FROM `my_table` WHERE `parent_id` IS NULL AND `alias` = 'users' ) AND `alias` = 'admins' ) AND `alias` = 'nickf'; 

Dado que el número de sub-consultas depende de la cantidad de pasos en la ruta, ¿voy a tener problemas al tener demasiadas subconsultas ? (Si hay tal cosa)

¿Hay alguna forma mejor / más inteligente para realizar esta consulta?

¿Esto funciona?

 select r0.id from my_table as r0 join my_table as r1 on(r0.parent_id = r1.id) join my_table as r2 on(r1.parent_id = r2.id) where r0.alias='nickf' and r1.alias='admins' and r2.alias='users' and r2.parent_id is null 

Me parece que no hay realmente una necesidad de subconsultas anidadas.

o estoy equivocado, falta algo?

Me pregunté sobre esto yo mismo, y estaba buscando algo que no fuera más lento a medida que avanzaste más profundo (lo que significa más niveles en las dos opciones anteriores). El problema que tengo con "mi versión" es que tiene que crear todos los paths posibles. antes de que networkinguzca el resultado al que estás buscando en realidad … así que creo que la versión de Lexu debe superar la mía incluso para anidamientos muy grandes porque es una unión simple, pero espero que alguien la vea y desee expandirla en eso más.

Además, esta forma de hacerlo definitivamente se beneficiaría de un process almacenado, y / o una vista de la parte 'paths' (sin la cláusula HAVING). Tal vez con ellos sea una mejor solución, pero desafortunadamente no sé lo suficiente en este momento sobre el performance de SQL para decirlo con certeza. Puedo decir que el mío se vuelve más lento a medida que los datos (número de combinaciones posibles de paths) se hacen más grandes, pero con una vista (dado que el resultado está almacenado en caching y usándolo para networkingucirlo) parece rápido (el set de datos más grande que encontré) fue 370 en total, en algún momento crearé un set mucho más grande para probar).

 SELECT node.id, GROUP_CONCAT(parent.alias ORDER BY parent.lft SEPARATOR '.') AS path_name FROM my_table AS node, my_table AS parent WHERE node.lft BETWEEN parent.lft AND parent.rght GROUP BY node.id HAVING path_name = 'users.admins.nickf' 
Intereting Posts