Jordan Vit Jordan Vit - 4 months ago 72
MySQL Question

Showing Sub-categories on menu only if has products with Laravel

I have simple query which populate menu with Main categories and sub-categories. This work perfectly

// in controller
$cat=Categories::all();
View::share('categories_menu',$cat);

// in view
@foreach($categories_menu as $category_menu)
@if($category_menu->has('subcategories'))
<ul class="nav nav-stacked cat-nav">
<li>
<a href="#">{{ $category_menu['category_name'] }} </a>
@if($category_menu->subcategories->count())
<ul>
@foreach($category_menu->subcategories as $subcategory)
<li class=""><a href="#">{{$subcategory->sub_cat_name}}</a></li>
@endforeach
</ul>
@endif
</li>
@else
<li><a href="#"><i class="fa fa-link"></i> <span>{{ $category_menu->category_name }}</span></a></li>
@endif
</ul>
@endforeach


This make output like

Main Category
-Sub Cat
-Sub Cat
Second main category
-Sub in second main category
...


The problem is that it is loading also sub-categories which are empty. I don't want to show them and I tried to change the query with joining all three tables -
category
,
sub_categories
,
products


This is the query so far

$cat = Categories::select('*', DB::raw('category.category_id AS category_id'))
->leftJoin('products', function($join) {
$join->on('products.category_id', '=', 'category.category_id');
})
->leftJoin('sub_category', function($secondjoin){
$secondjoin->on('sub_category.category_id', '=', 'category.category_id');
})
->whereNotNull('products.sub_cat_id')
->get();


The result is strange ( image )
enter image description here

It's should show me only two main categories each with one sub. On the image you can see my product table

Category 1
-sub1
Category 2
-sub3


enter image description here

If need some more source or something i can provide.

Update with models:

Categories model

class Categories extends Eloquent {
protected $table = 'category';
protected $primaryKey = 'category_id';

public $timestamps = false;

public function products()
{
return $this->hasMany('Product', 'category_id');

}
public function subcategories()
{
return $this->hasMany('SubCategories', 'category_id');
}
}


SubCategories model

class SubCategories extends Eloquent {
protected $table = 'sub_category';
protected $primaryKey = 'sub_cat_id';

public $timestamps = false;

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

public function products()
{
return $this->hasMany('Product', 'sub_cat_id');
}
}


Products model

class Product extends Eloquent {
protected $table = 'products';
protected $primaryKey = 'product_id';

public function categories()
{
return $this->hasMany('Categories', 'category_id');
}

public $timestamps = false;
}

Answer
 @foreach($categories_menu as $category_menu)
    @if($category_menu->has('subcategories'))
         <ul class="nav nav-stacked cat-nav">
              <li>
                  <a href="#">{{ $category_menu['category_name'] }} </a>
                        @if($category_menu->subcategories->count())
                          <ul>
                            @foreach($category_menu->subcategories as $subcategory)
                              @if($subcategory->products->count())
                                 <li class=""><a href="#">{{$subcategory->sub_cat_name}}</a></li>
                              @endif
                            @endforeach
                          </ul>
                        @endif
               </li>
            @else
               <li><a href="#"><i class="fa fa-link"></i> <span>{{ $category_menu->category_name }}</span></a></li>
            @endif
        </ul>
@endforeach

I added a condition for subcatergy to be displayed only if their products count is above 0.

Comments