深度嵌套的子查询,用于遍历MySQL中的树 [英] Deeply nested subqueries for traversing trees in MySQL
问题描述
我的数据库中有一个表,其中使用混合嵌套集(MPTT)模型(具有lft
和rght
值的模型)和邻接列表模型(将parent_id
存储在每个节点).
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)
这个问题与树的MPTT方面无关,但是我认为如果有人对如何利用它有个好主意,我会保留.
我想将别名路径转换为特定节点.例如:"users.admins.nickf"
将找到别名为"nickf"的节点,该节点是根的别名为"users"的子节点,别名为"admins". (parent_id, alias)
上有一个唯一索引.
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?
推荐答案
这项工作有效吗?
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
在我看来,实际上并不需要嵌套的子查询..
Seems to me there is not really a need for nested subqueries ..
还是我错了,缺少什么?
or am I wrong, missing something?
这篇关于深度嵌套的子查询,用于遍历MySQL中的树的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!