Deeply nested subqueries for moving trees in MySQL

Question

I have a table in my database where I store a tree structure using the hybrid Nested Set (MPTT) model (one that has lftand values rght) and an address list model (saving parent_idon each node).

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

This question does not apply to any aspect of MPTT in the tree, but I thought I would leave it in case anyone had a good idea on how to use this.

I want to convert a path from aliases to a specific node. For example: "users.admins.nickf"find a node with the nickname nickf, which is a child of one with the admins nickname, which is the child of the users, which is located in the root. There (parent_id, alias)is a unique index on it.

I started by writing a function to divide the path into its parts, and then query the database one by one:

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

But then I realized that I could do this with a single request, using a variable nesting value:

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';

Since the number of subqueries depends on the number of steps along the way, am I going to run into problems with too many subqueries? (If there is even such a thing)

Are there any better / smarter ways to fulfill this request?

Answer 1

?

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

, .

, - ?

Answer 2

-, , ( ). , " ", , , ... , lexu , , , - .

, / "" ( HAVING). , , , , SQL, . , , ( ) , ( ) ( , 370, - .)

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'