CODEWITHSUNDEEP
mysqlapilaravel-6.2
Naveen Nayak
Naveen Nayak

Reputation: 185

how to get data groupBy email from database in Laravel 6

I have the same email ids in multiple times in database, I need to get and display all users excluding duplicates.

Please help me to solve this issue

expected output:

[
    {
        "donation_id": "DMS1900001",
        "donor_name": "Naveen",
        "phone": "9164020904",
        "email": "[email protected]"

    },
    {
        "donation_id": "DMS1900032",
        "donor_name": "Naveen",
        "phone": "9164020904",
        "email": "[email protected]"
    }
]

My Code:

$donordata =  DB::table('donors')
                      ->groupBy('email')
                        ->get();
         return $donordata;

And the Response 

SQLSTATE[42000]: Syntax error or access violation: 1055 'minidms_db.donors.id' isn't in GROUP BY (SQL:
        select * from `donors` group by `email`)

But the data is in Table:

[
    {
        "donation_id": "DMS1900001",
        "donor_name": "Naveen",
        "phone": "9164020904",
        "email": "[email protected]",
    },
    {
        "donation_id": "DMS1900002",
        "donor_name": "Naveen",
        "phone": "9164020904",
        "email": "[email protected]",
    },
    {
        "donation_id": "DMS1900035",
        "donor_name": "Naveen",
        "phone": "9164020904",
        "email": "[email protected]",
    }
]

Upvotes: 0

Views: 304

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

I would just do a distinct select here:

$donordata = DB::table('donors')
                 ->distinct()
                 ->get();

The issue here, as mentioned by the error message, is that you are doing a SELECT * but you are only grouping by a single column:

select * from `donors` group by `email`

If you wanted to use a GROUP BY approach to remove duplicates, then you could have used:

SELECT donation_id, donor_name, phone, email
FROM donors
GROUP BY donation_id, donor_name, phone, email;

But, just calling Laravel's distinct() function is a bit easier in this case.

Upvotes: 1

dom.macs
dom.macs

Reputation: 796

Edit your applications's database config file config/database.php

Update strict value on mysql to false

return [
   'connections' => [
      'mysql' => [
         'strict' => false
       ]
   ]
]

Upvotes: 0

Related Questions