[FIXED] Laravel returning children of parents in same table

Issue

Using Laravel 5.1, I am trying to create a menu list from a MySQL categories table. My service provider returns data, but I don't understand how to create the child categories in a foreach loop. When I perform the loop, only the last row of the child query is returned. Any guidance would be appreciated.

categories Table

id  | cat_name      | cat_parent_id
--- | --------------| ------------- 
1   | Parent Cat 1  | NULL 
2   | Parent Cat 2  | NULL 
3   | Child Cat 1   | 2 
4   | Child Cat 2   | 2 
5   | Parent Cat 3  | NULL 
6   | Child Cat 3   | 5

Desired Result

Parent Cat 1
Parent Cat 2
    Child Cat 1
    Child Cat 2
Parent Cat 3
    Child Cat 3

viewComposerServiceProvider.php

public function boot()
{
       $this->composeTopCategoryNavigation();
       $this->composeSubCategoryNavigation();
}

private function composeTopCategoryNavigation()
{
    view()->composer('partials.header', function($view)
        {
            $view->with('top_cats', Category::whereNull('cat_parent_id')->orderBy('cat_name', 'asc')->get());
        });
}

private function composeSubCategoryNavigation()
{
        view()->composer('partials.header', function($view)
        {
            $view->with('sub_cats', Category::whereNotNull('cat_parent_id')->orderBy('cat_name', 'asc')->get());
        });
}

header view

<ul>
@foreach ($top_cats as $top_cat)
  <?php $top_cat_slug = str_slug( $top_cat->cat_name, "-"); ?>
  <li>{{ $top_cat->cat_name }}
    @foreach ($sub_cats as $sub_cat)
            @if ( $sub_cat->cat_parent_id === $top_cat->id )
                <ul>
                  <li{{ $sub_cat->cat_name }}</li>
                </ul>
                @endif
        @endforeach
   </li>
@endforeach
</ul>

Solution

First of all, what you are doing is inefficient. Your view iterates through all subcategories for every parent category. If you defined relations properly and made use of Eloquent's eager loading, you could fetch and access child categories in easier way.

Start with defining relations:

class Category extends Model {
  //each category might have one parent
  public function parent() {
    return $this->belongsToOne(static::class, 'cat_parent_id');
  }

  //each category might have multiple children
  public function children() {
    return $this->hasMany(static::class, 'cat_parent_id')->orderBy('cat_name', 'asc');
  }
}

Once you have the relations defined properly, you can fetch whole category tree like below:

view()->composer('partials.header', function($view) {
  $view->with('categories', Category::with('children')->whereNull('cat_parent_id')->orderBy('cat_name', 'asc')->get());
});

The second composer won't be needed, as parent categories already contain children.

Now, you only need to display the categories in your view:

<ul>
  @foreach ($categories as $parent)
    <li>{{ $parent->cat_name }}
      @if ($parent->children->count())
        <ul>
          @foreach ($parent->children as $child)
            <li>{{ $child->cat_name }}</li>
          @endforeach
        </ul>
      @endif
    </li>
  @endforeach
</ul>

Answered By - jedrzej.kurylo