laravel unique() dont work with paginating

Question

i'm facing with an issue in laravel 5.7.28, i'm trying to get records with all of my fields and paginate them , sadly i got a bug in my code that cause registering users with duplicate phone number(a user registered itself more than one time and of course i fix the bug ) , for now i'm facing with a scenario that i want to fetch my (non-duplicate) distinct users on their phone, and paginate them . here is wishful scenario :

$users = User::distinct('phone')
->doesntHave('carrierUsers')
->doesntHave('thirdparty')
->latest()->paginate(40);

it returns all users (with duplicate phone number), seems distinct function not affected , so i tried unique function :

$users = User::doesntHave('carrierUsers')
->doesntHave('thirdparty')
->latest()->paginate(40)->unique('phone');

it works (for getting non-duplicate) but break paginating functionality

and for

$users = User::doesntHave('carrierUsers')
->doesntHave('thirdparty')
->latest()
->unique('phone')
->paginate(40);

i got error for undefined function on builder , means it works on collections , also i tried groupBy (search whole the web for solution) , it makes me confused till when i use it

User::doesntHave('carrierUsers')->doesntHave('thirdparty')
->latest()->groupBy('phone')
->paginate(40);

i got SELECT list is not in GROUP BY clause, is that mean in every phrase in my select statement (for now is all ('*') ) i should use it in my groupBy ? i mean i need all columns , how can i have all those column in groupBy clause ? whats that mean by the way ?

, error is telling : this is incompatible with sql_mode=only_full_group_by. is she making this happen ? how can i fix it ? 1) excuse me for my english 2) Thanks for reading whole this 3) pleasure to reply and help me

Answer 1

Problem 1: Use distinct

For distinct() method, passing parameters is not supporteds, so distinct(phone) will not works.

You can use ->select('phone')->distinct() instead. However, support unique column is selected.

Problem 2: Use Group By

So if you want to select all columns and unique one column, you can use group by.

And because you are using mysql 5.7+.

The ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES modes were added in MySQL 5.7.5.

So if you select all columns and group one column, you will get the only_full_group_by error.

There are two options can solve this problem.

Solution1:

Use ANY_VALUE()

User::doesntHave('carrierUsers')->doesntHave('thirdparty')
->latest()
->selectRaw('phone, ANY_VALUE(id) AS id, ANY_VALUE(username) AS username, ...')
->groupBy('phone')
->paginate(40);

Solution2:

In config/database.php configuration, set strict mode to false(This is not very safty):

'mysql' => [
   'driver' => 'mysql',
   ...
   'strict' => false,
],