PHP/MySQL:在邻接表模型中检索单个路径 [英] PHP/MySQL: Retrieve a single path in the Adjacency List model
问题描述
在不限制深度的情况下，是否有任何有效的方法可以基于节点的ID在邻接表"模型中检索单个路径?就像如果我有一个名为香蕉"的节点的ID一样，我可以得到以下路径:食物>水果>香蕉
Is there any effective way to, without limiting the depth, retrieve a single path in a Adjacency List model based on the node's ID? Like if I've got an ID for a node named "Banana" I could get the following Path: Food > Fruits > Banana
如果不可能的话这不是一个大问题，但是我考虑过是否可以通过while循环或其他方式运行联接?直到父母变成0.
It's not a big problem if it's impossible, but I thought about if it could be possible to run joins through a while-loop or something? Until the parent turns 0.
推荐答案
否，至少不是在MySQL中.这是邻接列表模型的最大限制之一.
No, not in MySQL at least. That is one of the biggest limitations of the Adjacency List Model.
您可以继续自我加入有限的次数，但这很丑陋，尴尬并且不能涵盖无限的部门.您还可以下载应用程序中的所有数据，构建一棵树，然后在应用程序中找到路径.
You could continue to self join a finite number of times, but that's ugly, awkward and doesn't cover unlimited dept. You could also download all the data in your application, build a tree, and find the path in the application.
某些DBMS(例如SQL Server 2005，Postgres 8.4和Oracle 11g)使用公用表来支持递归查询表达式 和WITH关键字.此功能使编写这样的查询变得容易，但是不幸的是，MySQL还不支持递归查询.
Some DBMSes, such as SQL Server 2005, Postgres 8.4 and Oracle 11g, support recursive queries using common table expressions with the WITH keyword. This feature allows queries such as this to be written with ease, but unfortunately MySQL does not support recursive queries yet.
您可能有兴趣查看以下描述替代模型的文章(嵌套集模型)，这使得递归操作在MySQL中更容易(可能):
You may may be interested in checking out the following article which describes an alternative model (the nested set model), which makes recursive operations easier (possible) in MySQL:
此外，我还建议通过Stack上的定期撰稿人 @Bill Karwin 查看以下演示文稿溢出:
In addition, I also suggest checking out the following presentation by @Bill Karwin, a regular contributor on Stack Overflow:
演示文稿中描述的闭包表模型是嵌套集的非常有效的替代方案.他在 SQL Antipatterns (
The closure table model described in the presentation is a very valid alternative to the nested set. He describes this model in further detail in his SQL Antipatterns book (excerpt from the chapter on this topic).
这篇关于PHP/MySQL:在邻接表模型中检索单个路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
