Pivot tables and many-to-many relationships

Today I want to talk about a feature of Laravel which is really useful but can be potentially difficult to understand at first. Pivot table is an example of intermediate table with relationships between two other "main" tables.

Real-life example of pivot tables

So here's a potential database structure:

• - id
• - name

• - id
• - name

• - product_id
• - shop_id

• Name of the pivot table should consist of singular names of both tables, separated by undescore symbol and these names should be arranged in alphabetical order, so we have to have product_shop, not shop_product.
• To create a pivot table we can create a simple migration with artisan make:migration or use Jeffrey Way's package Laravel 5 Generators Extended where we have a command artisan make:migration:pivot.
• Pivot table fields: by default, there should be only two fields - foreign key to each of the tables, in our case product_id and shop_id. You can add more fields if you want, then you need to add them to relationship assignment - we will discuss that later.

Models for Many-to-Many Relationships: BelongsToMany

class Shop extends Model
{
    /**
     * The products that belong to the shop.
     */
    public function products()
    {
        return $this->belongsToMany(Product::class);
    }
}

class Product extends Model
{
    /**
     * The shops that belong to the product.
     */
    public function shops()
    {
        return $this->belongsToMany(Shop::class);
    }
}

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

public function products()
{
    return $this->belongsToMany(Product::class, 'products_shops',
      'shops_id', 'products_id');
}

Managing Many-to-Many Relationships: attach-detach-sync

$shop = Shop::find($shop_id);
$shop->products()->attach($product_id);

$shop->products()->detach($product_id);

$shop->products()->detach();

$shop->products()->attach([123, 456, 789]);
$shop->products()->detach([321, 654, 987]);

$product->shops()->sync([1, 2, 3]);

Additional Columns in Pivot Tables

public function products()
{
    return $this->belongsToMany(Product::class)
        ->withPivot('products_amount', 'price')
        ->withTimestamps();
}

foreach ($shop->products as $product)
{
    echo $product->pivot->price;
}

$shop->products()->attach(1, ['products_amount' => 100, 'price' => 49.99]);

Conclusion

Want to learn more?