How to Remove Duplicate Rows in Laravel

Question

I need to delete duplicated rows for specified Mobile Number on a mysql table. How can I do this with an Laravel query?

Answer 1

You could also do something like this, if you want to find duplicate values in the column 'name':

Example:

$duplicateRecords = DB::select('name')
              ->selectRaw('count(`name`) as `occurences`')
              ->from('users')
              ->groupBy('name')
              ->having('occurences', '>', 1)
              ->get();

Then you need to loop through your collection and delete the items.

foreach($duplicateRecords as $record) {
    $record->delete();
}

Update Laravel 8

Example records in user table:

id	name
1	Camilla O'Conner
2	Camilla O'Conner
3	Camilla O'Conner
4	Mr. Gussie Dickens IV
5	Patience Jacobs
6	Patience Jacobs

To find duplicate records for the field name you can use this:

$duplicated = DB::table('users')
                    ->select('name', DB::raw('count(`name`) as occurences'))
                    ->groupBy('name')
                    ->having('occurences', '>', 1)
                    ->get();

This will give you the value that is duplicated and the amount of duplicates:

Illuminate\Support\Collection {#274 ▼
  #items: array:2 [▼
    0 => {#277 ▼
      +"name": "Camilla O'Conner"
      +"occurences": 3
    }
    1 => {#278 ▼
      +"name": "Patience Jacobs"
      +"occurences": 2
    }
  ]
}

Now you can cycle through this and delete the records:

foreach ($duplicated as $duplicate) {
    User::where('name', $duplicate->name)->delete();
}

Answer 2

With Eloquent:

App\Model::where('mobile_number', '0123456789')->delete();

With the Query Builder:

DB::table('some_table')->where('mobile_number', '0123456789')->delete();

EDIT

The above will delete all rows with mobile_number 0123456789. If you want to keep one, use this:

// Get the row you don't want to delete.
$dontDeleteThisRow = App\Model::where('mobile_number', '0123456789')->first();

// Delete all rows except the one we fetched above.
App\Model::where('mobile_number', '0123456789')->where('id', '!=', $dontDeleteThisRow->id)->delete();

Answer 3

To delete duplicates but keeping the first or last one, do like this:

// Get all duplicated values. Replace 'table' and 'name' accordingly
$duplicates = DB::table('table') // replace table by the table name where you want to search for duplicated values
              ->select('id', 'name') // name is the column name with duplicated values
              ->whereIn('name', function ($q){
                $q->select('name')
                ->from('table')
                ->groupBy('name')
                ->havingRaw('COUNT(*) > 1');
              })
              ->orderBy('name')
              ->orderBy('id') // keep smaller id (older), to keep biggest id (younger) replace with this ->orderBy('id', 'desc')
              ->get();
        
$value = "";

// loop throuht results and keep first duplicated value
foreach ($duplicates as $duplicate) {
  if($duplicate->name === $value)
  {
    DB::table('table')->where('id', $duplicate->id)->delete(); // comment out this line the first time to check what will be deleted and keeped
    echo "$duplicate->name with id $duplicate->id deleted! \n";
  }
  else
    echo "$duplicate->name with id $duplicate->id keeped \n";
  $value = $duplicate->name;
}