Laravel 5.2 - Get products belonging to the a category and products belonging to n child categories

I'm developing an eCommerce website using laravel 5.2. Each product belongs to one category, thus we added a CategoryID field on the products table.

When the user selects a certain category, we are able to show products that belong to that category. But now, the need has come to show products that belong to categories under the selected category.

Here is an example to clarify things

Categories

• Electronics -> Smartphones -> Apple -> iPhone
• Electronics -> Smartphones -> Android -> Samsung
• Electronics -> Laptops -> Lenovo

Products

• A product called iPhone 6S belongs to the iPhone Category
• A product called Galaxy S7 belongs to the Samsung Category
• A product called Lenovo x240 belongs to the Lenovo Category

The current system easily shows the products inside each category. But what we want to do now is :

• When i go to the Smartphones category, i should see the Galaxy S7 and the iPhone 6s
• When i go to the Electonics category, i should see the Galaxy S7, the iPhone 6s and the Lenovo x240

The category table has a CategoryID primary key, CategoryName and Parent. If parent = 0, it means the category has no parent

Category Model

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
class Categories extends Model
{
    protected $primaryKey = 'CategoryID';

    public function products()
    {
        return $this->hasMany('App\Product', 'CategoryID');
    }
    public function children(){
        return $this->hasMany('App\Category', 'Parent', 'CategoryID');
    }

    public function recursiveChildren()
    {
       return $this->children()->with('recursiveChildren');
   }

}

Product Model

<?php

namespace App;    
use Illuminate\Database\Eloquent\Model;    

class Product extends Model
{
  protected $primaryKey = 'ProductID';

  public function Category()
    {
        return $this->belongsTo('App\Category', 'CategoryID');
    }

}

What i've tried

I tried to show the products inside the categories using the laravel's eager loading

public function show($CategoryID)
{
    $products = Categories::with(array(
        'recursiveChildren' => function ($query) {
          $query->orderBy('ProductName', 'asc');
        }
        ))
       ->orderBy('Name', 'asc')
       ->where('CategoryID', '=', $CategoryID)
       ->get();

       return view('categories.show', compact('products '));
}

The funny thing is that the above query works as expected but it doesn't go more than level 2 in the category hierarchy. When i browse to the Electronics Category (CategoryID = 1), using DebugBar i can see that the above function generated the following query

select * from `categories` where `CategoryID` = '1' and `categories`.`deleted_at` is null order by `Name` asc

select * from `categories` where `categories`.`Parent` in ('1') and `categories`.`deleted_at` is null

select * from `categories` where `categories`.`Parent` in ('5', '17') and `categories`.`deleted_at` is null

select * from `categories` where `categories`.`Parent` in ('24', '31', '35') and `categories`.`deleted_at` is null

select * from `products` where `products`.`CategoryID` in ('5', '17') and `products`.`deleted_at` is null order by `Title` asc

The bottom query is partially right, but i don't know why it's not getting the products inside categories belonging to Electronics.

The last query should have been

select * from `products` where `products`.`CategoryID` in ('35', '31', '24', '17', '5') and `products`.`deleted_at` is null order by `ProductName` asc

So, here is my question

1. How would i generate a query like the one above

2. Is there a better option in terms of performance. Maybe a query that gets the required products with less number of queries

Thanks

via user3659497