How to retrive data from database to a treeview using laravel

Question

My requirement is to construct the treeview using database values.

Here are my database tables:

|    Categories   |  |sub_categories   |
|     id(pk)      |  |    id(pk)       |
|  cate_name      |  | sub_cat_name    |
| route_name      |  | route_name      |
                     |Categories_id(fk)|

I'm getting categories and sub categories as well which are related to categories table.

Here is my Controller code:

$treeView = DB::table('categories')
                ->join('sub_categories', 'sub_categories.categories_id', '=', 'categories.id')
                ->get();

Here is the HTML structure in the *.blade.php :

@foreach($treeView as $tv)
                <li class="treeview">
                    <a href="#"><i class="fa fa-link"></i> <span>{{ $tv->category_name }}</span> <i
                                class="fa fa-angle-left pull-right"></i></a>
                    <ul class="treeview-menu">
                        <li class=""><a href="#">{{$tv->sub_category_name}}</a></li>
                        <li><a href="#">Update Article</a></li>
                    </ul>
                </li>
            @endforeach

But it doesn't work fine. It gives same main category again and again.. Can anyone suggest a proper way to retrieve data?

no .. I'm not using eloquent... I'm using Illuminate\Support\Facades\DB. — PereraSH
– PereraSH, Commented Jul 19, 2015 at 9:39
Well, it's not that you cannot have both ;) Please check back in half a day, I'll apot an answer when i am at the computer if noone answers by then — jedrzej.kurylo
– jedrzej.kurylo, Commented Jul 19, 2015 at 9:58

jedrzej.kurylo · Accepted Answer · 2015-07-19 16:13:04Z

4

I suggest you use Eloquent, as it will make the code simpler and will make your life easier in the future.

Create model classes for your tables:

class Category extends Illuminate\Database\Eloquent\Model {
  protected $table = 'Categories';

  public function subcategories() {
    return $this->hasMany(Subcategory::class, 'Categories_id');
  }
}

class Subcategory extends Illuminate\Database\Eloquent\Model {
  protected $table = 'sub_categories';

  public function category() {
    return $this->belongsTo(Category::class, 'Categories_id');
  }
}

In your controller fetch data like that:

$treeView = Category::with(['subcategories'])->get();

And then in the view:

@foreach($treeView as $category)
  <li class="treeview">
    <a href="#"><i class="fa fa-link"></i> <span>{{ $category->cate_name }}</span> <i class="fa fa-angle-left pull-right"></i></a>
    <ul class="treeview-menu">
      @foreach($category->subcategories as $subcategory)
        <li class=""><a href="#">{{$subcategory->sub_category_name}}</a></li>
      @endforeach
    </ul>
  </li>
@endforeach

I can see that your categories and subcategories have the same stucture. You might consider storing them in the same table, just add a parent_id field to the table and set it to NULL for parent categories and to parent id for subcategories.

edited Jul 19, 2015 at 16:13

answered Jul 19, 2015 at 14:23

jedrzej.kurylo

41.1k10 gold badges108 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

PereraSH Over a year ago

thanks for your quick reply.. I tried using your code example. But It gives me the error BadMethodCallException in Builder.php line 2003: Call to undefined method Illuminate\Database\Query\Builder::all()

PereraSH Over a year ago

I created Category and Subcategory classes in app/ directory as u mentioned...

PereraSH Over a year ago

I wanted to check if the category has subcategories else do another work.. I tried it like below. But it didnt retrieve the required answer .. Is anything wrong with it ??

PereraSH Over a year ago

@foreach($treeView as $category) @if($category->has('subcategories')) <li class="treeview"> <a href="#"><i class="fa fa-link"></i> <span>{{ $category->category_name }}</span>  <i class="fa fa-angle-left pull-right"></i></a> <ul class="treeview-menu">                 @foreach($category->subcategories as $subcategory) <li class=""><a href="#">{{$subcategory->sub_category_name}}</a></li> @endforeach      </ul> </li> @else <li><a href="#"><i class="fa fa-link"></i> <span>{{$category->category_name }}</span></a></li> @endif  @endforeach

jedrzej.kurylo Over a year ago

Where did you get that has() idea? Anyway, see the answer in stackoverflow.com/questions/31509536/…

Collectives™ on Stack Overflow

How to retrive data from database to a treeview using laravel

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related