Laravel call relation from model in when() conditional clauses-CodePudding

Hello am creating product filtering and now i have problem when i want to filter product category by name.

Am not shure if is posible to call relation inside when() conditional clauses.

In product controller i create:

$results = Product::query();
    
// Contional filter
  
$results->when($request->has('product_code'), function ($q) use ($request) {
  return $q->where('product_code', 'like', '%'.$request->query('product_code').'%');
});

$results->when($request->has('status'), function ($q) use ($request) {
  return $q->where('status', $request->query('status'));
});

$results->when($request->has('price'), function ($q) use ($request) {
  return $q->where('price', $request->query('price'));
});

// Filter by category NAME  
// HERE IS PROBLEM

$results->when($request->has('category_name'), function ($q) use ($request) {
  return $q->category->where('name', $request->query('cat_name'));
});

// I also try 
// This return SQLSTATE[42S22]: Column not found: 1054 Unknown column 'category.name' in 'where clause' (SQL: select count(*) as aggregate from `products` where `category`.`name` = Apple)

$results->when($request->has('category_name'), function ($q) use ($request) {
  return $q->where('category.name', $request->query('cat_name'));
});


$results = $results->paginate(20);

Model:

class Product
{
  public function category()
    {
        return $this->hasOne('App\Models\Category', 'id', 'category_id');
    }
}

CodePudding user response：

for this filtering do this:

        $filter = [];
        if($request->product_code!= null)
              array_push($filter, ['product_code', 'like', "%$request->product_code%"]);
        if($request->price)
            array_push($filter, ['price', $request->price]);
        // and so on ...

then you have array of conditions, just call where and pass the array:

        $results = Product::where($filter);

CodePudding user response：

How about that?

$category_ids = Category::where('category_name', $request->query('category_name'))->pluck('id');

$results = Product::select("*")->when($request->has('category_name'), function ($query) use ($request, $category_ids) {
  return $query->whereIntegerInRaw('category_id', $category_ids);
})->get();