Laravel query or function for nested/ infinite sub category

Question

Category table

id  | parent_id | name                   |
1   |   0           |Human Resource |
2   |   1           |Settings                |
3   |   2           |Departments        |
4   |   3           |Positions              |

I want to get like this:

Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [parent_id] => 0
            [name] => Human Resource
            [created_at] => 0000-00-00 00:00:00
            [updated_at] => 2016-03-08 05:43:09
            [sub_category] => Array
                (
                    [0] => stdClass Object
                        (
                            [id] => 4
                            [parent_id] => 1
                            [page_name] => Settings
                            [icon] => 
                            [created_at] => 2016-03-09 11:53:35
                            [updated_at] => 2016-03-08 05:52:34
                        )

                )

        )

)

Note: I want to have a dynamic/ infinite sub categories. No limit for level of sub categories.

I already have a query like this:

$categories = DB::table('category')->where('parent_id',0)->get();
foreach ($categories as $key => $value) {
    $value->sub_category = DB::table('category')->where('parent_id',$value->id)->get();
}

But how can I make this recursive loop? because I want to have infinite level of sub category.

> Parent cat
> Parent cat
  > sub cat
    > second sub cat
     > third sub cat

And so on etc.

Answer 1

You can use this Controller

public function create()
{
    $categories = Category::all();
    return view('backend.categories.create')->with('categories', $categories);
}

Model

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Category extends Model
{
    protected $guarded = ['id'];

    public function parent()
    {
        return $this->belongsTo('App\Category', 'parent_id');
    }

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

View

<table class="table table-bordered table-hover">
                <thead>
                    <tr>
                        <th>Name</th>
                        <th>Description</th>
                        <th>Slug</th>
                        <th>Action</th>
                    </tr>
                </thead>
                <tbody>
                @foreach ($categories as $category)
                    <tr>
                        <td>{{ $category->name }}</td>
                        <td>{{ $category->description }}</td>
                        <td>{{ $category->slug }}</td>
                        <td><a class="edit" href="{!! action('Admin\CategoriesController@edit', $category->id) !!}" title="Edit"><i class="fa fa-pencil-square-o"></a></i> <a class="delete" href="{!! action('Admin\CategoriesController@destroy', $category->id) !!}" title="Are you sure you want to delete?"><i class="fa fa-trash-o"></i></a></td>
                        @foreach ($category->children as $children)
                            <tr>
                                <td>{{ $children->name }}</td>
                                <td>{{ $children->description }}</td>
                                <td>{{ $children->slug }}</td>
                                <td></td>
                            </tr>
                        @endforeach
                    </tr>
                </tbody>

                @endforeach
            </table>

And then make children loop with condition if it has run the loop

Answer 2

You can use this.

public function view() {
$categories = Category::with('children')->get();

return compact('categories');  // or use json_encode
}