How to query models with a where condition on the pivot table using a many-to-many relationship?

Question

I have two models, User and Role. These two models have many-to-many relationship between each other with pivot table role_user. In this pivot table are values user_id, role_id and meta_value. meta_value is important, because it is foreign key (in database just int) of other models. The other model is chosen by Roles mate_type.

User migration:

Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });

Role migration:

Schema::create('roles', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
            $table->string('name');
            $table->integer('meta_type')->nullable();
        });

Role_user migration:

Schema::create('role_user', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
            $table->foreignId('user_id')->constrained()->onDelete('cascade');
            $table->foreignId('role_id')->constrained()->onDelete('cascade');
            $table->integer('meta_value')->nullable();
        });

These meta values are used if the role is dependent on other models. In practise, if I have a Role named "Team member", the Role will have a meta type of Team (I am using numbers to determine that).

And now, I need to select somehow all users that have the role of team member with the specific team (in meta).

I tried:

$user = User::with('roles')->where(column: 'meta_type', "=", "2")->wherePivot("meta_value", "=", $team_id)->get();

$users = User::all()->with('roles')->where("pivot_meta_value", "=", $team_id)->get();

$users = User::whereHas('roles', function($q) {
        $q->whereIn('meta_value', 2);
        })->get();

And many more, but all without success.

Does anyone know how to do it?

Alex · Accepted Answer · 2025-06-05 12:50:17Z

I tried:

$user = User::with('roles')->where(column: 'meta_type', "=", "2")->wherePivot("meta_value", "=", $team_id)->get();

$users = User::all()->with('roles')->where("pivot_meta_value", "=", $team_id)->get();

$users = User::whereHas('roles', function($q) {
        $q->whereIn('meta_value', 2);
        })->get();

In the first example, you just load the roles relationship normally and are using the meta_type where() condition on the User query, but it is a Roles column. The wherePivot might actually work here, but I guess you must use it on the actual relationship inside a callback.

In the second example you try to query on pivot_meta_value, which doesnt exist.

In the third example you try to read the pivot tables column on the roles table.

Or basically: You are querying your conditions against your Users, not the Roles or the pivot table, i.e. the relationship.

Try this:

$users = User::with([
    'roles' => function($query) use($metaType, $teamId) {
        $query->where('meta_type', $metaType)->wherePivot('meta_value', $teamId);
    },
])->get();

You can also provide the pivot columns in the relationship function. By doing so, they are always available when querying models, but you can also just use it in conditions.

public function roles()
{
    return $this->belongsToMany(Role::class)
                ->withPivot('meta_value');
}

Further documentation: https://laravel.com/docs/12.x/eloquent-relationships#filtering-queries-via-intermediate-table-columns

Collectives™ on Stack Overflow

How to query models with a where condition on the pivot table using a many-to-many relationship?

User migration:

Role migration:

Role_user migration:

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

User migration:

Role migration:

Role_user migration:

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related