When using relationships with belongsToMany in Laravel, we use a pivot table (also referred to as an intermediate table).

What’s exciting is that this pivot table can be customized with additional fields, offering flexibility and control in your application.

In this tutorial, we’ll showcase an example where orders are associated with products, and their corresponding “quantity” is stored as an extra field in the pivot. We’ll explore how Laravel Eloquent provides a convenient syntax to access these extra pivot field values:

A pivot table used in a belongsToMany can contain extra field(s). These can be made accessible to the Eloquent Model’s relation by adding ->withPivot($name) on a belongsToMany. When this is done, we can then access the extra field’s value as a property by using ->pivot->fieldName on the Eloquent Model.

Diagram of the Tables `orders`, `products` and the pivot `order_product` With Extra Field: “quantity”

Let’s get started!

Step 1: Create and Run the Migrations

To begin, let’s create the necessary migrations for the “products” and “orders” tables, as well as the pivot table “order_product”. Open your terminal and run the following Artisan commands:

php artisan make:migration create_products_table --create=products
php artisan make:migration create_orders_table --create=orders
php artisan make:migration create_order_product_table --create=order_product

This will generate three migration files: one for the “products” table, one for the “orders” table, and one for the pivot table “order_product”. Open each of the generated migration files and modify their up() and down() methods as shown below:

For the products table, use:

database/migrations/2023_07_18_201740_create_products_table.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->text('description')->nullable();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('products');
    }
};

For the orders table, use:

database/migrations/2023_07_18_201741_create_orders_table.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('orders', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('user_id')->nullable(); // If orders are user-specific
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('orders');
    }
};

Now, let’s create the pivot table order_product that links products and orders and also includes the extra field “quantity” to track the quantity of each product in the cart.

For the order_product table, use the following code:

database/migrations/2023_07_18_201743_create_order_product_table.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('order_product', function (Blueprint $table) {
            $table->unsignedBigInteger('order_id');
            $table->unsignedBigInteger('product_id');
            $table->unsignedInteger('quantity')->default(1); // The extra field for quantity

            // Add foreign keys
            $table->foreign('order_id')->references('id')->on('orders')->onDelete('cascade');
            $table->foreign('product_id')->references('id')->on('products')->onDelete('cascade');
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('order_product');
    }
};

Run the migration to create the “products”, and “carts” tables as well as the “cart_product” pivot table:

php artisan migrate

Output When Running `php migrate` to Create `products`, `orders` and `order_products` tables

Step 2: Creating the Eloquent Models

Next, let’s create the Eloquent models for the “Product” and “Cart” entities. Run the following commands in your terminal:

php artisan make:model Product
php artisan make:model Order

This will generate two model files in the “app/Models” directory.

Note: In this case, we do not need to create a model for the intermediate pivot table “order_product” since it does not require additional functionality beyond the relationship.

Open the generated model files and add the necessary relationships as shown below.

For the Product Model, use:

app/Models/Product.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    protected $fillable = [
        'name',
        'description',
    ];

    public function orders() {
        return $this->belongsToMany(Order::class)->withPivot('quantity');
    }
}

For the Order Model, use:

app/Models/Order.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Order extends Model
{
    public function products() {
        return $this->belongsToMany(Product::class)->withPivot('quantity');
    }
}

Step 3: Working with Pivot Data

Now that the relationships and the pivot table are set up, you can easily work with pivot data. For example, to add a product to an order with a specific quantity, use the attach method:

routes/web.php

<?php

use Illuminate\Support\Facades\Route;

Route::get('/pivot-extra-fields', function () {

    $product = \App\Models\Product::create(['name' => 'Weight Gain 4000']);
    $order = \App\Models\Order::create();

    // Adding the product to the cart with the specified quantity
    $order->products()->attach($product->id, ['quantity' => 3]);

    // find product in the order
    $orderProduct = $order->products->find($product->id);

    // find product in the order and access extra field 'quantity' from pivot
    $quantity = $order->products->find($product->id)->pivot->quantity;

    // print results
    dump($orderProduct->attributesToArray());
    dump($quantity);
});

When opening in the browser this prints the product added to the order and its quantity correctly:

Screenshot of Browser Showing Variable Dump of Product and Pivot Extra Field: Quantity

We can also see the result when we check the database contents:

Showing Result Data in `products`, `orders` and `order_product` Tables Using MySQL Client

Further Examples of Pivot Tables with Extra Fields

Entity 1	Entity 2	Pivot Table	Extra Field(s)
Product	Category	category_product	position_in_category
Team	Member	team_member	role
Student	Course	course_student	enrollment_date, grade
Employee	Project	employee_project	role, hours_worked
Author	Book	author_book	publication_year, genre
Product	Cart	cart_product	quantity

Table Showing Example Relationships With Extra Fields in the Pivot Table

Conclusion

Using a pivot table with an extra field in Laravel is a powerful way to add data to relationships effectively. In this tutorial we have seen how to set up the tables accordingly, define the relationships and work with pivot data, like the quantity of a product order.

I’m sure these technique can prove helpful to you. Try to apply the pivot table concept in your own project and let me know how it went in the comments. Happy coding!

References

This entry is part 4 of 9 in the series Laravel Eloquent Relationships