title: Showing Sub-categories on menu only if has products with Laravel
body: <p>I have simple query which populate menu with Main categories and sub-categories. This work perfectly</p>
  
  <pre><code>  // in controller
  $cat=Categories::all(); 
  View::share('categories_menu',$cat); 
  
   // in view
   @foreach($categories_menu as $category_menu)
      @if($category_menu-&gt;has('subcategories'))
           &lt;ul class="nav nav-stacked cat-nav"&gt;
                &lt;li&gt;
                    &lt;a href="#"&gt;{{ $category_menu['category_name'] }} &lt;/a&gt;
                          @if($category_menu-&gt;subcategories-&gt;count())
                            &lt;ul&gt;
                              @foreach($category_menu-&gt;subcategories as $subcategory)
                                &lt;li class=""&gt;&lt;a href="#"&gt;{{$subcategory-&gt;sub_cat_name}}&lt;/a&gt;&lt;/li&gt;
                              @endforeach
                            &lt;/ul&gt;
                          @endif
                 &lt;/li&gt;
              @else
                 &lt;li&gt;&lt;a href="#"&gt;&lt;i class="fa fa-link"&gt;&lt;/i&gt; &lt;span&gt;{{ $category_menu-&gt;category_name }}&lt;/span&gt;&lt;/a&gt;&lt;/li&gt;
              @endif
          &lt;/ul&gt;
  @endforeach
  </code></pre>
  
  <p>This make output like</p>
  
  <pre><code>Main Category
     -Sub Cat
     -Sub Cat
  Second main category
     -Sub in second main category
     ...
  </code></pre>
  
  <p>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 - <code>category</code>, <code>sub_categories</code>, <code>products</code></p>
  
  <p>This is the query so far</p>
  
  <pre><code>$cat = Categories::select('*', DB::raw('category.category_id AS category_id'))
      -&gt;leftJoin('products', function($join) {
               $join-&gt;on('products.category_id', '=', 'category.category_id'); 
             })
      -&gt;leftJoin('sub_category', function($secondjoin){
              $secondjoin-&gt;on('sub_category.category_id', '=', 'category.category_id');
             })
      -&gt;whereNotNull('products.sub_cat_id')
      -&gt;get();
  </code></pre>
  
  <p>The result is strange ( image )
  <a href="https://i.stack.imgur.com/EhnMG.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/EhnMG.jpg" alt="enter image description here"></a></p>
  
  <p>It's should show me only two main categories each with one sub. On the image you can see my product table</p>
  
  <pre><code>Category 1
    -sub1
  Category 2
    -sub3
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/z4KGd.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/z4KGd.jpg" alt="enter image description here"></a></p>
  
  <p>If need some more source or something i can provide.</p>
  
  <p>Update with models:</p>
  
  <p>Categories model</p>
  
  <pre><code>class Categories extends Eloquent {
      protected $table = 'category';
      protected $primaryKey = 'category_id';
  
      public $timestamps = false;
  
      public function products()
      {
          return $this-&gt;hasMany('Product', 'category_id');
  
      } 
      public function subcategories()
      {
          return $this-&gt;hasMany('SubCategories', 'category_id');
      } 
  }
  </code></pre>
  
  <p>SubCategories model</p>
  
  <pre><code>class SubCategories extends Eloquent {
      protected $table = 'sub_category';
      protected $primaryKey = 'sub_cat_id';
  
      public $timestamps = false;
  
      public function category()
      {
          return $this-&gt;belongsTo('Category', 'category_id');
      }
  
      public function products()
      {
         return $this-&gt;hasMany('Product', 'sub_cat_id');
      } 
  }
  </code></pre>
  
  <p>Products model</p>
  
  <pre><code>class Product extends Eloquent {
      protected $table = 'products';
      protected $primaryKey = 'product_id';
  
      public function categories()
      {
          return $this-&gt;hasMany('Categories', 'category_id');
      }
  
      public $timestamps = false;
  }
  </code></pre>