从Laravel上的数据库中检索所有父/子记录(分层数据) [英] Retrieve all parent/child records from database on Laravel (hierarchical data)

问题描述

对于类似旧票证的系统，我具有以下简化的数据库表结构.

I have the following simplified database table structure for a legacy tickets-like system.

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

在列表上，我显示所有消息.单击一条消息后，我将显示其答案等.

On a list, I show all the messages. When a message is clicked, I display its answer etc.

问题是，现在我需要创建一个与此消息相关的所有父母和孩子的列表结构，以及该消息在树中的位置. 如何从数据库中检索这些?

The problem is, now I need to make a list structure of all parents and children related to this message, as well as the position of this message in the tree. How could I retrieve those from database?

我正在使用Laravel，但是原始SQL也会帮助我找到方向.

I'm using Laravel, but raw SQL would also help me find the direction.

---

示例:

╔════╦═══════════╦════════════════════════╦═════════════════╗
║ id ║ parent_id ║        content         ║     answer      ║
╠════╬═══════════╬════════════════════════╬═════════════════╣
║  1 ║ NULL      ║ Hi, I have a problem   ║ I can't help    ║
║  2 ║ 1         ║ The problem persists   ║ Ok, what is it? ║
║  3 ║ 2         ║ Nevermind, I got this  ║ Oh, well.       ║
║  4 ║ 3         ║ Problem is back        ║ Which problem?  ║
║  5 ║ 4         ║ The same problem again ║ ...             ║
╚════╩═══════════╩════════════════════════╩═════════════════╝

在显示带有 id = 4 的消息时，我应该能够显示以下列表:

When showing message with id = 4, I should be able to display something like this list:

消息历史记录:
-我有问题
-问题仍然存在
-没关系，我知道了
-问题又回来了
-再次遇到相同的问题

Message history:
- Hi, I have a problem
- The problem persists
- Nevermind, I got this
- Problem is back
- The same problem again

对于父母和孩子，我只能想到一个循环和几个SQL查询执行，这看起来像代码的味道.

I could only think of a loop and several SQL query executions, for each parent and child, which looks like a code smell.

---

如达安(Daan)所述，该问题似乎是>如何创建MySQL分层递归查询的重复.

As stated by Daan, this question seems like a duplicate for How to create a MySQL hierarchical recursive query.

但是，我决定不删除它，因为Ravan只是使用Laravel的方法回答了该问题，该方法可以帮助我解决问题，所以我将其留在此处以供将来参考.

I decided to not delete it however, since Ravan just answered it with a Laravel approach that helped me solve the problem, so I'll just leave this here for future reference.

推荐答案

由于您正在执行分层操作，因此应该使用一种策略来保存和检索数据库中的数据.

Since you are doing hierarchical operations, you should use a strategy to save and retrieve this data from your database.

一种方法是使用嵌套集模型，这样可以使其变得更容易. Laravel有一个很好的软件包可以处理它，叫做 etrepat/baum ，它也解释了它是如何工作的，我引用:

One approach is to use Nested Set Model, that can make it easier. Laravel has a great package that deals with it, called etrepat/baum, that also explains how it works and I quote:

可视化嵌套集工作方式的一种简单方法是考虑围绕所有对象的父实体 它的孩子，以及它周围的父母，等等.所以这棵树:

An easy way to visualize how a nested set works is to think of a parent entity surrounding all of its children, and its parent surrounding it, etc. So this tree:

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
|   |___________________________|   |___________________________|   |
|___________________________________________________________________|

数字代表左右边界.该表然后可能 看起来像这样:

The numbers represent the left and right boundaries. The table then might look like this:

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

要获取父级节点的所有子级，您

To get all children of a parent node, you

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

要获得孩子的数量，是

(right - left - 1)/2

要获得一个节点及其所有祖先回到根，您

To get a node and all its ancestors going back to the root, you

SELECT * WHERE node.lft IS BETWEEN lft AND rgt

如您所见，查询将是递归的，并且在 普通的树木突然变得很快.漂亮，不是吗?

As you can see, queries that would be recursive and prohibitively slow on ordinary trees are suddenly quite fast. Nifty, isn't it?

这篇关于从Laravel上的数据库中检索所有父/子记录(分层数据)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！