get category list (including multiple subcategories) of an article

So your categories are a hierarchy and you want to retrieve all the ancestors for any given category.

If you're not defining the structure of the whole tree in your schema you'll most likely need to make several queries, or have a helper of some sort that does, querying for the parent(s) until parent id is null/0. You might be able to do that with a recursive function on the model, whatever the case it'll be multiple queries.

This might be useful: http://stackoverflow.com/questions/27816738/laravel-parent-child-relationship-on-the-same-model

There are many ways of storing hierarchical data in SQL, search sql tree traversal and you'll find many approaches. There are ways of storing data so that the entire hierarchy can be fetched in a single query. Writes are more costly as they involve updating every or nearly every other record in the tree, but something like that may be preferable in your situation.

Hello Willvincent,

Thanks for your answer. I did some research and found this solution but it doesn't quite work yet, I get the error: Trying to get property of non-object

This is the code I use in my view:

@php
$catlist = $product->category->name;
if($product->category->parent != null){
  $catlist .= " / " . $product->category->parent->name;
}
if($product->category->parent->parent != null){
  $catlist .= " / " . $product->category->parent->parent->name;
}
if($product->category->parent->parent->parent != null){
  $catlist .= " / " . $product->category->parent->parent->parent->name;
}
if($product->category->parent->parent->parent->parent != null){
  $catlist .= " / " . $product->category->parent->parent->parent->parent->name;
}
@endphp

If I try this in Tinker:

$product->category->parent

or

$product->category->parent->parent

and so on... it works, until the final parent returns 'null'.

What am I doing wrong?

I found the solution!

In my CategoriesController, I added some functions:

public function getCategoryTreeIDs($catID) {
  $row = Category::where('id', $catID)->first();
  $path = array();
  if (!$row->parent_id == '') {
    $path[] = $row->parent_id;
    $path = array_merge($this->getCategoryTreeIDs($row->parent_id), $path);
  }
  return $path;
}

public function getCategoryTitle($id){
  $c = Category::where('id', $id)->first();
  return $c->name;
}

The first function returns an array containing the category id's The second function returns the category name by id

In my blade view that lists my articles, I inject the CategoriesController and in the @foreach productlist, I run some php code:

@inject('extra', 'App\Http\Controllers\CategoriesController')
@foreach($products as $product)
  <tr>
    <td>{{ $product->id }}</td>
    <td>{{ $product->description }}</td>
    <td>
      @php
      $cats = $extra->getCategoryTreeIDs($product->category->id);
      if(count($cats)>0){
        foreach($cats as $cid){
          echo $extra->getCategoryTitle($cid) . " -> ";
        }
      }
      @endphp
      {{ $product->category->name }}
    </td>
  </tr>
@endforeach

This gives me the category 'breadcrumb' list:

Main Category -> Sub Category -> Sub Category 2 -> etc...

Maybe there are better ways to to this, I would be happy to read it here!

Greetingz!

I did this to get a path from my belongsToMany categories relationship using an accessor on the product model (my categories model has an alias column so I implode them with '/') when accessing $product->path

public function getPathAttribute($value)
    {
        return collect($this->categories()->get())->implode('alias','/');
    }

when you use $category->allParents it will return all the parents of the category, same with allChilds, you can try in the Tinker to see how it structures

    public function parent()
    {
        return $this->belongsTo(Category::class);
    }

    public function childs()
    {
        return $this->hasMany(Category::class, 'parent_id');
    }
    
    public function allParents()
    {
        return $this->parent()->with('allParents');
    }

    public function allChilds()
    {
        return $this->childs()->with('allChilds');
    }