Skip to main content

Black Friday 2025! Only until December 1st: coupon FRIDAY25 for 40% off Yearly/Lifetime membership!

Read more here

Advanced BelongsToMany with Extra Pivot Table Features

Premium
1:51

Let's talk about a belongs-to-many, also called many-to-many relationships, but on a deeper level. What are other options besides the foreign keys columns in the pivot table?

This is a typical pivot table between a project and a user. A project may belong to many users.

Schema::create('project_user', function (Blueprint $table) {
    $table->foreignId('project_id')->constrained();
    $table->foreignId('user_id')->constrained();
});

To show users for each project, you typically get the projects with user relations and do a foreach loop.

use Illuminate\Database\Eloquent\Relations\BelongsToMany;
 
class Project extends Model
{
    public function users(): BelongsToMany
    {
        return $this->belongsToMany(User::class);
    }
}
$projects = Project::with('users')->get();
 
foreach ($projects as $project) {
    print $project->id . ': ' . $project->title . ' (';
 
    foreach ($project->users as $user) {
        print $user->email .'; ';
    }
 
    print ')<hr />';
}

This is a typical default simple belongs-to-many relation.

Adding Timestamps to Pivot

What can we add to the pivot? For example, you want to know when that record was added. By default, pivot tables are without timestamps. To enable timestamps, we must first add columns to the migration.

Schema::create('project_user', function (Blueprint $table) {
    $table->foreignId('project_id')->constrained();
    $table->foreignId('user_id')->constrained();
    $table->timestamps(); 
});

But that wouldn't be enough. Timestamps, by default, wouldn't be set in a pivot table. To enable setting timestamps in the relationship belongs-to-many, you need to...

The Full Lesson is Only for Premium Members

Want to access all of our courses? (29 h 14 min)

You also get:

54 courses
Premium tutorials
Access to repositories
Private Discord
Get Premium for $129/year or $29/month

Already a member? Login here

Comments & Discussion

MM
Marek Miklúšek ✓ Link copied!

Hello, i think in first function activeUsers() missing ->withPivot('is_active')

MS
Mustafa Selman Yıldırım ✓ Link copied!

In case of a single-value is_active column, a separate relation seems the better option. What if we have a multiple-value column on the pivot table and we want to filter with whereIn()? It doesn't seem reasonable to create different relations for every possible value. You've mentioned that whereHas() can be used. How do we do that? Is there any other eloquent ways?

M
Modestas ✓ Link copied!

It is not only reasonable to create columns for each potential value, but also beneficial.

When searching inside a JSON or some other multi-value column - you are creating performance issues. And using single value columns - you are avoiding them.

Now about the whereHas() there is wherePivotIn() and other wherePivot() methods available

MS
Mustafa Selman Yıldırım ✓ Link copied!

I apologize for any confusion caused by my earlier question.

By single-value column, I mean a column with a boolean value, such as is_active (as in the example above). By multi-value column, I mean a column that can take one value among several possible options, such as country_code. For instance, this column may have a value like us, uk, ca, or de, but not multiple codes simultaneously. Since the list of possible values can be extensive, it doesn't seem practical to create separate relationships for each of them.

However, this is just background information.

My main question is: I don't want to create separate relationships like activeUsers. Instead, I want to query dynamically with arbitrary values. I've read above that this should be possible using whereHas, but I couldn't find an example. When I tried, I wasn't able to make it work.

Here’s the scenario:

I have two models: User and Ability. There’s a pivot table between them that includes a column in_which_country. I want to query users based on both the name column of the abilities table and the in_which_country column of the pivot table (ability_user).

I assumed the following syntax should work, but it fails:

$users = User::whereHas('abilities', function ($query) {
    $query->where('name', 'send_feed');
})->whereHas('abilities', function ($query) {
    $query->wherePivotIn('in_which_country', ['us', 'ca']);
})->get();
M
Modestas ✓ Link copied!

Okay, this makes it more clear :)

So to filter this, in theory you could do:

$users = User::whereHas('abilities', function ($query) {
    $query->where('name', 'send_feed');
})->whereHas('abilities', function ($query) {
    $query->where('user_abilities.in_which_country', ['us', 'ca']);
})->get();

This should trigger the pivot table. Or, you could install debugbar and see the query it generates. From there it would be easier to work out what needs to be done ;)

M
Modestas ✓ Link copied!

ps. It's all about table/column naming. Laravel relationship via pivot table loads that pivot table too. So it can be queried by prefixing the column with table name

MS
Mustafa Selman Yıldırım ✓ Link copied!

Thank you very much! I think I got a bit too fixated on the wherePivotIn method. I guess we should use whereIn instead of where for handling multiple values (['us', 'ca']). Additionally, specifying the table name doesn’t seem necessary unless there’s ambiguity, such as when both tables have columns with the same name. For future reference, I can confirm that the following works:

$users = User::whereHas('abilities', function ($query) {
    $query->where('name', 'send_feed');
})->whereHas('abilities', function ($query) {
    $query->whereIn('in_which_country', ['us', 'ca']);
})->get();
PR
Paul Rijke ✓ Link copied!

Hi Povilas, in the documentation is stated:

Pivot models may not use the SoftDeletes trait. If you need to soft delete pivot records consider converting your pivot model to an actual Eloquent model.

What does this have for implications on using the relationships? Why shouldn't we always use a regular Model instead of Pivot