Pulling all categories and grouping them by parent id

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

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.