WherePivot and WherePivotIn – additional filters for many-to-many relationships

Recently I’ve found a small detail in Laravel documentation which I want to share with you. Apparently, it’s possible to filter pivot tables additionally, if needed. Let me show you how.

Imagine a simple situation: we have tables products, categories and a pivot table category_product with this relationship:

class Product extends Model
{

    public function category()
    {
        return $this->belongsToMany(Category::class);
    }
    
}

And there are a few products entered – one with each different category: category_id = 1 and category_id = 2:

screen-shot-2017-01-02-at-6-57-31-pm

And we want to show product list in the table – here’s how it looks (code and table generated by our QuickAdmin):

products list

As you can see, column Category lists all possible categories, code looks like this:


  @foreach ($product->category as $singleCategory)
     {{ $singleCategory->name }}
  @endforeach

And now, here’s a magic trick: what if for some reason we want to show only categories with
category_product.id = 1?

There are additional methods for that: wherePivot() or wherePivotIn().
Then relationship looks like this:

    public function category()
    {
        return $this->belongsToMany(Category::class)->wherePivot('id', 1);
    }

Or like this:

    public function category()
    {
        return $this->belongsToMany(Category::class)->wherePivotIn('id', [1]);
    }

And then what happens in our table? It shows only Category 1, even if there are entries with Category 2:

pivot table wherepivotin

Of course, you can also filter entries bigger/smaller than X, with ->wherePivot(‘id’, ‘>’, 1) or something like that.

As I said – small detail, but might be useful to avoid tons of code for filtering!

More on many-to-many relationships – in official documentation.

Like our articles?
Check out our Laravel online courses!

4 COMMENTS

  1. sir one product has many color and one color has many sizes so how to create relationship between products, colors, and sizes table i create table has
    product Table
    Schema::create(‘products’, function (Blueprint $table) {
    $table->id();
    $table->string(‘name’);
    $table->text(‘description’);
    $table->timestamps();
    });
    Colors Table
    Schema::create(‘colors’, function (Blueprint $table) {
    $table->id();
    $table->string(‘color’);
    $table->timestamps();
    });
    Size table
    Schema::create(‘sizes’, function (Blueprint $table) {
    $table->id();
    $table->string(‘size’);
    $table->timestamps();
    });
    Color_product table
    Schema::create(‘color_product’, function (Blueprint $table) {
    $table->id();
    $table->unsignedInteger(‘product_id’);
    $table->unsignedInteger(‘color_id’);
    $table->timestamps();
    });
    size_color table
    Schema::create(‘size_color’, function (Blueprint $table) {
    $table->id();
    $table->unsignedInteger(‘product_id’);
    $table->unsignedInteger(‘color_id’);
    $table->unsignedInteger(‘size_id’);
    $table->timestamps();
    });
    RelationShip
    product model ->
    public function colors()
    {
    return $this->belongsToMany(Color::class, ‘color_product’);
    }
    Size model->
    public function products()
    {
    return $this->belongsToMany(Product::class, ‘color_product’);
    }

    public function colors()
    {
    return $this->belongsToMany(Color::class, ‘size_color’);
    }
    Color Model
    public function products()
    {
    return $this->belongsToMany(Product::class, ‘color_product’);
    }

    public function sizes()
    {
    return $this->belongsToMany(Size::class, ‘size_color’, ‘color_id’,’size_id’)->withPivot(‘product_id’);
    }
    and my controller
    public function index()
    {
    DB::enableQueryLog();
    $productDatas = Product::with([‘colors’ => function($query){
    $query->with([‘sizes’ => function($query){
    $query->join(‘products’, ‘size_color.product_id’, ‘=’, ‘products.id’);
    }]);
    }])->get();
    //
    dump($productDatas->toArray());
    dd(DB::getQueryLog());

    //$productDatas = Product::with(‘colors.sizes’)->get();
    //dd($productDatas->toArray());
    }
    but not achive accepted result
    1 product has 3 color(red,green,blue) and each color has 3 size SML but product-2 has 3 colors(R,G,B) and each color has no size but this code contooller code product-2 give same color and size which have product-1 how to handel

  2. Seeder of ColorSeeder
    public function run()
    {
    $color = [
    [‘color’=>’red’],
    [‘color’=>’green’],
    [‘color’=>’blue’]
    ];

    Color::insert($color);

    }
    SizeSeeder:
    public function run()
    {
    $size = [
    [‘size’=>’S’],[‘size’=>’M’],[‘size’=>’L’]
    ];

    Size::insert($size);
    }
    ProductSeeder:
    public function run()
    {
    $product = [
    [‘name’=>’Shirt’, ‘description’ =>’Shirt product’],[‘name’=>’T-Shirt’, ‘description’ =>’T-Shirt product’],[‘name’=>’Polo’, ‘description’ =>’Polo product’]
    ];

    Product::insert($product);
    }
    ProductColorSeeder:
    $productAttribute = [
    [‘product_id’ => 1, ‘color_id’ => 1],
    [‘product_id’ => 1, ‘color_id’ => 2],
    [‘product_id’ => 1, ‘color_id’ => 3],
    [‘product_id’ => 2, ‘color_id’ => 1],
    [‘product_id’ => 2, ‘color_id’ => 2],
    [‘product_id’ => 2, ‘color_id’ => 3],
    ];

    DB::table(‘color_product’)->insert($productAttribute);
    ColorSizeSeeder:
    $colorSizeAttribute = [
    [‘product_id’ => 1, ‘color_id’ => 1, ‘size_id’ => 1],
    [‘product_id’ => 1, ‘color_id’ => 1, ‘size_id’ => 2],
    [‘product_id’ => 1, ‘color_id’ => 1, ‘size_id’ => 3],
    [‘product_id’ => 2, ‘color_id’ => 1, ‘size_id’ => 1],
    [‘product_id’ => 2, ‘color_id’ => 1, ‘size_id’ => 2],
    [‘product_id’ => 2, ‘color_id’ => 1, ‘size_id’ => 3]
    ];

    DB::table(‘size_color’)->insert($colorSizeAttribute);

LEAVE A REPLY

Please enter your comment!
Please enter your name here