How to extract all rows which is duplicates in laravel?
10,868
Solution 1
Use havingRaw
:
$duplicates = DB::table('users')
->select('name','location', DB::raw('COUNT(*) as `count`'))
->groupBy('name', 'location')
->havingRaw('COUNT(*) > 1')
->get();
I also wasn't sure of the syntax, but the Laravel documentation seems to imply that the alias you defined in the select clause is not available in the normal having()
function.
Solution 2
To get All Rows rather than a total count of a group of duplicate rows would look like the following;
$duplicates = DB::table('users')
->select('id', 'name', 'location')
->whereIn('id', function ($q){
$q->select('id')
->from('users')
->groupBy('name', 'location')
->havingRaw('COUNT(*) > 1');
})->get();
Author by
Soul Coder
Updated on June 09, 2022Comments
-
Soul Coder almost 2 years
I want to get all rows which is the same name and location from Users Table
**id** |name |location |phone_number 1 |John | Europe |0988884434 2 |john | Europe |0933333333 3 |Michael |Europe |0888888888 4 |Smith |Dubai |082388888888 5 |Smith |Dubai | 03939494944
I want to get all rows which is the same name and location like
john |Europe john |Europe Smith |Dubai Smith |Dubai
here is how i tried to do
$duplicates = DB::table('users') ->select('name','location', DB::raw('COUNT(*) as `count`')) ->groupBy('name', 'location') ->having('count', '>', 1) ->get();
but this is just showing only one row which is duplicates like
john |Europe Smith|Dubai
Any help or advice you have would be greatly appreciated.