从Laravel数据库中检索所有父/子记录（分层数据）

Question

我有一个遗留的类似于票务系统的简化数据库表结构，如下所示：

messages
  id         INT
  parent_id  INT
  content    TEXT
  answer     TEXT
  ...

在列表中，我展示了所有的消息。当点击一条消息时，我会显示它的答案等等。

问题是，现在我需要制作一个包含此消息的所有父元素和子元素的列表结构，以及该消息在树形结构中的位置。如何从数据库中检索这些信息？

我正在使用Laravel，但原始SQL也可以帮助我找到方向。

例子：

╔════╦═══════════╦════════════════════════╦═════════════════╗
║ id ║ parent_id ║        content         ║     answer      ║
╠════╬═══════════╬════════════════════════╬═════════════════╣
║  1 ║ NULL      ║ 嗨，我有一个问题       ║ 我无法帮助你     ║
║  2 ║ 1         ║ 问题仍然存在           ║ 好的，是什么问题？║
║  3 ║ 2         ║ 没事了，我解决了       ║ 哦，那好吧。     ║
║  4 ║ 3         ║ 问题又出现了           ║ 是哪个问题？     ║
║  5 ║ 4         ║ 又是同样的问题         ║ ...             ║
╚════╩═══════════╩════════════════════════╩═════════════════╝

当显示id = 4的消息时，我应该能够显示类似以下列表的内容：
消息历史：
- 嗨，我有一个问题
- 问题仍然存在
- 没关系，我解决了
- 问题又回来了
- 又是同样的问题
我只能想到循环和多个SQL查询执行，对于每个父级和子级，这看起来像是代码异味。

---

更新

正如Daan所说，这个问题看起来像是如何创建一个MySQL分层递归查询的重复。

然而，我决定不删除它，因为Ravan刚刚用Laravel方法回答了它，帮助我解决了问题，所以我将把它留在这里供日后参考。

- Charles

---

可能是MySQL分层递归查询的重复问题。 - Daan

它确实是重复的。当我搜索时，我想不起“分层”的单词。我该怎么办，删除这个问题吗？ - Charles

我认为这个问题很相关，因为有一些针对Laravel的特定解决方案。 - Ravan Scafi

好的，我会编辑问题的标题，以便专注于Laravel解决方案，因为这是我的真实场景，并提到其他可能的重复问题，以防对其他人有所帮助。感谢您的答案Ravan，正是我所需要的。 - Charles

@charlesrockbass，太棒了，我自己也使用这个包。既然它为你提供了解决方案，你能接受我的答案吗？ - Ravan Scafi

Answer 1

由于您正在进行分层操作，因此应使用一种策略从数据库中保存和检索此数据。

一种方法是使用嵌套集模型，这可以使它更容易。 Laravel有一个很棒的包，叫做etrepat/baum，它处理了这个问题，并且解释了它的工作原理，我引用:

背后的理论，简短版

可视化嵌套集如何工作的一种简单方法是将父实体围绕其所有子实体，其父实体围绕它等等。所以这棵树:

root
  |_ Child 1
    |_ Child 1.1
    |_ Child 1.2
  |_ Child 2
    |_ Child 2.1
    |_ Child 2.2

可以这样形象地展示：

 ___________________________________________________________________
|  Root                                                             |
|    ____________________________    ____________________________   |
|   |  Child 1                  |   |  Child 2                  |   |
|   |   __________   _________  |   |   __________   _________  |   |
|   |  |  C 1.1  |  |  C 1.2 |  |   |  |  C 2.1  |  |  C 2.2 |  |   |
1   2  3_________4  5________6  7   8  9_________10 11_______12 13  14
|   |___________________________|   |___________________________|   |
|___________________________________________________________________|

数字表示左右边界。表格可能如下所示：

id | parent_id | lft  | rgt  | depth | data
 1 |           |    1 |   14 |     0 | root
 2 |         1 |    2 |    7 |     1 | Child 1
 3 |         2 |    3 |    4 |     2 | Child 1.1
 4 |         2 |    5 |    6 |     2 | Child 1.2
 5 |         1 |    8 |   13 |     1 | Child 2
 6 |         5 |    9 |   10 |     2 | Child 2.1
 7 |         5 |   11 |   12 |     2 | Child 2.2

要获取所有子节点，您需要使用父节点。

SELECT * WHERE lft IS BETWEEN parent.lft AND parent.rgt

要获得子元素的数量，可以使用以下代码：

(right - left - 1)/2

为了获取一个节点及其所有祖先节点，一直追溯到根节点，您可以：

SELECT * WHERE node.lft IS BETWEEN lft AND rgt

正如你所看到的，那些在普通树上递归且速度极慢的查询，在这里变得非常快速。很神奇，不是吗？

- Ravan Scafi