Debug School

rakesh kumar
rakesh kumar

Posted on

laravel sql query

1.Check for Exact Duplicate on Multiple Columns

$isDuplicate = Booking::where('user_id', $userId)
    ->where('vechicle_id', $vehicleId)
    ->where('start_date', $startDate)
    ->where('end_date', $endDate)
    ->exists();
Enter fullscreen mode Exit fullscreen mode
  1. Get All Duplicate Phone Numbers 
$duplicates = DB::table('users')
    ->select('number', DB::raw('COUNT(*) as total'))
    ->groupBy('number')
    ->having('total', '>', 1)
    ->pluck('number');
Enter fullscreen mode Exit fullscreen mode
  1. Find Duplicate Emails 
$duplicates = DB::table('users')
    ->select('email', DB::raw('COUNT(*) as total'))
    ->groupBy('email')
    ->having('total', '>', 1)
    ->pluck('email');
Enter fullscreen mode Exit fullscreen mode
  1. Duplicate Bookings by Vehicle and Date Range 
$duplicates = Booking::where('vechicle_id', $vehicleId)
    ->whereBetween('start_date', [$from, $to])
    ->groupBy('user_id', 'start_date', 'end_date')
    ->havingRaw('COUNT(*) > 1')
    ->get();
Enter fullscreen mode Exit fullscreen mode
  1. Check for Duplicate Vendors by Name and Number 
$isDuplicate = Vendor::where('name', $vendorName)
    ->where('number', $vendorNumber)
    ->exists();
Enter fullscreen mode Exit fullscreen mode
  1. Get All Duplicates on Combination of Brand and Model 
$duplicates = DB::table('vehicles')
    ->select('brand', 'model', DB::raw('COUNT(*) as total'))
    ->groupBy('brand', 'model')
    ->having('total', '>', 1)
    ->get();
Enter fullscreen mode Exit fullscreen mode
  1. Detect Duplicate Shops by Address 
$duplicates = Shop::select('address', DB::raw('COUNT(*) as total'))
    ->groupBy('address')
    ->having('total', '>', 1)
    ->get();
Enter fullscreen mode Exit fullscreen mode
  1. Duplicate Orders by User and Same Amount 
$duplicates = DB::table('orders')
    ->select('user_id', 'amount', DB::raw('COUNT(*) as total'))
    ->groupBy('user_id', 'amount')
    ->having('total', '>', 1)
    ->get();
Enter fullscreen mode Exit fullscreen mode
  1. Detect Duplicate Rows by Multiple Fields (Raw SQL Example) 
$duplicates = DB::select("
    SELECT user_id, vehicle_id, start_date, end_date, COUNT(*) as total
    FROM bookings
    GROUP BY user_id, vehicle_id, start_date, end_date
    HAVING total > 1
");
Enter fullscreen mode Exit fullscreen mode
  1. Count Duplicates in a Collection 
$duplicates = collect($array)->duplicates('field_name');
Enter fullscreen mode Exit fullscreen mode

Top comments (0)