Returning full duplicate rows using Laravel's query builder

Question

I am looking to return the full information on duplicate records from my table.

I am currently using the following:

DB::table($entity['table'])
                ->select('*')
                ->groupBy($entity['columns'])
                ->havingRaw('COUNT(*) > 1')
                ->get();

Which is great, it returns the duplicate records, however, this only returns one of the records I need to return all the duplicates so that I can greet the user with a choice on which one to delete or keep.

How can I modify the above query to accomplish that?

Answer 1

Joining against the same table will allow you to retrieve the duplicate records without just getting a single version of it (caused by your groupBy in your question)

$entity['columns'] = ['username', 'surname'];

$groupBy = implode(',', $entity['columns']);

$subQuery = DB::table('list')
    ->select('*')
    ->groupBy($groupBy)
    ->havingRaw('(count(id) > 1))');

$result = DB::table('list')
    ->select('*')
    ->join(
        DB::raw("({$subQuery->toSql()}) dup"), function($join) use ($entity) {
            foreach ($entity['columns'] as $column) {
                $join->on('list.'.$column, '=', 'dup.'.$column);
            }
        })
    ->toSql();
    // or ->get(); obviously

    dd($result);