CODEWITHSUNDEEP
phpmysqllaravelinner-joinquery-builder
ChrisBratherton
ChrisBratherton

Reputation: 1590

Returning full duplicate rows using Laravel's query builder

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?

Upvotes: 2

Views: 5488

Answers (3)

Dan Kinchen
Dan Kinchen

Reputation: 117

You can accomplish this by using whereIn and a function to query the same table that you are working with.

Let say you have a scenario where you want to look for duplicate records containing the same last name in the user table.

Database Table - User

      --- user_id --- first_name --- last_name --- email ---

            1           Dan           Smith     [email protected]
            2           Jim           Jones     [email protected]
            3           Amy           Grant     [email protected]
            4           Bob           Brown     [email protected]
            5           Sue           Davis     [email protected]
            6           Leo           Grant     [email protected]
            7           Ann           Grant     [email protected]

Then you can use the following code;

 $duplicates = DB::table('user')
 ->select('user_id', 'last_name')
 ->whereIn('user_id', function ($q){
             $q->select('user_id')
             ->from('user')
             ->groupBy('last_name')
             ->havingRaw('COUNT(*) > 1');
 })->get();

Which will return the following;

      --- user_id --- last_name

            3           Grant
            6           Grant
            7           Grant

Upvotes: 0

Ben Swinburne
Ben Swinburne

Reputation: 26477

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

Upvotes: 1

Shadow
Shadow

Reputation: 34231

You need to join back to the table in $entity['table'] on the columns in the $entity['columns'] to get the duplicates.

Also, the select('*') is not really a good idea even if you are using mysql. This is against the sql standard and works in mysql only if it is configured in a certain way. If the configuration changes or you migrate your app to a mysql server with different configuration on sql modes, your query will fail.

I would use $entity['columns'] in the select list as well.

In sql the query should look like as follows:

select table.* from table inner join
  (select field1, field2 from table t
  group by field1, field2
  having count(*)>1) t1
on table.field1=t1.field1 and table.field2=t1.field2

Upvotes: 0

Related Questions