Pulling all categories and grouping them by parent id

Question

I am working with Laravel data querying and I need a query that is going to group all the children of a parent when I take the categories.

the categories table has a name and a parent_id, the routes of the categories have the parent_id set as null, the query should return every category grouped by parent id and the parent should be the first node of every group.

Answer 1

If you only want to display the categories as parent child somewhere, you do not need to collect them like that, you can make a relationship within the model like

class Category {
    public function children()
    {
        return $this->hasMany(self::class, 'parent_id');
    }

    public function parent()
    {
        return $this->hasMany(self::class, 'id', 'parent_id');
    }
}

may be it will be one-to-many relationship instead of many-to-many depending on your requirement.

Now you can just get all the parents like

Category::whereNull('parent_id')->get();

or using a scope

Category::parent()->get(); and define the scope in the model

and loop through the parent category like

@foreach ( $categories as $category ) 
       {{ $category->name }}
       @foreach ( $category->children as $subCategory )
           {{ $subCategory->name }}
       @endforeach
@endofreach

and to retrieve parent with children you can use

Category::whereNull('parent_id')->with('children')->get();

or

Category::parent()->with('children')->get();

I have not tested the code, but roughly it will be like this.