CODEWITHSUNDEEP
phpsqllaraveleloquent
Steve Ruru
Steve Ruru

Reputation: 153

Get the latest data on duplicate in Laravel Eloquent

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

Upvotes: 3

Views: 2281

Answers (3)

Masood Khan
Masood Khan

Reputation: 635

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.

Upvotes: -1

TsaiKoga
TsaiKoga

Reputation: 13394

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

Upvotes: 3

Sehdev
Sehdev

Reputation: 5662

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

Upvotes: 0

Related Questions