Get the latest data on duplicate in Laravel Eloquent

user_9170680 0
Viewed 1601

so I'm trying to get duplicate data with the higher ID number so I can delete the latest duplicate data (not the older one).

I've tried using orderBy but it won't work

$duplicates = MasterPayrollInput::where('periode', $month)
        ->orderBy('id', 'asc')
        ->groupBy('nip')
        ->havingRaw('count(*) > 1')
        ->get();

    $duplicatesid = array_column($duplicates->toArray(), 'id');

    $todelete = array_map(function($item){ return $item[0]; }, $duplicatesid);

    MasterPayrollInput::whereIn('id', $duplicatesid )->delete();
eloquent laravel sql
3 Answers

Use whereIn closure to get the max id in group, and delete it by one query:

MasterPayrollInput::whereIn('id', function($query) {
    $query->from('master_payroll_inputs')
          ->groupBy('nip')
          ->havingRaw('count(*) > 1')
          ->selectRaw('MAX(id)');
})->delete();
user_28730520

You can use raw query for the same:

Assuming you want o delete record with duplicate names in master_payroll_input table:

  1. If you want to keep the row with the lowest id value:

    $result = DB::delete('DELETE n1 FROM master_payroll_input n1, master_payroll_input n2 WHERE n1.id > n2.id AND n1.name = n2.name');

  2. If you want to keep the row with the highest id value:

    $result = DB::delete('DELETE n1 FROM master_payroll_input n1, master_payroll_input n2 WHERE n1.id < n2.id AND n1.name = n2.name');

user_70996160

Use below query to delete duplicates

$duplicateIds = MasterPayrollInput::where('periode', $month)
    ->orderBy('id', 'asc')
    ->offset(1)
    ->limit(10)
    ->pluck('id');

MasterPayrollInput::whereIn('id', $duplicateIds)->delete();

offset - skip first row which you want to keep.

limit - Possible no. of duplicates.

user_90255860
Related