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.

Check this: https://stackoverflow.com/questions/34758965/eloquent-parent-child-relationship-on-same-model — namelivia, Apr 16 '19 at 08:51
this is not a nested set, the max depth will be parent and child, so depth is one. — Endrit Sheholli, Apr 16 '19 at 08:53

score 3 · Accepted Answer · answered Apr 16 '19 at 12:33

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.

score 0 · Answer 2 · answered Jan 04 '23 at 08:37

contoller

$locations   = OurLocation::groupBy('country_id')->with('children')->get();

model

public function children()
    {
        return $this->hasMany(OurLocation::class, 'country_id','country_id');
    }

blade

@foreach($locations as $index=>$location)
  @foreach($location->children as $children)
        {{ $children->company_name }} <br>
  @endforeach
@endforeach

score -2 · Answer 3 · answered Apr 16 '19 at 09:57

-2

When you get the returned collection from the query, you are able to use the ->groupBy() method where you can specify the field which the results should be grouped by.

Assuming your categories model is Category:

$categories = Category::all()->groupBy('parent_id')->toArray();

answered Apr 16 '19 at 09:57

thisiskelvin

4,136
1
10
17

this does not return a nested collection. It groups all categories with no parent, then all categories with parent_id 1, etc. OP: `should return every category grouped by parent id and the parent should be the first node of every group` – wivku Jan 16 '20 at 16:38

Pulling all categories and grouping them by parent id

3 Answers3