[FIXED] delete the duplicate records, and keep the one that have lower id

Issue

I have

a users table that contains bad data. I want to loop through and delete the duplicate records in term of email, and keep the record that have lower id.

I want to keep only id : 1, 14, 1004, 1005, 1003, 1006, and 1007.

---

I've tried

$users = DB::table('users')->where('id', DB::raw("(select min(`id`) from users)"))->get();

foreach ($users as $user) {
    if ( ???? )) { // <---- I'm not sure what to put here. 
        $user->delete();
    }
}

---

I noticed

$users = DB::table('users')->groupBy('email')->get();

return all the records I want to keep.

Solution

Try something like this

// get users with min id
$ids= DB::table('users')->where('id', DB::raw("(select min(`id`) from users)"))->lists('id');

// get all users
$users = \App\User::all();

foreach ($users as $user) {
    if (!in_array($user->id, $ids)) {
        $user->delete();
    }
}

First List all ids of the users with the minimum id ( with unique email ) , then get all users and loop through them. Delete every user who's id is not in it.

UPDATE

Since the upper query seems not to work here's what I would try ( not finally veryfied )

DB::table('users')->select('id', DB::raw('min("id") as lowest_id, email'))->groupBy('email')->lists('id')

Answered By - Frnak