MySQL - order/group records by foreign key referencing the same table (Laravel, ORM)

Question

I making an application in Laravel, and have run into a problem. I am creating a simple CRUD back-end to manage the pages. Page table structure (simplified) is as follows:

id  |  title    |  parent_id
-----------------------------
1   |  Homepage |  0
2   |  About    |  0
3   |  Team     |  2
4   |  Mission  |  2
5   |  Directors|  3
6   |  Contact  |  0

In my application, I want to display records nested by their parents. So it would like this:

Homepage
About
 - Team
   - Directors
 - Mission
Contact

I would be able to do this in normal application. However, I need a query/methods to work with the Eloquent models so that it arranges in that order. The nesting is not necessary, as I can make those checks per individual record, however, I need a query that would return all pages in order as per above.

This code, while groups correctly pages with same parents, I also need the query to put them in the correct place, just order it below the parent page:

Page::orderBy('parent_id')->get();

Thanks for help!

It can have any number of levels (well realistically no more than 3-4 in practical application, however, I would like a recursive solution that wouldn't be hard-coded for a number of levels) — Giedrius, Sep 07 '16 at 14:22

score 1 · Accepted Answer · answered Sep 07 '16 at 14:28

1

First of all, define at the model the relationship to itself, something like this:

public function children()
{
    return $this->hasMany('App\Page', 'parent_id', 'id');
}

Next, you can retrieve the first level items as follows with their children:

Page::with('children.children.children')->where('parent_id', 0)->get();

answered Sep 07 '16 at 14:28

Jose Rojas

3,490
3
26
40

That seems to get me closer. Now I do get all pages instances and 'children' accessible within them. However, I need to pass this into module, that accepts a collection of records and shows them in a table. How can I transform this result so that the children pages are not properties of the parent pages, but rather are on the same level in the collection as parent pages, ordered as they are now? – Giedrius Sep 07 '16 at 14:33
@Giedrius what do you mean with "children pages are not properties of the parent pages"? – Jose Rojas Sep 07 '16 at 14:44
Sorry having difficulty putting my ideas into proper sentences. With this answer, `$pages` variable will be sort of like this `[{title: 'About', children: [{title: 'Team'}...], {title: 'Contact}]`, and I need the Collection to have all the pages at the top level, so `[{title: 'About'}, {title: 'Team'} ..., {title: 'Contact'}]`, but they have to remain the same order as they do in the nested collection. Do you know what I mean? – Giedrius Sep 07 '16 at 14:51
1

I have used this solution together with a short anonymous recursive function to flatten on the children, seems to do the job – Giedrius Sep 07 '16 at 16:46

jeremy_nikolic · Answer 2 · 2016-09-07T14:25:52.470

0

Why not define the relations:

public function children(){
    return hasMany('Page', 'parent_id', 'id')
}
public function parent(){
    return hasOne('Page', 'id', 'parent_id');
}

(Juste be careful with a page being child of it self)

Then you use:

Page::with(['children' => function($q){
    return $q->orderBy('parent_id');
}])->get();

edited Sep 07 '16 at 14:25

answered Sep 07 '16 at 14:20

jeremy_nikolic

90
1
9

1

@Jose Rojas You must flatten your array: [link](http://stackoverflow.com/questions/1319903/how-to-flatten-a-multidimensional-array) – jeremy_nikolic Sep 07 '16 at 15:03
I tried Laravel's `array_flatten` but it didn't do the trick, probably as it's all Collections of Models. I will give it a go to manually recursively flatten it, if there is no nicer solution. Thanks :) – Giedrius Sep 07 '16 at 15:07
1

Why not use the flatten method on the laravel Collection ? [Link](https://laravel.com/docs/5.2/collections#method-flatten) – jeremy_nikolic Sep 07 '16 at 15:15
I have tried `$pages->flatten()`, but it doesn't seem to change the structure – Giedrius Sep 07 '16 at 15:27
My bad, seems like Collection->flatten use array_flatten – jeremy_nikolic Sep 07 '16 at 15:28

MySQL - order/group records by foreign key referencing the same table (Laravel, ORM)

2 Answers2