CODEWITHSUNDEEP
laravel-5eloquentduplicates
mchev
mchev

Reputation: 735

Laravel 5.8 get a list of duplicates datas

I'm looking for the most appropriate method to retrieve a list of duplicates in my database with laravel and eloquent.

Exemple of table records :

id : 1
artist_name : Jane Doe
track_name : The doe song

id : 2
artist_name : John Doe
track_name : A love song

id : 3
artist_name : Jane Doe
track_name : The doe song

id : 4
artist_name : John Doe
track_name : Happy

id : 5
artist_name : John Doe
track_name : Happy

Result expected (a list of all duplicates):

id : 1
artist_name : Jane Doe
track_name : The doe song

id : 3
artist_name : Jane Doe
track_name : The doe song

id : 4
artist_name : John Doe
track_name : Happy

id : 5
artist_name : John Doe
track_name : Happy

I tried the following piece of code but I'm getting an error :

$tracks = Track::whereIn('id', function ( $query ) {
    $query->select('id')->from('tracks')->groupBy('track_name')->havingRaw('count(*) > 1');
})->paginate(20);

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'table.tracks.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select count() as aggregate from tracks where id in (select id from tracks group by track_name having count() > 1))

Upvotes: 0

Views: 69

Answers (1)

Vibha Chosla
Vibha Chosla

Reputation: 713

This is not recommended but you should try

Remove ONLY_FULL_GROUP_BY from phpmyadmin

  • Open phpmyadmin & select localhost
  • Click on menu Variables & scroll down for sql mode
  • Click on edit button to change the values & remove ONLY_FULL_GROUP_BY & click on save.

Or you can run following query : 

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Upvotes: 0

Related Questions