Laravel query for deleting duplicated users which don't have any roles

Question

I have users with duplicate email addresses in users table but not all of them have assigned roles in the role_user table. My task is to delete those records from the users that do not have any assigned roles AND it is a duplicate email address.

There are two tables that are involved in this query (there is also a roles table but that isn't relevant):

• users (id, name, email, created_at, deleted_at)
• role_user (user_id, role_id, email, created_at, updated_at)

Example:

user record 1

• id - 1
• name - John Smith
• email - [email protected]
• created_at 2021-12-15 14:37:00
• updated_at 2021-12-15 14:37:00
• deleted_at NULL

role_user record 1

• user_id - 1
• role_id - 1
• created_at 2021-12-15 14:37:00
• updated_at 2021-12-15 14:37:00

user record 2

• id - 2
• name - John Smith
• email - [email protected]
• created_at 2022-02-08 17:30:00
• updated_at 2021-12-15 14:37:00
• deleted_at NULL

The user record above doesn't have any assigned roles (so no record for them in role_user)

I would need a query to delete the above user (id = 2) and any others like this.

I have already made a start on getting the duplicate users:

$duplicates = DB::table('users')
    ->select('name', DB::raw('LOWER(email)'))
    ->groupBy('name', DB::raw('LOWER(email)'))
    ->havingRaw('COUNT(*) > 1')
    ->get();

DB::table('users')
    ->whereIn('email', $duplicates->pluck('email'))
    ->whereNotExists(function ($query) {
        $query->select(DB::raw(1))
            ->from('users as u')
            ->where(function ($query) {
                   $query->where('email', DB::raw('users.email'))
                       ->where('id', '<', DB::raw('users.id'));
                   })
            ->whereNull('deleted_at')
            ->limit(1);
    })->update(['deleted_at' => now()]);

The query can be written using Eloquent or the query builder.

I need help with deleting the relevant users please.

Answer 1

You can perform a single query to delete duplicate records that don't have role like this i think:

User::whereNotIn('id', function($query) {
    $query->select('user_id')
      ->from('role_user');
  })->whereIn('email', function($query){
    $query->select('email')
      ->from('users')
      ->groupBy(DB::raw('LOWER(email)'))
      ->havingRaw('count(*) > 0')
  })->delete();