Laravel Eloquent get N Level hierarchy records from self-referencing table with other table's columns

Question

I am using Laravel 4 with MySQL back-end.

I have two database tables namely - surveyes and templates.

Both of the tables are self-referencing and they have column named parent that is foreign key of that table itself. Their records are in self referencing parent-child relationship.

The table design is as below :

Surveyes :

(PK)                        (FK_Surveyes)
Id  title       type        parent          sort_order  deleted_at
1   General     group       NULL            1           NULL
2   Where..?    question    1               1           NULL
3   abc..       answer      2               1           NULL
4   def..       answer      2               2           NULL
5   efg..       answer      2               3           NULL
6   ghi..       answer      2               4           NULL
7   What..?     question    1               2           NULL
8   hij..       answer      7               1           NULL
9   ijk..       answer      7               2           NULL
10  How..?      question    8               1           NULL
11  jkl..       answer      10              1           NULL
12  mnm..       answer      10              2           NULL
13  Special     group       NULL            2           NULL
14  Whom..?     question    13              1           NULL
15  opq..       answer      14              1           NULL
16  rst..       answer      14              2           NULL

Templates:

(PK)(FK_surveyes)               (FK_Templates)
Id  survey_id       type        parent          sort_order  deleted_at
1   NULL            group       NULL            1           NULL
2   14              question    1               1           NULL
3   15              answer      2               1           NULL
4   16              answer      3               2           NULL
5   NULL            group       NULL            2           NULL
6   2               question    5               1           NULL
7   3               answer      6               1           NULL
8   4               answer      6               2           NULL
9   5               answer      6               3           NULL
10  6               answer      8               4           NULL
11  7               question    10              2           NULL
12  8               answer      10              1           NULL
13  9               answer      10              2           NULL

Now, I want their records also to be in the same manner with N level of hierarchy.

So that I have creates a model for templates as below :

class Template extends BaseModel{

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

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

    // recursive, loads all descendants
    public function children()
    {
       return $this->child()->with('children')->orderBy('sort_order');
    }

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

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

And I am using method below (which is working fine) to get the N level of hierarchy for templates :

public function getTemplates(){
    $templates = Template::with('children')
                ->whereNull('parent')
                ->orderBy('sort_order', 'ASC');

    return $templates->toJson();
}

But now, I want the column title from the surveyes table. The column survey_id in table templates is the foreign key of table surveyes.

How can I achieve it?

I have updated following method in Template model as below :

public function children()
{
   return $this->child()->with('children')->orderBy('sort_order')
        ->leftJoin('surveyes', 'surveyes.id', '=', 'templates.survey_id')->select('templates.*','surveyes.title');
}

But it doesn't gives the hierarchical records and browser gets hang-up. I have only 1500 records in templates table.

Does anybody knows how to achieve it?

EDIT :

I have added a method to get Survey model in templates as below :

public function survey()
    {
        return $this->belongsTo('D2D\Models\Survey','survey_id');
    }

and updated the children() method as below :

public function children()
{
   return $this->child()
   ->with('survey','children')
   ->orderBy('sort_order');
}

Now I am able to get the records of Survey model but it returns all the column of the Survey as below :

{
    "id": 2,
    "survey_id": 522,
    "title": "Abc....?",
    "type": "question",
    "parent": 1200
    "survey": {
        "id": 522,
        "type": "question",
        "subtype": null,
        "title": "Abc....?",
        "parent": 1
    },
    "children": [{
        "id": 3,
        "survey_id": 526,
        "title": "aaa",
        "type": "answer",
        "parent": 2
        "survey": {
            "id": 526,
            "type": "answer",
            "subtype": null,
            "title": "aaa",
            "parent": 522
        },
        "children": []
    },
    {
        "id": 4,
        "survey_id": 527,
        "title": "bbb",
        "type": "answer",
        "parent": 2
        "survey": {
            "id": 527,
            "type": "answer",
            "title": "bbb",
            "parent": 522,
        },
        "children": []
    },
    ...
}

But I want it like below :

{
    "id": 2,
    "survey_id": 522,
    "title": "Abc....?", // Must be from Survey table
    "type": "question",
    "parent": 1200
    "children": [{
        "id": 3,
        "survey_id": 526,
        "title": "aaa", // Must be from Survey table
        "type": "answer",
        "parent": 2
        "children": []
    },
    {
        "id": 4,
        "survey_id": 527,
        "title": "bbb", // Must be from Survey table
        "type": "answer",
        "parent": 2
        "children": []
    },
    ...
}

Is there any way to achieve this?

I have tried by updating survey() method as below :

public function survey()
{
    return $this->belongsTo('D2D\Models\Survey','survey_id')->select(array('title'));
}

But in that case it gives title=NULL for each and every element.

Please let me know how to achieve this?

Thanks.

See: stackoverflow.com/questions/24989245/… The recursive methods have been defined separately. You also still need to set the relations. — Luceos
– Luceos, Commented Jul 31, 2014 at 5:40
That is different. I don't want to store anything but I want to get a column that is in another table. Also I think if I use it then in each for loop iteration it will make a separate database call. I can have thousands of records in future, in that case there might have a performance issue. — Dev
– Dev, Commented Jul 31, 2014 at 5:41
@deczo Yes. I get this error if I use ->get() statement as I have mentioned. — Dev
– Dev, Commented Jul 31, 2014 at 9:11
Yes, but without that leftJoin in the relation, there is no error? — Jarek Tkaczyk
– Jarek Tkaczyk, Commented Jul 31, 2014 at 9:31

bthecohen · Accepted Answer · 2015-01-21 00:05:24Z

2

One possible solution to this problem is to use the Nested Sets model. This avoids the problem of recursive queries. There are two available Laravel packages that extend Eloquent to implement Nested Sets: Laravel-NestedSet and Baum. I understand you have the issue of existing data; however, it shouldn't be too hard to make a one-time migration script. You can create new models using whichever nested sets library you pick; these models will have methods to set the parent node of a survey. You can have the new survey data in a new table (you can override the table name in Eloquent; see here. Then, write a script or protected controller action to migrate the existing surveys to the new table. It will work something like this (using the laravel-nestedset syntax; Baum is similar):

$oldSurveys = Survey::all();
$oldSurveys->each(function($survey) {
    $newSurvey = NestedSurvey::create(array($survey->name, $survey->type ...etc));
});

$newSurveys = NestedSurvey::whereNotNull('parent_id');
$newSurveys->each(function($survey) {
    $parent = NestedSurvey::find($survey->parent_id);
    $survey->appendTo($parent)->save();
});

Once you have imported all your survey into the new table, you can switch your application over to use the new models.

Now, you'll be able to fetch an arbitrarily deep hierarchy in linear time. And since your model extends Eloquent, you can get any related columns just as you normally would.

edited Jan 21, 2015 at 0:05

answered Jan 21, 2015 at 0:00

bthecohen

4863 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dev Over a year ago

I have already used laravel baum package and it seems working fine now. Thanks a lot. +1 for your kind help.

Collectives™ on Stack Overflow

Laravel Eloquent get N Level hierarchy records from self-referencing table with other table's columns

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related