How set active condition on subrequest?-CodePudding

As Product model has many to many relations with Category (using ProductCategory model).

I try to add an additive condition in the request.

$productsWithActiveCategories = Product 
    ::getByTitle($filter_title)
    ->getByStatus('A')
    ->getByPublishedAt($filter_date_from, '>=')
    ->getByPublishedAt($filter_date_till, '<')
    ->with('creator')
    ->with('productCities')
    ->with('productCategories') // product_categories
    ->with('productCategories.category')
    ->getByActiveCategory( true)

    ->orderBy('regular_price', 'asc')
    ->get();

But I got error :

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ts_categories.active' in 'where clause'
select
  *
from
  `ts_products`
where
  `ts_products`.`title` like % no %
  and `ts_products`.`status` = A
  and ts_products.published_at >= 2022 -04 -01
  and ts_products.published_at < 2022 -04 -21
  and `ts_categories`.`active` = 1
order by
  `regular_price` asc

In app/Models/Product.php model I added :

public function scopeGetByActiveCategory($query, $active = null)
{
    if ( ! isset($active) or strlen($active) == 0) {
        return $query;
    }

    return $query->where(with(new Category)->getTable() . '.active', $active);
}

It looks like error is raised as this scope is applied to 1st request, not subrequests with all productCategories and productCategories.category as I need.

How can I set this scope to productCategories.category relation ?

UPDATED BLOCK : In app/Models/Product.php I added 2 methods :

public function categories()
{
    return $this->hasManyThrough(ProductCategory::class, Category::class);
}

public function scopeOnlyActiveCategories()
{
    return $this->hasManyThrough(ProductCategory::class, Category::class)
                ->where(with(new Category)->getTable() . '.active', true);
}

but when I tried to use 2nd method in request :

   $productsWithActiveCategories = Product   
    ::getByTitle($filter_title)
    ->getByStatus('A')
    ->getByPublishedAt($filter_date_from, '>=')
    ->getByPublishedAt($filter_date_till, '<')
    ->onlyActiveCategories()
    ->with('creator')
    ->with('productCities')
    ->with('productCategories') // product_categories
    ->with('productCategories.category')
    ->orderBy('regular_price', 'asc')
    ->get();

I got error :

 Column not found: 1054 Unknown column 'ts_categories.product_id' in 'field list'

What is wrong ?

Thanks!

CodePudding user response：

Instead of ->getByActiveCategory(true), you can use the dot nested notation in whereHas to filter your model like following :

$productsWithActiveCategories = Product 
    (...)
    ->whereHas('productCategories.category', function ($category) {
        $category->where('active', 1);
    })
    (...)
    ->get();