Laravel ORM from self referencing table get N level hierarchy JSON

laravel, laravel-4, php

Solution

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.

So here's all you need:

// 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');
}

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

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

Problem

I am using `LARAVEL 4` with `MySQL` back-end. 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 ``` 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 }] ``` 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'); } } ``` and called it in `controller` with : ``` $user = Survey::all(); $parent = $user->parent()->first(); $children = $user->children()->get(); ``` 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). while ``` print_r($children ->toJson()); ``` Gives only questions (Not groups and answers). I want the whole self-referencing data in nested JSON format with N level of hierarchy. I also have tried ``` $user = Survey::with('parent', 'children')->get(); ``` But found same as $parent above. Is there anyway I can get the desired result? Thanks in advance..

Original source