Get duplicate records in laravel

• Get list of duplicate records using unique() and diff() method

  --PATH app\Http\Controllers\<YourController>.php

  $users = User::all();
  $usersUnique = $users->unique(['user_name']);
  $userDuplicates = $users->diff($usersUnique);
  echo "<pre>";
  print_r($userDuplicates->toArray());
  

  This code snippet helps you to get the list of all duplicate records in laravel. We will get the list of all users which have the duplicate values in the user_name column. You can also change column name (id) as per your requirement to check the duplicate records in this particular table.

• Get list of duplicate records using groupBy method on sub-query

  --PATH app\Http\Controllers\<YourController>.php

  
  $results = User::whereIn('id', function ( $query ) {
              $query->select('id')->from('users')->groupBy('ip')->havingRaw('count(*) > 1');
          })->get();
  return $results;
  

  This code snippet returns the list of all users which have duplicate values in ip column. Basically It helps you to find the users which have created multiple ids with different user emails using the same device.

  If you are getting error, SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column then set value strict => false in your mysql connections array in your config\database.php file.

  config\database.php

  'connections' => [
  
          'mysql' => [
              
              'strict' => false,

Back to code snippet queries related laravel