Why can't I select unique fields using the groupBy method in Laravel?

Question

MySQL

I'm trying to get records from the database without duplicates.
I execute a request

$data= new App\Client();
$client = $data->distinct()->groupBy('email')->get();

I'm facing an error:

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

But if you copy-paste the query from this error select distinct * from `clients` group by `email`and execute it directly in the database, then it works fine.

What is the problem then?

Answer 1

The error is absolutely correct: you need to select in the query only those columns that are specified in the grouping, otherwise the result will be unpredictable. Well, distinct is not needed here at all: It works through the database because strict mode is enabled in Laravel (and this is good).
$emails = $data->groupBy('email')->pluck('email');
But the fact that you have a variable called $client suggests that you do want something strange. Getting records without duplicates with a single query is unlikely to succeed, because there you need to specify very clearly what result you want to get and the query will be cumbersome, if it can be compiled at all. Such a task is much easier to solve with code, although it is not as resource efficient. If this is a one-time task, then it is definitely worth doing it through PHP, but if it is a permanent one, you need to think about the database schema and the necessary data.