Giedrius Giedrius - 3 months ago 9
MySQL Question

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

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!

Answer

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();