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:
And we want to show product list in the table – here’s how it looks (code and table generated by our QuickAdmin):
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:
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.
You always shared something useful, thank you!
You helped understand this in a nice way.Thanks
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
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);