Pivot Tables and Extra Field Operations

Let's discuss a feature of Laravel that is useful but potentially difficult to understand at first. A pivot table is an example of an intermediate table with relationships between two other "main" tables.

Real-life Example of Pivot Tables

Let's say a company has a dozen Shops all over the city/country and a variety of products, and they want to store information about which Products are sold in which Shops. It's a perfect example of a many-to-many relationship: one product can belong to several shops, and one shop can have multiple products.

So here's a potential database structure:

The product_shop table is called a "pivot" table.

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

How do we save the data with the help of our two Models instead of the third intermediate one? A couple of things here. For example, if we want to add another product to the current shop instance, we use relationship function and then method attach():

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

A new row will be added to the product_shop table with $product_id and $shop_id values. Likewise, we can detach a relationship - let's say we want to remove a product from the shop:

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

Or, more brutally, remove all products from a particular shop - then call the method without parameters:

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

You can also attach and detach rows by passing an array of values as parameters:

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

Another beneficial function, in my experience, is updating the whole pivot table. For example, your admin area has checkboxes for shops for a particular product. During the Update operation, you must check all shops, delete those not in the new checkbox array, and then add/update the existing ones. Pain in the neck. Not anymore - there's a method called sync() which accepts new values as parameters array and then takes care of all that "dirty work" of syncing:

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

Result - no matter what values were previously in the product_shop table. After this call, there will be only three rows with shop_id equals 1, 2, or 3.

Additional Columns in Pivot Tables

As mentioned above, you would want more fields in that pivot table. In our example, saving the amount of products and price in that particular shop and timestamps makes sense. We can add the fields through migration files, as usual, but for proper usage in relationships, we have to make some additional changes to Models:

Schema::create('product_shop', function (Blueprint $table) {
    $table->foreignId('product_id')->constrained();
    $table->foreignId('shop_id')->constrained();
    $table->integer('products_amount'); 
    $table->integer('price');
    $table->timestamps();
});

use Illuminate\Database\Eloquent\Relations\BelongsToMany;
 
class Shop extends Model
{
    protected $fillable = [
        'name',
    ];
 
    public function products(): BelongsToMany
    {
        return $this->belongsToMany(Product::class)
            ->withPivot(['products_amount', 'price']) 
            ->withTimestamps();
    }
}

As you can see, we can add timestamps with a simple method withTimestamps(), and additional fields are added just as parameters in method withPivot(). Now, it allows us to get those values in our loops in the code. With a property called pivot:

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

Basically, ->pivot represents that intermediate pivot table, and with this, we can access any of our described fields, like created_at, for example. How do you add those values when calling attach()? The method accepts a second parameter as an array so that you can specify all additional fields there:

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

Filtering Queries via Pivot Table

When defining relationships in your Models, you can add a where() statement. You can do the same for pivot tables and query via pivot field.

For example, we want to get products for a shop sold only today. We can create a new relationship in the Shop Model and use the wherePivotBetween() method to do that. All available methods have a pivot in the method name.

class Shop extends Model
{
    protected $fillable = [
        'name',
    ];
 
    public function products(): BelongsToMany
    {
        return $this->belongsToMany(Product::class)
            ->withPivot(['products_amount', 'price'])
            ->withTimestamps();
    }
 
    public function productsSoldToday(): BelongsToMany 
    {
        return $this->belongsToMany(Product::class)
            ->wherePivotBetween('created_at', [now()->startOfDay(), now()->endOfDay()]);
    } 
}

When querying shops, we would eager load this relationship as with any relationship.

App\Models\Shop::with('productsSoldToday')->get();

These are all available for querying the pivot table: wherePivot, wherePivotIn, wherePivotNotIn, wherePivotBetween, wherePivotNotBetween, wherePivotNull, and wherePivotNotNull.

Updating Value in the Pivot Table

There might be cases when you need to update a value in the pivot table. For such cases, Laravel has an updateExistingPivot() method. This method returns the number of rows that have been updated.

For example, for the first shop, we want to update the products_amount value for some products. On the Shop Model, we call the products() relationship and then the updateExistingPivot() method.

In the update method, the first parameter is the IDs of the relationship records. The value can be a single ID or an array of multiple IDs. The second parameter is an array of attributes where we specify the column name and the new value.

In this example code, we update the products_amount field and set the value to 5 for the first shop where the product ID equals 2.

$shop = \App\Models\Shop::first();
 
$shop->products()->updateExistingPivot(2, [
    'products_amount' => 5,
]);

So, these are the simple and more complex operations you can perform on the pivot tables in Laravel.