Deeply nested subqueries for traversing trees in MySQL
- by nickf
I have a table in my database where I store a tree structure using the hybrid Nested Set (MPTT) model (the one which has lft and rght values) and the Adjacency List model (storing parent_id on each node).
my_table (id, parent_id, lft, rght, alias)
This question doesn't relate to any of the MPTT aspects of the tree but I thought I'd leave it in in case anyone had a good idea about how to leverage that.
I want to convert a path of aliases to a specific node. For example: "users.admins.nickf" would find the node with alias "nickf" which is a child of one with alias "admins" which is a child of "users" which is at the root. There is a unique index on (parent_id, alias).
I started out by writing the function so it would split the path to its parts, 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 realised I could do it with a single query, using a variable amount of nesting:
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 sub-queries is dependent on the number of steps in the path, am I going to run into issues with having too many subqueries? (If there even is such a thing)
Are there any better/smarter ways to perform this query?
