best approach to search from pivot table using laravel

1
votes

Searching from pivot table using laravel.

Here is my table structure:

Product
id
name

Categories
id
name

product_category (Pivot table)

id
category_id
product_id

//products can have multiple categories

Product model:

public function categories(){
     return $this->belongsToMany(Category::class, 'product_category');
}

What is the best way to search all products by category id? Currently I am doing this way, and it seems not an efficient way:

//Controller
$categories = product_category::where('category_id',1)->get();

Now I have to loop through categories and then get Products and pass it to views? Any idea how to do this in an efficient way?

3
laraveleloquentpivottable.js

3 Answers

6
votes

For this you could use the whereHas() method:

$categoryId = 1;

$products = Product::whereHas('categories', function ($query) use($categoryId) {
    $query->where('id', $categoryId);
})->get();

The above will return all products that are in the Category where the id is equal to $categoryId.

1
votes

You can eager load products for a given category. Try:

$category = Category::with('products')->where('category_id',1)->find(1);

When you do this, only 2 database queries will be executed: one for loading the category, and one for loading related products.

Then in your Blade view you can do:

@foreach($category->products as $product
    {{ $product->name }}
@endforeach
0
votes

You can use this inside your method in controller..this only works when $request->$query(search) have value .then here we use wereHas for get the relationship of model and with->() using for get pivot table values

->when($request->query('search'), function ($query)use($request) {

   $q= $request->query('search');

   return $query->whereHas('relation name', function (Builder $query) use ($q) {
                  
   $query->with('pivot table name.column name')  

  ->where('pivot table name.column name', 'like', "%{$q}%")
     });})