WherePivot and WherePivotIn – additional filters for many-to-many relationships

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.