How to extract all rows which is duplicates in laravel?

Question

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.

Answer 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.

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