CODEWITHSUNDEEP
phplaravellaravel-5
Karthik mathesh
Karthik mathesh

Reputation: 223

How to Remove Duplicate Rows in Laravel

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

Upvotes: 9

Views: 43325

Answers (11)

Rohallah Hatami
Rohallah Hatami

Reputation: 559

DB::delete('DELETE t1 FROM table_name t1, table_name t2 WHERE t1.id > t2.id AND t1.mobile_number = t2.mobile_number');

change table_name with your table name and mobile_number is column for mobile number in table I think items is clear :)

Upvotes: 1

Dima
Dima

Reputation: 1

It's my variant:

    $translations = Translation::query()->orderByDesc('id')->get();
    $deletedRows = 0;
    
    foreach ($translations as $record) {
        $count = $record->where('key', $record->key)->count();
        if($count > 1) {
            while ($count > 1) {
                $record->delete();
                $count--;
            }
            $deletedRows++;
        }
    }

    echo "Duplicate rows:$deletedRows has been deleted\n";
    return 0;

Upvotes: 0

Haseeb Butt
Haseeb Butt

Reputation: 1

public function RemoveDuplicate(){
  
    $car_chars = \Illuminate\Support\Facades\DB::table('character_diemensions')->get();

    foreach ($car_chars as $char){
        $duplicates = \App\Models\CharacterDiemension::where(['char_name'=>$char->char_name,'char_type'=>$char->char_type,'board_size'=>$char->board_size,'font_type'=>$char->font_type])->count();
        if($duplicates > 1) {
            $duplicates_records = \App\Models\CharacterDiemension::where(['char_name'=>$char->char_name,'char_type'=>$char->char_type,'board_size'=>$char->board_size,'font_type'=>$char->font_type])->get();
            foreach ($duplicates_records as $key3 => $duplicates_record){
                if($key3 != 0){
                    $duplicates_record->delete();
                }
            }
        }
    }
    dd('remove duplicate successfully');
};

Upvotes: -1

BitValentine
BitValentine

Reputation: 78

$getDuplications = testOrderResults::groupBy('test_id', 'column', 'result')->get();

$idsToKeep = array_column($getDuplications->toArray(), 'id');

testOrderResults::whereNotIn('id', $idsToKeep)->delete();

$getDuplications generates the array into groups of duplicates.

$idsToKeep lists the IDs to keep and not delete (the OGs)

The DB query deletes all entries apart from the IDs to keep

Upvotes: 1

vesperknight
vesperknight

Reputation: 770

Works with Laravel 8, in my case my duplicates are identified by two columns, player_id and played_at

For each duplicate, it keeps one record ($keeper) and erases the other duplicates

ray(sprintf('Game Analyses before: %d', GameAnalysis::count()));

$duplicated = \DB::table('game_analyses')
    ->select(['player_id', 'played_at', \DB::raw('count(*) as occurences')])
    ->groupBy(['player_id', 'played_at'])
    ->having('occurences', '>', 1)
    ->get();

ray(sprintf('Found %d duplicate records', GameAnalysis::whereIn('played_at', $duplicated->pluck('played_at'))->count() - $duplicated->count()));

foreach ($duplicated as $duplicateRecord) {
    $keeper = GameAnalysis::where('player_id', $duplicateRecord->player_id)->where('played_at', $duplicateRecord->played_at)->first();
    GameAnalysis::where('id', '<>', $keeper->id)
        ->where('player_id', $duplicateRecord->player_id)
        ->where('played_at', $duplicateRecord->played_at)
        ->delete();
}

ray(sprintf('Game Analyses after: %d', GameAnalysis::count()));

Upvotes: 0

Jean-Roch B.
Jean-Roch B.

Reputation: 554

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;
}

Upvotes: 3

codedge
codedge

Reputation: 5164

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();
}

Upvotes: 14

Sajeer Bin Saleem
Sajeer Bin Saleem

Reputation: 11

Here I am deleting duplicate Users which having same mobile number

$users =  DB::table('users')->get();
    $users  =  $users->groupBy('mobile_no');       
    foreach ($roles as $key => $value){
      if(count($value) >1){
          $id = $value[0]->id;
          $t = $value->where('id','!=',$id)->pluck('id');
          User::whereIn('id',$t)->delete();
      }
    }

Upvotes: -1

Brad Ahrens
Brad Ahrens

Reputation: 5168

You could also try to map out all of the phone numbers within the entries and if the number comes up again, you delete it.

For example:

$allContacts = Contact::all()->map->only(['mobile', 'id']);

$uniqueContacts = [];

foreach ($allContacts as $contact) {
    if (in_array($contact['mobile'], $uniqueContacts)) {
        Contact::where('id', $contact['id'])->delete();
    } else {
        array_push($uniqueContacts, $contact['mobile']);
    }
}

Upvotes: 1

zarpio
zarpio

Reputation: 7338

If you would like to leave every single entry and delete other duplicates.

The easiest way I found.

$same_data = DB::table('table_name')->where('mobile_number', '0000000000');

if ($same_data->count() > 1) {
    $same_data_before = clone $same_data;
    $top = $same_data->first();
    $same_data_before->where('id', '!=', $top->id)->delete();
}

Upvotes: 1

noodles_ftw
noodles_ftw

Reputation: 1313

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();

Upvotes: 6

Related Questions