Laravel - Remove duplicates from a pivot table

Question

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

For duplicates where some have approved_at and others are null, I want to preserve the last one that has been approved and remove all the others (taking the above table as example, row with ID 2 needs to remain)
For all other duplicates, only keep one instance

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

user_4369919 · Answer

here is a solution without the need for unsafe group by

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

        $dupplicates = \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($dupplicates as $dupplicate) {
            var_dump('Deleting one group');
            $safeId = \App\Models\Model::query()
            ->orderByRaw('ISNULL(approved_at) ASC');
            ->where('table_a_id', $dupplicate->table_a_id)
            ->where('table_b_id', $dupplicate->table_b_id)
            ->where('table_c_id', $dupplicate->table_c_id)
            ->value('id');
            $deleteQuery = \App\Models\Model::query()
                ->where('table_a_id', $dupplicate->table_a_id)
                ->where('table_b_id', $dupplicate->table_b_id)
                ->where('table_c_id', $dupplicate->table_c_id)
                ->where('id','!=',$safeId)
                ->delete();
        }
        var_dump('Delete complete');
    }

Laravel - Remove duplicates from a pivot table

1 Answers