Laravel efficient way to remove X records if there are duplicates-CodePudding

I'm trying to write a query to be able to find duplicate records based on a column called hash and remove all but the 5 newest records, I figured I could use the limit method to limit the number of records but aren't sure the best way to do it without performing multiple queries.

For example, if I have 30 entries in my database table of applications where the hash column is ABC, then I want to remove 25 records leaving only 5 records left.

If there's 6 records for a particular hash then I'd only want to remove 1 record, I'm not sure how I would achieve this if there are fewer than 5 records though.

My current query to grab these records where I would loop over and delete is:

/**
 * Get applications by hash
 */
protected function getDuplicateApplications($hash = '')
{
    return Application::where('hash', $hash)
                      ->orderBy('created_at', 'asc')
                      ->limit(5)
                      ->get();
}

What else do I need?

CodePudding user response：

protected function deleteDuplicateApplications($hash = '', $skip = 5)
{
    // First get total count
    $totalCount = Application::where('hash', $hash)->count();

    // Then skip certain amount and take rest and delete it.
    return Application::where('hash', $hash)
                        ->orderBy('created_at', 'desc')
                        ->skip($skip)
                        ->take($totalCount - $skip)
                        ->delete();
}