Giedrius Giedrius - 1 year ago 111
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:

- Team
- Directors
- Mission

I would be able to do this in normal application. However, I need a query/methods to work with the
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:


Thanks for help!

Answer Source

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();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download