php – How to retrieve products of a nested category using Many-to-Many relationship in Laravel and order the result?-ThrowExceptions

Exception or error:

I want to retrieve products of the selected category, as well as sub-categories and I may even need to order them according to price or date. Please let me know what changes should be made to the controller!

I am using a Many to Many Relationship for the 2 tables: Categories and Products, and a category_product to relate both.

Example

(Not all categories have sub-categories)

Gifts

Books

Toys

  • Boys

  • Girls

Phone

  • Samsung

  • Nokia

If a user clicks Phone, all products of the category ‘Phone’, ‘Samsung’ or ‘Nokia’ should appear!

Database

Products: id, name, price, created_at
Categories: id, name, slug, parent_id, sorting_order, created_at
category_product: id, category_id, product_id

Code:

Category Model:

class Category extends Model
{
    public function products()
    {
        return $this->belongsToMany('App\Product');
    }

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

    public function children()
    {
        return $this->hasMany('App\Category', 'parent_id');
    }
}

Product Model

class Product extends Model
{
    public function categories()
    {
        return $this->belongsToMany('App\Category');
    }
}
class ProductController extends Controller {

    public function index($slug, Request $request)
    {
        if ( ! isset($_GET['sortBy']))
        {
            $category = Category::where('slug', $slug)->with('products')->first();

            if ( ! $category)
            {
                abort(404, 'Page not found');
            }
        }
        else
        {
            $slug = $request->segments()[1];
            $products = Category::where('slug', $slug);
            switch ($request->sortBy)
            {
                case 'latest':
                    $category = $products->with(['products' => function ($q) {
                        $q->orderBy('created_at', 'desc');
                    }])->first();
                    break;
                case 'asc':
                    $category = $products->with(['products' => function ($q) {
                        $q->orderBy('price', 'asc');
                    }])->first();
                    break;
                case 'desc':
                    $category = $products->with(['products' => function ($q) {
                        $q->orderBy('price', 'desc');
                    }])->first();
                    break;
                default:
                    $category = $products->with('products')->first();
                    break;
            }
        }

        return view('products', compact('category'));
    }
}

View

<form id="sortProducts" class="form-inline" method="get">
   {{ csrf_field() }}
   <label for="sortBy">Sort By:</label>
   <select name="sortBy" id="sortBy">
      <option value="latest">Latest</option>
      <option value="asc">Price Low to Hight</option>
      <option value="desc">Price High to Low</option>
   </select>
</form>

@foreach($category->products as $product)
    <div class="product">
        <img border="0" src="{{Voyager::image($product->image)}}" alt="{{$product->name}}">
        <div class="product-name">{{$product->name}}</div>
        <div class="product-price">${{$product->price}}</div>
    </div>
@endforeach

I am using Laravel Version 6.2 along with Voyager Version 1.3.

How to solve:

If your category depth is unlimited, you’ll need a recursive relationship.

I think something like this could work:

In your Category Model:

public function nestedStructure()
{
   return $this->children()->with([
         'nestedStructure',
         'products' => function($q) {
             $q->orderBy('created_at', 'desc');
         }
   );
}

Answer:

Okay so first you need to modify your Category Model to get all children along with parent

Category Model

class Category extends Model
{
    public function products()
    {
        return $this->belongsToMany('App\Product');
    }

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

    public function children()
    {
        return $this->hasMany('App\Category', 'parent_id');
    }

    // recursive, loads all descendants
    public function recursiveChildren()
    {
       return $this->children()->with('recursiveChildren');
    }
}

Now according to given many-to-many relation a single Product might belong to multiple categories but we wont like to have same product show up again and again. So a possible fix for your Controller can be

class ProductController extends Controller {

    public function index($slug, Request $request)
    {

        $categories = Category::where('slug', $slug)->with('recursiveChildren')->whereNull('parent')->get();

        if($categories->count() == 0) abort(404, 'Page not found');

        $products = collect([]); // its a helper method to make collections

        foreach($categories as $category) {
            $category_products = $category->products;
            foreach($category_products as $product) {
               if(!$products->contains($product)) $products->push($product);
            }
        }

        if($request->input('soryBy')) {
            switch ($request->sortBy)
            {
                case 'latest':
                    $products = $products->sortBy('created_at');
                    break;
                case 'asc':
                    $products = $products->sortBy('price');
                    break;
                case 'desc':
                    $products = $products->sortByDesc('price');
                    break;
                default:
                    $products;
                    break;
            }
        }

        return view('products', compact('products'));
    }
}

Now lets modify the view a little

<form id="sortProducts" class="form-inline" method="get">
   {{ csrf_field() }}
   <label for="sortBy">Sort By:</label>
   <select name="sortBy" id="sortBy">
      <option value="latest">Latest</option>
      <option value="asc">Price Low to Hight</option>
      <option value="desc">Price High to Low</option>
   </select>
</form>

@foreach($products as $product)
    <div class="product">
        <img border="0" src="{{Voyager::image($product->image)}}" alt="{{$product->name}}">
        <div class="product-name">{{$product->name}}</div>
        <div class="product-price">${{$product->price}}</div>
    </div>
@endforeach

Answer:

There are at least two solutions.

Solution 1 (Pure Laravel):

Add these two methods to your Category model:

public function descendants()
{
    $collection = new \Illuminate\Support\Collection();

    foreach ($this->children as $child) {
        $collection->add($child);
        $collection = $collection->merge($child->descendants());
    }

    return $collection;
}

public function getRouteKeyName()
{
    return 'slug';
}

And use it in your ProductController controller like so:

class ProductController extends Controller
{
    public function index(Request $request, Category $category)
    {
        $categories = $category->descendants()->add($category)->pluck('id');

        $products = DB::table('category_product AS cp')
            ->join('products', 'cp.product_id', '=', 'products.id')
            ->select('products.*')
            ->whereIn('cp.category_id', $categories)
            ->get();

        return view('products', compact('category', 'products'));
    }
}

You can then output them in your view file:

@forelse($products as $product)
    <div class="product">
        <img border="0" src="{{ Voyager::image($product->image) }}" alt="{{ $product->name }}">
        <div class="product-name">{{ $product->name }}</div>
        <div class="product-price">${{ $product->price }}</div>
    </div>
@empty
    <div class="product">
        There are no products in this category.
    </div>
@endforelse

Solution 2 (Using a package):

First of all, install the package:

composer require kalnoy/nestedset

Replace parent_id column with $table->nestedSet(); in your categories table and related migration file:

Schema::create('categories', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->string('name');
    $table->string('slug')->unique();
    $table->nestedSet();
    $table->timestamps();
});

Then, update your Category model like so:

use Illuminate\Database\Eloquent\Model;
use Kalnoy\Nestedset\NodeTrait;

class Category extends Model
{
    use NodeTrait;

    protected $fillable = [
        'name',
        'slug',
    ];

    public function products()
    {
        return $this->belongsToMany('App\Product');
    }

    public function parent()
    {
        return $this->belongsTo(self::class, 'parent_id');
    }

    public function children()
    {
        return $this->hasMany(self::class, 'parent_id');
    }

    public function getRouteKeyName()
    {
        return 'slug';
    }
}

You can now use it in your controller:

class ProductController extends Controller
{
    public function index(Request $request, Category $category)
    {
        $categories = Category::descendantsAndSelf($category->id)->pluck('id');

        $products = DB::table('category_product AS cp')
            ->join('products', 'cp.product_id', '=', 'products.id')
            ->select('products.*')
            ->whereIn('cp.category_id', $categories)
            ->get();

        return view('products', compact('category', 'products'));
    }
}

You can output as shown in Solution 1.


Please note that I assumed you use {category} key in your route definition. (See Route Model Binding) For example:

Route::get('/products/category/{category}', 'ProductController@index');

Read the documentation first to create, update and delete an item in a nested set (categories).

Leave a Reply

Your email address will not be published. Required fields are marked *