Query products with parent category

I have a problem with displaying the list of products on subpages of product categories.

I created two models: Products and Categories.

Products model use $belongsTo (categories) relation.

Categories model use \October\Rain\Database\Traits\NestedTree to create nested categories and $hasMany (products) relation.

URL structure for category pages: "/products/:slug?/:sub?"

In component I use that code:

if ($this->param('sub')) {
  // Child category - that works
  $query = productsModel::orderBy($order, $orderDirection)->get();
  $currentcategory = categoriesModel::where('slug', $this->param('sub'))->first();
  $query = $query->where('categories_id', $currentcategory->id);
} elseif ($this->param('slug')) {
  // Parent category / Category without subcategories
  // How to get all products whose parent category slug is equal $this->param('slug')?

I’ve tried various things but nothing works. I will be grateful for help

1 Like

may be it will help you

$query = productsModel::whereRelation('your_category_column_name', 'slug',$this->param('sub')).where('slug',$this->param('slug')).orderBy($order, $orderDirection)->get();

result of this is this
$query->your_category_column... relation

answer to question

products.whereRelation('main_category', 'slug', $this->param('slug')).paginate(12) - it is write in twig 

@mcore unfortunately it works on sub category pages but not on parent category page. On the product edit page, I select only one category from the drop-down list.

A couple of ways to handle this

  • Add where('parent_id', null) to the query to request only the parents
  • Use getNested() to request everything, returning only the parents with the children eager loaded

Hopefully this helps