Hey, my book is finally available. Find it here.  Eloquent Beyond The Basics!

Using pivot tables in Laravel: The complete guide to custom intermediary tables in many-to-many relationships

Oct 22, 2022

When working with many-to-many relationships the table structure is a little more complex than other relationships.

When using many-to-many relationships we need to create an intermediary table.

Let's say for example that we have an airline app where we have customers and flights, each customer can have multiple fights, and a flight can have multiple customers (or passengers).

Your migrations would look something like this (simplified to focus on the pivot table):

// clients migration
return new class extends Migration
{
    public function up()
    {
        Schema::create('clients', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->timestamps();
        });
    }

    //...
}
// flights migration
return new class extends Migration
{
    public function up()
    {
        Schema::create('flights', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('airline');
            $table->timestamps();
        });
    }

    //...
}
// pivot table migration
return new class extends Migration
{
    public function up()
    {
        Schema::create('client_flight', function (Blueprint $table) {
            $table->foreignId('client_id');
            $table->foreignId('flight_id');
            $table->primary(['client_id', 'flight_id']);
        });
    }

    //...
}

In this article, I'm going to show you how to use that pivot table going from the basics to the most custom behaviors.

The basic usage

By using the table structure I mentioned above we can leverage Laravel's defaults. So our models will look very simple.

class Flight extends Model
{
    public function clients()
    {
        return $this->belongsToMany(Client::class);
    }
}
class Client extends Model
{
    public function flights()
    {
        return $this->belongsToMany(Flight::class);
    }
}

Laravel guesses the name of the pivot table by taking the singular of the model names and joining the two in alphabetical order, in this case, client is before flight so the table name it's client_flight.

Then it assumes that column names will be the same singular model name with the _id postfix. In this case client_id and flight_id

However, both the table name and the column names can be changed to whatever you want by passing some parameters to the belongsTo function.

Using custom table and column names

Let's say for example that for some reason you don't want to call your pivot table client_flight, you want to call it tickets and the columns won't be client_id and flight_id. Instead, you want to drop the _id and just go with client and flight.

So, the table migration for your pivot table would look something like this:

// pivot table migration
return new class extends Migration
{
    public function up()
    {
        Schema::create('tickets', function (Blueprint $table) {
            $table->foreignId('client');
            $table->foreignId('flight');
            $table->primary(['client', 'flight']);
        });
    }

    //...
}

To make this structure work we need to make some changes to our models, we need to pass the table name and the new column names. Let's look at the Flight model first.

class Flight extends Model
{
    public function clients()
    {
        return $this->belongsToMany(Client::class, 'tickets', 'flight', 'client');
    }
}

We added 3 new parameters, tickets, flight and client. The first one represents the name of the pivot table, and the second on it's the name of the column that is related to our model (the one that will store the flight id).

And the last is the column related to the other model (the one that will store the client id).

We do the same in our Client model.

class Client extends Model
{
    public function flights()
    {
        return $this->belongsToMany(Flight::class, 'tickets', 'client', 'flight');
    }
}

Notice that now the client and flight are inverted from the Flight model.

And done, we now have a costume table name with custom column names.

But what if you want to store when the pivot table is created and updated by using Laravel timestamps?

Adding timestamps to the pivot table

To add timestamps to the pivot table, we first need to add the timestamps columns in our migration. You can create a new migration if the table is already in use, but I'm going to add it to the one that creates the table.

// pivot table migration
return new class extends Migration
{
    public function up()
    {
        Schema::create('tickets', function (Blueprint $table) {
            $table->foreignId('client');
            $table->foreignId('flight');
            $table->primary(['client', 'flight']);
            $table->timestamps();
        });
    }

    //...
}

We call the method $table->timestamps() the same way we would do on any other migration.

Now we need to tell Laravel to update the created_at and updated_at columns automatically. To do that we add the ->withTimestamps()

class Flight extends Model
{
    public function clients()
    {
        return $this->belongsToMany(Client::class, 'tickets', 'flight', 'client')
            ->withTimestamps();
    }
}
class Client extends Model
{
    public function flights()
    {
        return $this->belongsToMany(Flight::class, 'tickets', 'client', 'flight')
            ->withTimestamps();
    }
}

And done, we now have timestamps in our pivot table.

Adding additional columns to the pivot table

By default, you can access your pivot table by using the keyword pivot after accessing a relationship. For example:

$client = Client::find(1);

foreach ($client->flights as $flight) {
    echo "The ticket was purchased at:" . $flight->pivot->created_at;
}

But what if you want to add more columns to your pivot table? For example, maybe I would like to store the seat that the user purchased in the pivot table. First, let's add a column to the tickets table.

// pivot table migration
return new class extends Migration
{
    public function up()
    {
        Schema::create('tickets', function (Blueprint $table) {
            $table->foreignId('client');
            $table->foreignId('flight');
            $table->primary(['client', 'flight']);
            $table->timestamps();
            $table->string('seat');
        });
    }

    //...
}

After adding the column to the table, we need to tell Laravel that we would like to fetch that column when we access the relationship. To do that we'll use the withPivot method.

class Flight extends Model
{
    public function clients()
    {
        return $this->belongsToMany(Client::class, 'tickets', 'flight', 'client')
            ->withTimestamps()
            ->withPivot('seat');
    }
}
class Client extends Model
{
    public function flights()
    {
        return $this->belongsToMany(Flight::class, 'tickets', 'client', 'flight')
            ->withTimestamps()
            ->withPivot('seat');
    }
}

We pass the column name to the withPivot method, if you want to use multiple columns you can pass multiple arguments to the method like this ->withPivot('column1', 'column2', 'etc..').

Now to store the value when we attach a client to a flight or vice versa we can pass an array of values corresponding to the column names.

$client->flights()->attach($flightId, ['seat' => $seat]);

And we can access it through the pivot keyword:

$client = Client::find(1);

foreach ($client->flights as $flight) {
    echo "The client's seat is:" . $flight->pivot->seat;
}

But it's a little weird that our table is called tickets but we need to use this pivot word to access the values. Would it be nice if we could call $flight->ticket->seat instead?

Name your pivot table

Well, you can, and it's very simple. Just add a call to ->as('ticket') in the relationship definition.

class Flight extends Model
{
    public function clients()
    {
        return $this->belongsToMany(Client::class, 'tickets', 'flight', 'client')
            ->as('ticket')
            ->withTimestamps()
            ->withPivot('seat');
    }
}
class Client extends Model
{
    public function flights()
    {
        return $this->belongsToMany(Flight::class, 'tickets', 'client', 'flight')
            ->as('ticket')
            ->withTimestamps()
            ->withPivot('seat');
    }
}

Now instead of pivot we can use ticket:

$client = Client::find(1);

foreach ($client->flights as $flight) {
    echo "The client's seat is:" . $flight->ticket->seat;
}

Extract the pivot table to a model

One other feature that we can use in Laravel is creating a model for the pivot table.

Doing this allows us to add behaviors to the pivot table. For example, let's say I want to add a way to upgrade a seat to first class.

First I'm going to create a model called Ticket, but this model will extend Pivot instead of Model like we would in other models.

And here, I'm going to add a method to upgrade to first class.

use Illuminate\Database\Eloquent\Relations\Pivot;
 
class Ticket extends Pivot
{
    public function upgradeToFirstClass()
    {
        // Example code that doesn't work :P
        $this->seat = $someFirstClassSeat;
    }
}

Now in our main models, we need to call the ->using() method when defining the relationship.

class Flight extends Model
{
    public function clients()
    {
        return $this->belongsToMany(Client::class, 'tickets', 'flight', 'client')
            ->as('ticket')
            ->withTimestamps()
            ->withPivot('seat')
            ->using(Ticket::class);
    }
}
class Client extends Model
{
    public function flights()
    {
        return $this->belongsToMany(Flight::class, 'tickets', 'client', 'flight')
            ->as('ticket')
            ->withTimestamps()
            ->withPivot('seat')
            ->using(Ticket::class);
    }
}

And now we can call the methods in our pivot table, for example:

$flight = $client->flights()->find($flightId);
$flight->ticket->updateToFirstClass();

And that's it. We went from the most basic example of a many-to-many relationship to creating a very custom setup. Hope you learned something :).