Laravel Query Builder问题：数据透视表操作与无角色用户查询

Got it, let's break down these two Query Builder tasks for you—since you're in the migration phase and can't rely on models, we'll stick strictly to raw Query Builder calls.

First, let's assume your pivot table follows Laravel's standard naming: user_role (singular table names in alphabetical order) with at minimum user_id and role_id columns. We'll cover the common operations:

Inserting records into the pivot table #

To assign a single role to a user:

DB::table('user_role')->insert([
    'user_id' => 1,
    'role_id' => 3,
    // Add any extra pivot columns here, e.g., 'created_at' => now()
]);

For bulk assignments (e.g., multiple roles to one user, or one role to multiple users):

DB::table('user_role')->insert([
    ['user_id' => 1, 'role_id' => 2],
    ['user_id' => 1, 'role_id' => 3],
    ['user_id' => 2, 'role_id' => 1],
]);

Deleting records from the pivot table #

Remove a specific role from a user:

DB::table('user_role')
    ->where('user_id', 1)
    ->where('role_id', 3)
    ->delete();

Remove all roles from a user:

DB::table('user_role')
    ->where('user_id', 1)
    ->delete();

Updating pivot table records (for tables with extra columns) #

If your pivot has additional fields like is_primary, update them like this:

DB::table('user_role')
    ->where('user_id', 1)
    ->where('role_id', 2)
    ->update(['is_primary' => true]);

We've got two solid approaches here, both using pure Query Builder without model dependencies:

Method 1: LEFT JOIN with Null Check #

This joins the users table to the user_role pivot, then filters for users where no matching pivot record exists:

$usersWithoutRoles = DB::table('users')
    ->leftJoin('user_role', 'users.id', '=', 'user_role.user_id')
    ->whereNull('user_role.role_id')
    ->select('users.*') // Only fetch user data, not pivot columns
    ->get();

Method 2: WHERE NOT EXISTS Subquery #

This checks that there are no entries in the pivot table linked to the user—often more efficient for large datasets:

$usersWithoutRoles = DB::table('users')
    ->whereNotExists(function ($query) {
        $query->select(DB::raw(1))
            ->from('user_role')
            ->whereColumn('user_role.user_id', 'users.id');
    })
    ->get();

Either method will return all users with no assigned roles—pick whichever fits your database's performance profile better.

内容的提问来源于stack exchange，提问作者Epsilon47