TABLE: menu
________________________________ | id | name | parent_id | |______|__________|_____________| | 1 | users | NULL | | 2 | doctors | 1 | | 3 | patients | 1 | |______|__________|_____________|
parent_id refers to id in the same table
TABLE: menu_links
_________________________________________ | id | link | menu_id | |______|__________________|_____________| | 1 | /doctors | 2 | | 2 | /doctors/create | 2 | | 3 | /patients | 3 | |______|__________________|_____________|
menu_id refers to id in menu table
I want the fetched data from DB to look like this:
Array
(
[id] => 1
[name] => users
[parent_id] =>
[children] => Array
(
[0] => Array
(
[id] => 2
[name] => doctors
[parent_id] => 1
[links] => Array
(
[0] => Array
(
[id] => 1
[menu_id] => 2
[link] => /doctors
)
[1] => Array
(
[id] => 2
[menu_id] => 2
[link] => /doctors/create
)
)
)
[1] => Array
(
[id] => 3
[name] => patients
[class] => fa-child
[parent_id] => 1
[links] => Array
(
[0] => Array
(
[id] => 3
[menu_id] => 2
[link] => /patients
)
)
)
)
)
What I tried:
In Menu model:
public function links(){
return $this->hasMany(MenuLinks::class);
}
public function children(){
return $this->hasMany(Menu::class, 'parent_id', 'id')->with('links');
}
In the controller:
$menu = Menu::with('children')->get()->toArray();
But this runs 3 queries:
select * from `menu`
select * from `menu` where `menu`.`parent_id` in (1, 2, 3)
select * from `menu_links` where `menu_links`.`menu_id` in (2, 3)
And returns the menu items separated too like that:
Array
(
[id] => 2
[name] => doctors
[parent_id] => 1
[children] => Array
(
)
)
Array
(
[id] => 3
[name] => patients
[parent_id] => 1
[children] => Array
(
)
)
Is there a better way with 1 query for example to get the same result without duplicating the menu items?