Laravel ORM从自引用表获取N级层次结构JSON [英] Laravel ORM from self referencing table get N level hierarchy JSON

问题描述

我正在将LARAVEL 4与MySQL后端一起使用.

I am using LARAVEL 4 with MySQL back-end.

我有一个self-referencing表，其中有id, name, type和parent列. 此处，parent是列Id的foreign-key.表中的数据如下:

I have a self-referencing table with columns id, name, type and parent. Here, parent is foreign-key of the column Id. The data in table is as below :

id  name          type         parent 
1   General       group        NULL
2   What is..?    question     1
3   aa            answer       2
4   bb            answer       2
5   cc            answer       2
6   How is..?     question     1
7   ba            answer       6
8   bb            answer       6
9   Where is..?   question     4
10  ca            answer       9
11  cb            answer       9
12  Who is..?     question     6
13  da            answer       12
14  db            answer       12
15  Specific      group        NULL
16  When is..?    question     15
17  ea            answer       16
18  eb            answer       16
19  Whome is..?   question     2
20  fa            answer       19
21  fb            answer       19
22  fc            answer       19

我想要一个使用此关系数据返回nested JSON的函数.例如:

I want a function that return nested JSON using this relational data. For example :

[{
  "id" : 1, 
  "name" : "Geneal", 
  "type" : "group", 
  "children" : [{
      "id" : 2, 
      "name" : "What is..?", 
      "type" : "question", 
      "children" : [{
         "id" : 3, 
         "name" : "aa", 
         "type" : "answer"
      },
      {
         "id" : 4, 
         "name" : "bb", 
         "type" : "answer"
      },
      {
         "id" : 5, 
         "name" : "cc", 
         "type" : "answer"
      }]},
      {
      "id" : 6, 
      "name" : "How is..?", 
      "type" : "question", 
      "children" : [{
         "id" : 7, 
         "name" : "ba", 
         "type" : "answer"
      },
      {
         "id" : 8, 
         "name" : "bb", 
         "type" : "answer"
      }]
   }]
... and so on
}]

我创建了一个名为Survey的model，如下所示:

I have created a model named Survey as below :

class Survey extends BaseModel{

    protected $table = 'questions';
    protected $softDelete = false;

    public function parent()
    {
        return $this->belongsTo('Survey', 'parent');
    }

    public function children()
    {
        return $this->hasMany('Survey', 'parent');
    }   
}

并在controller中用

$user = Survey::all();
$parent = $user->parent()->first();
$children = $user->children()->get();

但是我没有得到上面JSON中提到的正确结果.

But I am not getting the proper result as I have mentioned in JSON above.

print_r($parent->toJson()); 

仅提供具有一级层次结构的记录(即组和问题，而不是答案).

gives records with one level hierarchy only (i.e group and questions, not answers).

同时

print_r($children ->toJson());

仅给出问题(不提供组和答案).

Gives only questions (Not groups and answers).

我想要整个嵌套JSON格式且具有N级层次结构的自引用数据.

我也尝试过

$user = Survey::with('parent', 'children')->get();

但与上面的$ parent相同.

But found same as $parent above.

反正我可以获得理想的结果吗?

Is there anyway I can get the desired result?

先谢谢了.

推荐答案

以下是您手动检索嵌套关系的方法:

Here's how you manually retrieve nested relations:

$collection = Model::with('relation1.relation2.relation3')->get();

因此，您的情况应该是:

So in your case it would be:

$surveys = Survey::with('children.children.children')->get();

很显然，当关系固定后，这将完成工作，但这不是对同一张表进行递归关系的方法.

Obviously this will do the job when the relations are fixed, but it's not the way to go for a recursive relation to the same table.

幸运的是，您可以使这种关系递归，然后您需要检索整个树:

Fortunately, you can make such relation recursive, then all you need to retrieve whole tree is this:

$surveys = Survey::with('childrenRecursive');

但是，我不会以这种方式为每一行加载父级.

However, I wouldn't load parent for each row this way.

这就是您所需要的:

// Survey model
// loads only direct children - 1 level
public function children()
{
   return $this->hasMany('Survey', 'parent');
}

// recursive, loads all descendants
public function childrenRecursive()
{
   return $this->children()->with('childrenRecursive');
   // which is equivalent to:
   // return $this->hasMany('Survey', 'parent')->with('childrenRecursive);
}

// parent
public function parent()
{
   return $this->belongsTo('Survey','parent');
}

// all ascendants
public function parentRecursive()
{
   return $this->parent()->with('parentRecursive');
}

---

要获得真实的树结构，必须将第一个查询限制为仅根节点:

---

To get real tree structure, first query must be limited to only root nodes:

$surveys = Survey::with('childrenRecursive')->whereNull('parent')->get();

这篇关于Laravel ORM从自引用表获取N级层次结构JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！