Confusion over querying a belongsToMany relationship (products and categories)

I was hoping someone could help me with something I'm struggling with (which is probably reasonably simple), which is searching for some products and only returning those which belong to a specific category.

My products model has this:

public function categories()
  {
    return $this->belongsToMany(Productcategory::class, 'products_productcategories', 'product_id', 'productcategories_id');
  }

and my products productcategory model has this:

  public function products()
  {
    return $this->belongsToMany(Product::class, 'products_productcategories', 'productcategories_id', 'product_id');
  }

In the 'products_productcategories' pivot table, I have 'productcategories_id' and 'product_id' columns.

When I do my search, I'm searching for product titles which contain a keyword (a variable called searchText) as well as a category ID (which will be a variable called searchCategory). If the category ID is empty, it ignores the categories and searches for all products. That works fine:

$search_results = Product::select("*")
        ->orderby('created_at', 'desc')
        ->where('type', '!=', 'grouped')
        ->where('title', 'like', '%' . $searchText . '%')
        ->orWhere('search_terms', 'like', '%' . $searchText . '%')
        ->paginate(20);

What I don't understand, is how to structure my search query so that it returns the products but only if they're in the category selected by the user.

So, using the above code as a starting point, how would I adapt it to pull only products which belong to a category with an ID of (say) 6?

Any help would be appreciated!

Sinnbeck

3 years ago

Best Answer

Level 102

Almost there. You just need a bit of chaining

$category = Productcategory::find(6);
$search_results = $category->products()
        ->orderby('created_at', 'desc')
        ->where('type', '!=', 'grouped')
        ->where('title', 'like', '%' . $searchText . '%')
        ->orWhere('search_terms', 'like', '%' . $searchText . '%')
        ->paginate(20);

1 like

Sinnbeck

3 years ago

Level 102

You can also just do this :)

$search_results = Product::select("*")
        ->orderby('created_at', 'desc')
       ->whereRelation('products', 'id', 6) //here
        ->where('type', '!=', 'grouped')
        ->where('title', 'like', '%' . $searchText . '%')
        ->orWhere('search_terms', 'like', '%' . $searchText . '%')
        ->paginate(20);

Sinnbeck

3 years ago

Level 102

One thing to be aware of. Anytime you add an orWhere you should scope your query using logical grouping https://laravel.com/docs/9.x/queries#logical-grouping

idcreatv

3 years ago

Level 3

That's brilliant, thanks for your help @sinnbeck (as usual). I knew it was probably simple but I just couldn't work It out. Will look into logical grouping next.

idcreatv

3 years ago

Level 3

For anyone who hits the same problem as me, this is my solution.

Category ID is selected from a dropdown (all categories has is '-1') and the text goes into the input:

if($request->ajax()){
    try {
      $searchCategory = $request->searchCategory;
      $searchText = $request->searchText;
      // No category selected
      if($searchCategory == -1){
        $query = Product::select("*");
        $searchFields = ['title', 'search_terms', 'sku'];
        $query->where('type', '!=', 'grouped');
        $query->where(function($query) use($request, $searchFields){
          $searchWildcard = '%' . $request->searchText . '%';
          foreach($searchFields as $field){
            $query->orWhere($field, 'LIKE', $searchWildcard);
          }
        });
        $search_results = $query->paginate(20);
      }
      // Category selected
      if($searchCategory != -1){
      $category = Productcategory::find($searchCategory);
      $query = $category->products();
        $searchFields = ['title', 'search_terms', 'sku'];
        $query->where('type', '!=', 'grouped');
        $query->where(function($query) use($request, $searchFields){
          $searchWildcard = '%' . $request->searchText . '%';
          foreach($searchFields as $field){
            $query->orWhere($field, 'LIKE', $searchWildcard);
          }
        });
        $search_results = $query->paginate(20);
      }
      /* Return response */
      return response(['msg' => 'Successful', 'search_results' => $search_results], 200);
    } catch (\Exception $e) {
      /* Return response */
      return response(['msg' => 'Unsuccessful'], 401);
    }
}
}

Works a charm!

If anyone has a better/more efficient way of handling this, feel free to let me know. :)

Please or to participate in this conversation.