CODEWITHSUNDEEP
mysqllaravel
Wyxos
Wyxos

Reputation: 595

Laravel - Remove duplicates from a pivot table

I am working on an existing project where the pivot table does not have a unique key. I would like to add one now but the table already contains duplicate records.

My table consists of the following

id table_a_id table_b_id table_c_id approved_at
1 1 1 1 2021-08-11
2 1 1 1 2021-08-11
3 1 1 1 NULL

What I need to achieve is

My attempt would be able to deal with the second part but not the first one.


    public function deleteDuplicates()
    {
        var_dump('Deleting...');

        $query = \App\Models\Model::query()
            ->select('id', 'table_a_id', 'table_b_id', 'table_c_id', 'approved_at', DB::raw('COUNT(*) as `count`'))
            ->groupBy([
                'table_a_id',
                'table_b_id',
                'table_c_id',
            ])
            ->havingRaw('COUNT(*) > 1');

        $duplicates = $query->pluck('id');

        \App\Models\Model::query()->whereIn('id', $duplicates)->delete();

        if ($query->pluck('id')->count() > 0) {
            $this->deleteDuplicates();
        }

        var_dump('Deleting more...');
    }

Upvotes: 2

Views: 825

Answers (1)

N69S
N69S

Reputation: 17216

Here is a solution without the need for unsafe group by

public function deleteDuplicates()
{
    var_dump('Deleting...');

    $duplicates = \App\Models\Model::query()
        ->select('table_a_id', 'table_b_id', 'table_c_id', DB::raw('COUNT(*) as `count`'))
        ->groupBy([
            'table_a_id',
            'table_b_id',
            'table_c_id',
        ])
        ->havingRaw('COUNT(*) > 1')
        ->get();

    foreach($duplicates as $duplicate) {
        var_dump('Deleting one group');
        $safeId = \App\Models\Model::query()
        ->orderByRaw('ISNULL(approved_at) ASC');
        ->where('table_a_id', $duplicate->table_a_id)
        ->where('table_b_id', $duplicate->table_b_id)
        ->where('table_c_id', $duplicate->table_c_id)
        ->value('id');
        $deleteQuery = \App\Models\Model::query()
            ->where('table_a_id', $duplicate->table_a_id)
            ->where('table_b_id', $duplicate->table_b_id)
            ->where('table_c_id', $duplicate->table_c_id)
            ->where('id','!=',$safeId)
            ->delete();
    }
    var_dump('Delete complete');
}

Upvotes: 1

Related Questions